In August of 2009, Google released “Google Apps Scripts” as a part of the Google Apps suite. With Scripts embedded within a cloud-based product, Google has created the ability for users to do more with Google Apps and also create standalone Web applications.  Legacy Excel users can relate to Scripts as the ability to program Visual Basic macros.

Scripts are programmed in JavaScript and all development is done directly in a browser. There is also a handy editor for you to manage your files and debug the scripts while coding. In addition, there’s a Developers portal to research advanced functionality and view tutorials (https://developers.google.com/apps-script/). Sounds really cool, right? The thought of JavaScript in the cloud, creating advanced features is intriguing… but what could this be used for in the real-world? In this blog post, I will share with you an example of how Backupify uses Scripts to extend the functionality of Google Forms and Spreadsheets and create a useful tool.

Product Specifications

A few months ago, I was asked to create a tool that could standardize creative content requests (infographics, e-books, whitepapers, etc) for our Design team. This tool would be used to help the Design team prioritize requests and provide all relevant information to create the artwork.

The requirements for the tool were communicated to me as:

  1. Utilize a form to collect responses to 10 questions (provided) using drop down boxes and text input fields
  2. Online access to this tool

  3. Collect answers to form, then email Design and Marketing teams each time the form is submitted

After understanding the requirements, I utilized good product management practices and negotiated the delivery schedule (fast as possible, I was told) and began crafting the solution.

Google Form

I had used Google Forms previously in the year (to facilitate my yearly Oscar picks competition with friends) so I knew that submitted Forms could link to a Spreadsheet. First, I went to Google Drive and created a new Form. I had a list of the questions that needed to be on the form, so I typed up all the questions and input fields. Google Forms makes it easy to setup security and authentication for the form.  By checking the first checkbox in the “Form Settings” tab, only users on your Google Apps domain will be able to submit the form.

As soon as you create the form, a Google Apps Spreadsheet is also created in the same location of the Form called “Untitled form (Responses).” Here in the root of my Google Drive you see the Form and the Spreadsheet.

As the title suggests, the Spreadsheet is going to hold all the Responses (form submissions) for our Creative Request Form. Also, our Apps Script is going to live inside that Spreadsheet - but more on that later.

Google Apps Spreadsheet

When you first open the “Untitled form (Responses)” Spreadsheet there will be a worksheet named “Form Responses.”  For this example, I created only 2 fields on the form “Project Name” and “Project Overview” which show up as well as “Timestamp” of the form submission and “Username” (hence the authentication, we’ll know who submitted the form each time).

Note that those fields are shown in dark grey, which indicates that they are connected to the Form.  Be careful not to modify this worksheet in any way, because any changes could impact linkage with the Form we created. I clicked “View live form” and submitted a test entry. Here’s what a submitted Form response looks like, we have the timestamp and username of the person, as well as the answers. Pretty nifty.

Now that we have a Form linked to a Spreadsheet, its time to program the JavaScript that will satisfy the requirement for having an email sent with the latest form submission.

The Google Apps Script

Now comes the fun part, programming the Apps Script. Within the form Responses Spreadsheet, click “Tools” and then “Script Editor” and you’ll be taken to the Apps Script entry screen. I’m going to be walking you through the JavaScript to do the emailing portion, so simply click “Blank Project” as the answer to the question “Create a script for.”

This blog post will focus on the implementation of JavaScript and assumes that you have some basic knowledge about programming utilizing variables, loops, functions, etc.  Check out this guide for some background basics on JavaScript (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide).

We are going to code a function called “emailFormResponses” which will well… email the form responses to a list of people you specify.  Here’s the code for the function:

function emailFormResponses() {

//1) Get our Spreadsheet, set it to the variable responses_spreadsheet:

var responses_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

//2) Now, within the Spreadsheet, get the Form Responses worksheet and store into a variable sheet:
var sheet = responses_spreadsheet.getSheetByName(“Form Responses”);

//3) Let’s use the getLastRow function to return an integer for which row number is holding the latest form submission.
//This code allows for the Script to be dynamic, capturing the latest submission each time:
var lastRow = sheet.getLastRow();

//Note that the Spreadsheet contains a row of column headers, so the first submission will be row 2.

//4) Now that we know which row has the data we need to email, I will use the the getRange function set the data range that will be pulled.
// The getRange function has four variations, I used this one:   getRange (row, column, number of rows, number of columns)

// For this blog example, each Form Response only has 4 columns worth of data (TimeStamp, User Name, Project Name, Project Overview)
// so its okay to set it to 4, to pull only 4 columns worth of data. If your form is bigger, you’ll have to set this number manually.

// The  sheet.getRange code will set the range of the data I need to pull.
var dataRange = sheet.getRange(lastRow, 1, 1, 4)

//5) The data in the specified range will be put into a variable called data, using the getValues function.
var data = dataRange.getValues();

// The information stored into the data variable as an array and looks like this:
// data =
// index: 0
// index: 0 - Timestamp
// index: 1 -  UserName
// index: 2 - Project Name
// index: 3 - Project Overview

//6) In order to access the 4 pieces of data, I had to specify data[0] first and then an additional bracket for each piece.
var timeStamp = data[0][0];
var userName = data[0][1];
var projectName = data[0][2];
var projectOverview = data[0][3];

// Note, the debugging functionality is very easy to use, and setting up breakpoints
// and stepping through each line is a great
// feature for Google Apps Scripts.

//7) I now have the data that needs to be emailed, so next, I need to create a new Email
//The MailApp function is built into Scripts, and has a method sendEmail to well, send
//an email that you specify the details:

MailApp.sendEmail({

// I set the email addresses that will receive this email each time it runs. You would modify this, so that my co-worker Sheila and I don’t get your emails to: “sheila@backupify.com, vishal@backupify.com”,

//And the subject of the email
subject:“NEW - Backupify Creative Request”,

//Because I am the one creating this script, the sender will be set to vishal@backupify.com.

//Now comes the fun part. We will use basic HTML tags to compile an email for MailApp to send, setting the htmlBody parameter.
//Devices capable of rendering HTML email will have no problems, alternatively, there is a body parameter which can be set
//by string only.  But for the purpose of this email, HTML works out great.

htmlBody:
//Quick review of HTML tags used here:
// <h2> sets the header in a larger font, used for the title
// <u> Underlines the text between the open and close tag
// <b> Bolds the text between the open and close tag
// <br> Line break

//Each variable is displayed with text to specify the field
”<h2><b><u>NEW - Backupify Creative Request</b></u></h2> <br>” +
“<b>Submitted on:   </b>” + timeStamp +  ”<br><br><br>” +
“<b>Submitted by:   </b>” + userName + “<br><br><br>”  +
“<b>Project Name:   </b>” + projectName + “<br><br><br>” +
“<b>Project Overview:   </b>” + projectOverview + “<br><br><br>”

});

}

You’re all set! Now, hit the Play button to the left of the spider.  The Script will prompt you to authorize, so click Authorize. Google will ask you to accept that it will send mail as you and have access to your Spreadsheet, click “Accept.”

Check your email to see if you received the form you submitted.

In order to have the function run each time the Form is submitted, you have to set a trigger. Within the Script Editor, click the on the tab “Resources” → “Current project’s triggers” → “Click here to add one now.”

Set it to the following parameters and click “Save,” and the emailFormResponses function that you coded will run each time your Form is submitted, creating an email and then sending it.

The next test you should run is an end-to-end test by going to the live form by clicking “View Live form” on your Form and submitting a test entry and see if it appears in your inbox as an email.

I hope this blog has provided you with a real-life example of how an embedded Google Apps Script can be used to extend the capability of a Google Form and Spreadsheet.  If you’re interested in learning more, I encourage you to look at the documentation for Scripts (https://developers.google.com/apps-script/) and anytime you’re in a Spreadsheet and say “I wish it could <insert some advanced feature>” look to a Google Apps Script.