Google forms to pdf email while storing data in google sheets

Sending marks of students has become a difficult task. The teacher often has a situation, where they need to create a document with their marks and details.

In this blog post, we will cover how to create a PDF from the responses of Google Form using Google Docs Script. I will be covering a fictitious example of the following:

1. There are 60 students in the classroom. The teacher has carried out the test via quiz in a Google form.
2. The task of sitting and creating a separate document for each is a tiresome process. So with the help of Google Sheets and a bit of Google Apps Script, we are going to automate the whole process.

So let’s get started.

Step 1: Convert these Sample Google Sheets to PDF

The form that I will be using is a Math paper that has to be given to the students. (If you would prefer working with a copy of the Google Form, 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
• Answers chosen by the students for the three math's questions
• Score that has been calculated as per the points assigned for each question

More tips on Google Apps Script here

Step 2: Write 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 using driveapp.getfolderbyid and other functions.

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

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

  });
 
}
function Create_PDF(info) {
  
  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);
  
   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();
  

  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;

}

I have to create a PDF file for each student. This will contain all the details and the marks secured by the students. For this I have created a template of the PDF file that will have the following details in it:

• Name of the student
• Roll number of the student
• Marks secured by the students
• Email address of the student

This is the template that I have created in Google Docs. So for each student, the code will put in the details and create a PDF.

Now let me explain the different parts of the Google Docs script format we have used.

function After_Submit(e){
      const info = e.namedValues;
      Create_PDF(info);
      console.log(info);}

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 done via what is called a Trigger.

This trigger will create the PDF 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’s triggers

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

3. Now we have to add a trigger, to create a PDF as soon a new response comes 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.
• As soon as a new response comes in, the function will be invoked and the function code will create a PDF with the responses from the Google Sheet.

4. After doing 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 this trigger and send the Google Form to respondents. As soon as the responses come in, the PDF will be automatically created in the assigned folder.
• If a school has to organize a workshop, and they have to send certificates, they can take the details and automatically create certificates for the students.

Now let’s move on to the other parts of the code.

function Create_PDF(info) {
const PDF_folder  
=DriveApp.getFolderById("1FU1wqMyyW3rmUAFHsu49NKivWPOYyONI");
const PDF_Template = 
DriveApp.getFileById("1HBxpN-c9Dlq2h0YMi8wSbS2gc_2Uo4lNAsYaht0tYpE");

Here I have done two things here :

1. I have got the folder by id in which I am going to save my PDF files.I have assigned it to a variable which I will be using later.
2. I have got the Google Doc template by id that I have to use to create the PDF.

const  OpenDoc = DocumentApp.openById(PDF_Template.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. In the doc I have created variables for each of the details that have to be saved in the PDF. Here I have accessed the body of the Google Doc, where the sdata from the Google Sheet is going to substitute the values.

body.replaceText("{Score}", info['Score'][0]);
body.replaceText("{name}", info['Enter your name'][0]);
body.replaceText("{email}", info['Enter your email address'][0]) 
body.replaceText("{roll}", info['Enter your roll number'][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. This variable ( {score} ) is in Google Doc and the other one is from the Google Sheet, so it will replace it. For each row it will take the value and substitute the value in the Google Doc. So for each of the values I have created a variable in the Google Doc such as the (1) Marks, (2) Name, (3) Roll number, (4) Email Address .

After putting all the values, we have to save and close the Google Doc. For this I have used the OpenDoc.saveAndClose(); function.

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

Here I have set the name of the PDF that has to be saved. I have assigned the name and roll number of the student as the name of the PDF file. After the PDF file has been created, I am going to print a confirmation message that will tell us that the PDF file has been created.

Our code is complete. Since we have added the trigger we do not have to run the program. As soon as the responses come in, it will automatically create a PDF. To check the PDF, go to the assigned folder and there you will see the PDF file.

The script on successful execution will send out an email to each recipient. A sample PDF is shown below:

If the user needs something more configurable, maintainable or sophisticated, they can use the Form Publisher add-on

How to Export Google Doc to PDF: The Summary

We have seen how you can automate the process of converting Google Sheets to PDFs for multiple people. This can be used by teachers to create report cards for students. It will reduce the work of the teachers and also increase the speed of the process. This can be used by people who take workshops, who have to give certificates to the students. Instead of sitting for hours trying to make PDFs for the students, they can automate the process. This will both save their time and also increase their efficiency.