Merge info from Google Forms to PDF Document and Send via Email

Today we needed a way to easily take information submitted from Google Form and create a form letter so administrators can sign and send it home with a student. I went through several mail merge scripts but nothing fit exactly what I needed so I took some of the best ideas from all of them (especially the employee of the month script) and created a system that works for us. This method of utilizing Google Apps script can be used for certificates for PD sessions, creating awards, or any other documents in which you need to get information that is submitted via a Google form.

Note, this is an advanced walkthrough but if you need help, please just ask! I am more than willing to work through it with you!

 

This is what our final document looks like:

Here is our workflow:

 

To get started create your Google form and get all of the information that you need to record. It is worth noting that we record more information on our form than actually goes on our final product. You can find a copy of the form we use here: http://bit.ly/mergetutform

 

After you create your form its time to create the template for what your end result will look like. Create a new blank Google doc and setup your letter. For our example, the bus conduct report, we have fields such as “Students Name” “Date of incident” “Explanation” etc. You can find a copy of our template here: http://bit.ly/mergetemplate notice in the area where information from our form needs to be we type the word “key” before it. This is important as our script searches the document for what we define here.

When your template is created its time to add the script to our spreadsheet. When you create a Google form all of  the information that user’s input is saved in a Google Spreadsheet. This spreadsheet is where we are going to add our script.

The way the Google scripts work is based on triggers. Triggers are a way to answer the question “What do I do when X happens?  In our case we want to create a PDF with the information from our form and send it to a pre-defined user (The building Administrator and Transportation Director).

 

To get started click “Tools” and then click “Script Editor”.

 

When you do you will see an untitled project come up. It looks like this:

Delete everything in the editor so we have a blank slate.

Step one is to pull in the template that we created earlier. We do this by defining our template variable:

 var docTemplate = ;

We need to tell the script what document ID is. We find this by opening our template and copying the long code in the url. You only want to copy what is pictured below:

 So your code should look like this:

 

 var docTemplate = "1sbsvGkJCmkN-NDkKLpZcZUy8h9x2YHkVmYOg9jJwYrI";

Next we name our template by entering

 var docName = "BusConductReport";

Now its time to create our trigger for when the user hits submit on the form. For this we create a function or trigger. We do this by entering:

 function onFormSubmit(e) {}

All of our code will go in between the ” { } “.

The first thing we want to do in the trigger is create our variables or holders. These will be the pieces of information that we need to put into our template.

 

We do this by entering:

 var  variablename = "static entry or form value"

Here is an example of the ones I used:

  var email_address = "thouston@huron-city.k12.oh.us";
  var full_name = e.values[2];
  var incident_date = e.values[5];
  var grade_level = e.values[4];
  var bus_number = e.values[6];
  var violations = e.values[10];
  var explanation = e.values[11];
  var drivers_coa = e.values[12];

*Note: e.values[x] is taking the value from the form in numerical order as seen below,

Now we need to grab the template we defined earlier and make a working copy. As you can see in the code we make a new copy and combine our document name and the name from the form:
 var copyId = DocsList.getFileById(docTemplate)
.makeCopy(docName+' for '+full_name)
.getId();

Next we open the temporary document that we just created:

var copyDoc = DocumentApp.openById(copyId);

Now we need to tell the script to go into the body of our document :

var copyBody = copyDoc.getActiveSection();

So we can replace our placeholder keys that we put in our template earlier:

copyBody.replaceText('keyFullName', full_name);
copyBody.replaceText('keyTodaysDate', incident_date);
copyBody.replaceText('keyGradeLevel', grade_level);
copyBody.replaceText('keyBusNumber', bus_number);
copyBody.replaceText('keyViolations', violations);
copyBody.replaceText('keyExplanation',explanation);
copyBody.replaceText('keyDriverCOA', drivers_coa);
 After we replace our variables we need to save and close our document:
copyDoc.saveAndClose();

Next, create a PDF of our temporary document:

var pdf = DocsList.getFileById(copyId).getAs("application/pdf");

Than, send an email to our recipient we defined earlier (remember this does not have to be a static entry like our example, you could pull from your form if you are creating certificates etc) :

var subject = "Bus Conduct Report";
var body = "Here is the Bus Conduct form for " + full_name + "";
MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

The final step is to get rid of the temporary document we have been working with. If you do not do this your Google Docs account will become filled with your temporary documents.

DocsList.getFileById(copyId).setTrashed(true); }

Whole Script:

// TJ Houston tjhouston.com (tj@tjhouston.com)
// Bus Conduct Report
// Get template from Google Docs and name it
var docTemplate = "1sbsvGkJCmkN-NDkKLpZcZUy8h9x2YHkVmYOg9jJwYrI"; // *** replace with your template ID ***
var docName = "BusConductReport";

// When Form Gets submitted
function onFormSubmit(e) {
//Get information from form and set as variables
var email_address = "tvonthron@huron-city.k12.oh.us, thouston@huron-city.k12.oh.us";
var full_name = e.values[2];
var grade_level = e.values[3];
var incident_date = e.values[4];
var bus_number = e.values[5];
var violations = e.values[9];
var explanation = e.values[10];
var drivers_coa = e.values[11];

// Get document template, copy it as a new temp doc, and save the Doc’s id
var copyId = DocsList.getFileById(docTemplate)
.makeCopy(docName+' for '+full_name)
.getId();
// Open the temporary document
var copyDoc = DocumentApp.openById(copyId);
// Get the document’s body section
var copyBody = copyDoc.getActiveSection();

// Replace place holder keys,in our google doc template
copyBody.replaceText('keyFullName', full_name);
copyBody.replaceText('keyTodaysDate', incident_date);
copyBody.replaceText('keyGradeLevel', grade_level);
copyBody.replaceText('keyBusNumber', bus_number);
copyBody.replaceText('keyViolations', violations);
copyBody.replaceText('keyExplanation',explanation);
copyBody.replaceText('keyDriverCOA', drivers_coa);

// Save and close the temporary document
copyDoc.saveAndClose();

// Convert temporary document to PDF
var pdf = DocsList.getFileById(copyId).getAs("application/pdf");

// Attach PDF and send the email
var subject = "Bus Conduct Report";
var body = "Here is the Bus Conduct form for " + full_name + "";
MailApp.sendEmail(email_address, subject, body, {htmlBody: body, attachments: pdf});

// Delete temp file
DocsList.getFileById(copyId).setTrashed(true);
}

You can find a copy of our template here: http://bit.ly/mergetemplate

You can find a copy of the spreadsheet that we used here: http://bit.ly/mergetut

You can find a copy of the form we use here: http://bit.ly/mergetutform

You can find the script here: http://pastie.org/3587263 or as text here: http://bit.ly/mergescripttext

TJ Houston

Hey everyone, My name is TJ and I am here to help you! I am a lifelong learner that strives to help others. Work: By day I work for Panasonic as the Sales Support Rep for the Midwest Region. Play: I love all things photography, videography, and tech! Home: I have a loving wife (Ashley) and an adorable German shepherd (Jynx) You can find me on twitter @tjhouston on instagram @tjshotsphotography or if you want to see some of my professional photography work check out tjshots.com. All of the views on this site are my own and do not reflect my employers.