Create ID cards and Send Emails Using Google Apps Script

Creating ID cards for the new students that have entered the school or college, due to the covid situation is a difficult task. It is difficult to take the details of the students and then make ID cards for them and send them on their respective email id.

So in this blog post I am going to show you how to create ID cards for students using Google Apps Script. I will be covering a fictitious example of the following:

  1. There are 60 students in the classroom. The teacher needs to create ID cards for the students.
  2. Due to the covid issues it is difficult for the teacher to take important details from the student to create ID cards for them.
  3. So with the help of Google Forms, Google Sheets and a bit of Google Apps Script we are going to create ID cards for students.

So let’s get started.

Step 1: Sample Google Form

The form that I will be using is an ID card detail collector that has to be given to the students. (If you would prefer working with a copy of the Copy of the Google Forms, click here)

Let us assume that all form responses have been received in the Google Sheet associated with the form. As you can see the form contains the following:

  • Name of the Student
  • Email address of the student
  • Roll number of the student
  • Phone number of the student
  • Date of birth of the student
  • Blood group of the student

Step 2: Writing the Automation Script

While you are in the sheet, launch the Apps Script Editor.

To do that:

(1) Click on Tools in the main menu.

(2) Click on the Script Editor.

This brings up the Script Editor as shown below:

We have reached the Script Editor. Let’s Code.

I have to create ID cards in the form of PDF’s for each of the students.For this I have created a template of the ID card, that will contain the following details:

  1. Name of the Student
  2. Email address of the student
  3. Roll number of the student
  4. Phone number of the student
  5. Date of birth of the student
  6. Blood group of the student

The Template of the ID card will look something like this:

This is the template that I have created using Google Docs. So for each student, the code will take all the details from the sheet and put it in this template and create a PDF and then send it on the respective Email ID.

Now let me explain the different parts of the code.

function After_Submit(e){const info = e.namedValues;const pdfFile = Create_PDF(info);console.log(info);sendEmail(e.namedValues['Email Address'][0],pdfFile);}

This is a function that will create PDF for multiple responses that are there in the Google Sheet. The parameter e contains all the new values received in the Google Form. How is this function invoked? It is via what is called a Trigger.

This trigger will create a PDF and send it on the designated email address as soon as a new response comes into the Google Form. To create a trigger, you have to follow these steps:

  1. Go to edit and click on Current Project triggers.

2. After clicking Current Project triggers you will see something like this.

3. No we have to add a trigger, to create a PDF and send an email to the designated email address, as soon as a new response come into the Google Sheet.So to add a trigger click on Add Trigger .After clicking Add trigger you will get the following options:

Here you have to choose the following:

  • The function that you want to run.
  • The source, which in this case is the Google Sheet.
  • The event type, in this case, is on Form Submit.
  • This means that as soon as a new response is recorded, it will automatically create a PDF and send it to the designated email address.

After doing all this, let us save the trigger by clicking on save.

This will help the person who has to create the PDF in the following ways:

  • The person who has to send the PDF does not have to go to the script and run the program every-time he gets a new response.
  • You need to just add the trigger and send the Google Form for response.As the responses come in an ID Card will be created and will be sent to the designated email address.
  • If a school needs to keep e ID cards, they can use this. Or if you need to create ID cards for workshops, you don’t need to print them too. Just follow these easy steps and you can send the ID cards on their email address.
sendEmail(e.namedValues['Email Address'][0],pdfFile);

Here I have called the email function. The parameters for this function are the email address and the PDF of the Id card that will be created.

Let’s move to the next part of the code.

function sendEmail(email,pdfFile){GmailApp.sendEmail(email, "ID CARD", "This is your ID Card.", {attachments: [pdfFile],});}

This function take the following parameter:

  1. Email Address of the recipient.
  2. The PDF of the ID card that has to be sent.

Here I have used the Gmail App to send the email. Here I have taken the email address, the subject and the body. Now I have to attach the PDF file.So in order to do that you have to write attachments: and then write the name of the file , followed by a comma. We are done with creating the email function.

Let’s move to the next part of the code.

const PDF_folder = DriveApp.getFolderById("1zx7rnI2M3p2U7RGTJugM_0G5aMINYTyh");const TEMP_FOLDER = DriveApp.getFolderById("1jO1BHwhwkKbGFcyT8DAJsew2v0gjCI4W");const PDF_Template =DriveApp.getFileById("1qHOMwuq2X_5LhUCfPLWcpUSh2n7pVRvHZ_kE-hsGmwg");const newTempFile = PDF_Template.makeCopy(TEMP_FOLDER);const  OpenDoc = DocumentApp.openById(newTempFile.getId());const body = OpenDoc.getBody();console.log(body);

Here I have done two things:

  1. I have got the folder where the PDF is going to be saved by id. I have assigned it to a variable which I will be using later.
  2. I have got the temporary folder by id, where the docs are going to be saved, in order two prevent changes in the original template.
  3. I have also got the template of the ID card where all the details will be put in a PDF.
const newTempFile = PDF_Template.makeCopy(TEMP_FOLDER);const  OpenDoc = DocumentApp.openById(newTempFile.getId());const body = OpenDoc.getBody();console.log(body);

My template has been saved in a Google Doc. I need to access it and then replace the variables in the doc with the data from the sheet. So here I have accessed the Google Doc by id, after which I have accessed the body of the Google DOC, where the data from the Google Sheets is going to substitute the values.

body.replaceText("{email}", info['Email Address'][0])body.replaceText("{name}", info['Enter your name'][0]);body.replaceText("{roll}", info['Enter your Roll number'][0]);body.replaceText("{number}", info['Enter your phone number '][0]);body.replaceText("{DOB}", info['Enter you Date of Birth '][0]);body.replaceText("{BLOOD}", info['Enter your Blood Group  [Eg:  O negative]'][0]);OpenDoc.saveAndClose();

Here I have used the replaceText function. So this will search for the variable in the Google Doc and replace it with the assigned data in the Google Sheet. For each row it will take the value and substitute the value in the Google Doc. So for each of the values in the Google Sheet, I have created a variable in the Google Doc. I have done this for the following values:

  • Name of the student
  • Email address of the student
  • Roll number of the student
  • Phone number of the student
  • DOB of the student
  • Blood group of the student

After putting these values, I will now save and close the Doc, using OpenDoc.saveAndClose();.

const BLOBPDF = newTempFile.getAs(MimeType.PDF);const pdfFile =  PDF_folder.createFile(BLOBPDF).setName(info['Enter your name'][0] + " " + info['Enter your Roll number'][0]);console.log("The file has been created ");return pdfFile;}

Here I am going to take the Google Doc template with the data put in it, and create a PDF. After that I have set the name of the PDF. For the file name I have taken the name and roll number of the students.I have created a confirmation message that will tell us that the PDF will be created. After this, I have to return the file so that the file can go to the email function, in order to send the PDF in the email.

Our code is complete. Since we have added the trigger, we don’t have to run the program every time a new response comes in. As soon as a new response comes in, it will automatically create a PDF with the details from the Google Sheet and send the file to the designated email address.

The script on successful execution will look something like this:

This what you will receive in the email and on opening the PDF file you will see something like this.

Summary

We have seen how you can automate the process of creating PDFs and sending the file in the email for multiple people. This can be used by teachers to create ID cards in this Covid situations. People organising workshops can use this to create ID cards for their events. This will save both time and money.Instead of sitting for hours trying to make ID cards, they can automate the process. This will save both their time and increase the efficiency .

Currently pursuing B Tech at MPSTME. Technical Blogger, interested in Google Workspace. Hope to remain a life long student.