Create PDF Using Google Apps Script

Aryan Irani
The Startup
Published in
7 min readSep 15, 2020

--

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 Apps 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.

Welcome to Chartmat! Are you tired of struggling to make sense of your data in Google Sheets? Do you want to turn your sheets into powerful apps and dashboards that can help you make better decisions and drive your business forward? Look no further! With Chartmat, you can easily transform your Google Sheets into beautiful and interactive dashboards and apps. Whether you’re a small business owner, a marketer, or a data analyst, Chartmat has something for everyone. With our easy-to-use platform and extensive range of features, you’ll be able to turn your data into actionable insights in no time. So why wait? Sign up for Chartmat today and start unlocking the full potential of your data! Check out the link given below to know more about charmat!

Step 1: Sample Google Form

The form that I will be using is a Maths 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 maths questions
  • Score that has been calculated as per the points assigned for each question

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.

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 code.

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 for response. As soon as the responses come in, the PDF will be automatically created in the assigned folder.
  • If a school has to organise 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 data 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:

Summary

We have seen how you can automate the process of creating 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 PDF’s for the students, they can automate the process. This will both save their time and also increase their efficiency.

Originally published on the FormPublisherBlog.

--

--