Google Drive

Fill and Send Google Docs with Google Apps Script

If you are feeling ambitious, here are instructions on how to automatically fill and send a Google Doc template from a Google Form, using Google Apps Script.

1. Prep your documents:

Before setting up the script, you will need three documents set up:

1. The Google Form for responders to fill out
2. The Google Sheet that will collect the responses from the Google Form
3. The Google Doc that will contain the template to be copied and mailed

2. Set up the Google Doc template:

When you set up the template, make sure to clearly mark the text that will be filled in by the responses from the Google Form. In the example here, everything that is surrounded by double curly brackets {{ }} will be filled in.

3. Get the document ID:

Once your template is created, find the document ID from the webpage URL. With the template document open, copy the URL from the top bar of your browser. In this case, it is:

https://docs.google.com/document/d/1jfYB9OlrWDML3KLLLfEPJk0kTh1A9IqUmEeM_1JLh-U/edit

The ID you want will be between /d/ and /edit:

https://docs.google.com/document/d/1jfYB9OlrWDML3KLLLfEPJk0kTh1A9IqUmEeM_1JLh-U/edit

4. Get the Drive folder ID to save the completed certificates:

If you want to save the filled-in templates, you will also need to make a folder in Google Drive to store them in.

Open the folder, then find its Drive folder ID. With the folder open, copy the URL from the top bar of your browser. In this case, it is:

https://drive.google.com/drive/folders/1ITQY1LaTEtnnEnLj9YvhCGZ56gcVWJy5

The ID you want will be the string of characters after the last forward slash / :

https://drive.google.com/drive/folders/1ITQY1LaTEtnnEnLj9YvhCGZ56gcVWJy5

5. Set up the Google Apps Script:
Open your Google Sheet. Below the name of the sheet, find the Tools menu item.

Under Tools, select Script Editor.

This will create a new Google Apps Script document.

6. Edit the Google Apps Script:
Using the script we have already made for the PRIDE Student of the Week form, we can make a new script here.

Highlight the existing text,

function myFunction() {

}

press 'Delete' on your keyboard to delete it, then copy and paste the code at the end of this document that we already wrote for the PRIDE Student of the Week form and edit it to fit the new Google Form you are setting up.

To help you edit the code, I have added comments (written in gray) that explain step by step what each part of the code does; if you need more explanation, please don't hesitate to contact me.

When you are done editing the code, click on 'Untitled project' in the upper left corner.

A Rename Project window will open. Name the project something descriptive, then click Rename to save it.

Once you have named your project, click on the Save icon.

7. Set the trigger for the script:

Almost done! On the left hand side, click the clock icon to open the Triggers menu.

In the window that opens, click Add Trigger in the bottom right corner.

From the options listed, change Select event type to On form submit, then click Save.

However, the script trigger needs some extra authorization first. If you have popups blocked, you will see a warning message like this appear at the bottom of your window:

At the top of your browser window, click on the popup icon to open the window anyway.

Click on your account name.

Scroll down to the bottom of the window and click Allow.

You will get this confirmation when the script has been authorized:

Lastly, click Save again to complete your script.

Sample code (copy and paste into your new Google Apps script):

function autoFillGoogleDocFromForm(e) {

1. Here is where you will collect the information from the Google Sheet to enter it into the Google Doc certificate template. Every time a response is submitted to your Google Form, a new row is created in your Google Sheet. 'Var' means that a "variable" is being created; the "variables" listed here each correspond to one cell in that new row, indicated by the number in brackets []. Note that the numbering starts at 0, so column A would be values[0], column B would be values[1], column C would be values[2], etc. The name of the variable is written after 'var' and should describe the answer being collected. If you change the questions on the Google Form to collect different information, make sure to update these variable names to accurately describe what they are recording.

var teacherName = e.values[2];

var studentName = e.values[3];

var studentGrade = e.values[4];

var recognitionArea = e.values[5];

var nominationReason = e.values[6];

var teacherEmail = e.values[7];

var parentEmail1 = e.values[8];

var parentEmail2 = e.values[9];

var studentEmail = e.values[10];

The code below does not need to be touched; it updates the recipient emails of the filled-out Google Doc certificate in case not all three email fields are filled out.

var sendEmails = parentEmail1 + "," + parentEmail2 + "," + studentEmail

if (parentEmail2=="") {

sendEmails = parentEmail1 + "," + studentEmail;

}

if (studentEmail=="") {

sendEmails = parentEmail1;

}

// This code sets the date on the certificate and formats it nicely as "Month, Day, Year," ex. "October 22, 2021".

var date = Utilities.formatDate(new Date(), "GMT-6", "MMMM dd, yyyy")

2. In the variable below, copy and paste the file ID of the Google Doc template you are using. Paste it between the single quotes ''. For example, for the Doc ID '1jfYB9OlrWDML3KLLLfEPJk0kTh1A9IqUmEeM_1JLh-U', the variable would read:

var template = DriveApp.getFileById('1jfYB9OlrWDML3KLLLfEPJk0kTh1A9IqUmEeM_1JLh-U');

3. In the variable below, copy and paste the Drive ID of the Google Drive folder where you want the filled certificates to be saved. Paste the Drive ID between the single quotes ''. For example, for the Drive ID '1ITQY1LaTEtnnEnLj9YvhCGZ56gcVWJy5', the variable would read:

var folder = DriveApp.getFolderById('1ITQY1LaTEtnnEnLj9YvhCGZ56gcVWJy5')

4. This code tells the filled-in certificate what it will be named and where it will be saved. The variables below indicate that the copy will be named with the current date, the name of the teacher who submitted it, and the student's name. 'Folder' refers back to the folder variable that we set up in the last step, so any filled-in certificates will be saved to that folder.

var copy = template.makeCopy(date + ', ' + teacherName + ', ' + studentName, folder);

// This code switches us over to filling in the new copy of the certificate instead of the original template.

var doc = DocumentApp.openById(copy.getId());

var body = doc.getBody();

5. Here is where we tell the certificate what to fill in the blanks. For each piece of text marked by double curly brackets in the certificate template, list the variable you want to fill in that space. Make sure that all the curly bracket text is written here exactly as it appears on the template, otherwise, it won't fill in correctly. The code will look like:

body.replaceText('{{Text to be replaced}}', nameOfVariableFromStep1);

body.replaceText('{{Reason for Nomination}}', nominationReason);

body.replaceText('{{Student Name}}', studentName);

body.replaceText('{{Area of Recognition}}', recognitionArea);

body.replaceText('{{Teacher Name}}', teacherName);

body.replaceText('{{Date}}',date);

// This code saves the new document.

doc.saveAndClose();

// This code has to do with emailing the filled-out certificate.

var aliases = GmailApp.getAliases()

Logger.log(aliases); //returns the list of aliases you own

Logger.log(aliases[0]); //returns the alias located at position 0 of the aliases array

// This code saves the certificate as a PDF.

var theBlob = doc.getBlob().getAs('application/pdf');

6. This code sets the subject line of the email. We can use the 'studentName variable from step 1 to personalize the email subject line.

var subject = studentName + " PRIDE Recognition";

7. This code contains the body of the email. The email will be sent using HTML formatting, so you can use HTML tags to make the email text more fancy. In this case, using tags creates a line break to format the text more cleanly. Here is a link to a page that contains some useful HTML tags for formatting text: https://www.w3schools.com/html/html_formatting.asp

var body = 'Dragon Families - Your student was nominated for a PRIDE award here at Johnston Middle School. Our PRIDE acronym stands for Positivity, Respect, Innovation, Determination and Excellence. Through lessons and activities about what PRIDE looks like at JMS, we are hoping to achieve a strong sense of trust and belonging, effective communication with all members of the school community, an atmosphere of equality, and positive relationships between students and teachers. Each week, teachers have the opportunity to recognize students who are demonstrating these qualities in their classrooms by nominating students who go above and beyond in these PRIDE areas. Students are recognized with a certificate detailing the teacher’s reason for the nomination. In addition, four students are selected every Friday from those nominated to receive special recognition in their classrooms, where our PRIDE patrol presents winners with candy, a school coin, and a prize. Please see the attached certificate and nomination providing details for your student’s nomination. Congratulations! -- Raine Mollenbeck | Associate Principal Johnston Middle School | Johnston Community School District 515-278-0476 | www.johnstoncsd.org';

// This code sends the email.

GmailApp.sendEmail(sendEmails, subject, body, {

from: aliases[0],

htmlBody: body,

attachments: [{

8. Lastly, this code sets the filename of the PDF attachment you are sending. Again, we can use the 'studentName variable from step 1 to personalize the name of the file.

fileName: studentName + " PRIDE Recognition.pdf",

content: theBlob.getBytes(),

mimeType: "application/pdf"

}]

});

}