How to Save Google Form Responses as PDF with Google Apps Script

June 29, 2021
How to Save Google Form Responses as PDF with Google Apps Script
Table of Contents
Table of Contents

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 on how to save Google Form responses into a PDF.

Step 1: Convert these Sample Google Sheets to PDF

The form that I will be using to demonstrate how to Google Form responses as PDF 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 Google form responses have been received in the Google Sheet associated with the form. As you can see the form contains the following:

More tips on Google Apps Script here

Step 2: Setting up automation with the App Script in Google Sheets

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.

Step 3: How to download new Google Form responses as PDF with triggers

If you want to know how to save a google form response in PDF format as soon as it comes in, this is the section where I explain the different parts of the Google Docs script format we have used for this purpose. 

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 to convert Google Form responses to PDF.

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

Learn how to create fillable forms in Google Docs

How to Convert Google Form Responses to PDF: The Summary

We have seen how you can automatically convert Google Form responses 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 create an automated system that automatically converts form responses to PDFs. This will both save their time and also increase their efficiency.

Great! Next, complete checkout for full access to Form Publisher Blog
Welcome back! You've successfully signed in
You've successfully subscribed to Form Publisher Blog
Success! Your account is fully activated, you now have access to all content
Success! Your billing info has been updated
Your billing was not updated