Customizing Google Forms and Sheets

The Client

Alberta Science Network (ASN) is a charity that is very close to my heart. They have several educational initiatives, including “Scientists And Engineers in the Classroom”. Through this program, from 2011 to 2015, I made over 40 presentations at local schools, teaching over 1500 students about Chemistry using magic and hands-on activities.
Here’s a quick video I whipped up when ASN wanted their volunteers to talk about what they do for the organization.
ASN approached me asking if I knew of any technologies or people who could help smooth out some of their processes. I initially declined because it was a busy time at work, but later approached the organization to see if I could help out.

Before

Here’s a screenshot of the member form that was in use originally.
Original Form Screenshot
More importantly, here’s the original process:
  1. Member fills out form
  2. ASN staff gets an email with form data
  3. ASN staff copies form data into a local spreadsheet
  4. At end of year, all members are sent an email, asking them to fill out the form again

This is quite a labor-intensive process for staff, the data is not backed up, and it does not make it easy for members to renew.

After

As you can see below, the form was remade using Google Forms. The look and feel wasn’t significantly changed but a few unnecessary fields were removed and the fields were aligned and spaced further apart from each other.
New Form Partial Screenshot

The Process

Going into the project with ASN, I was hoping to be able to use Ruby on Rails (RoR) to make a custom solution because I had recently done the fantastic Rails Tutorial by Michael Hartl. After considering RoR and looking into Customer Relationship Management tools such as the open-source platform CiviCRM, I realized that Google Forms and Sheets could do the trick in considerably less time.
Google automatically inserts form data into a spreadsheet, but there were a few things that weren’t automatically supported. For example, ASN has annual memberships, so every June, a script goes through and sets all members to inactive then invites them to renew their membership. In order to improve the renewal rate, I used the following script to pre-fill the membership form for these renewal emails.
/**
* Use Form API to generate pre-filled form URLs
* Based on http://stackoverflow.com/a/26395487/1704355
*
* Note: This script can either be run manually or linked to a trigger such as a time-based trigger.
*/

function GeneratePrefilledURLs()
{
  var range = GetSpreadsheetDataRange();
  var data = range.getValues();
  var headerRow = data[0];

  // Use form attached to sheet
  var formUrl = SpreadsheetApp.getActive().getFormUrl();
  var form = FormApp.openByUrl(formUrl);
  var formItems = form.getItems();
  
  // Grab column numbers for later use.
  var urlCol = headerRow.indexOf("Prefilled URL");
  var activeMemberColumn = headerRow.indexOf("Approved Active Member");
  
  // Skip headers, then build URLs for each row in this sheet.
  for (var row = 1; row < data.length; row++)
  {
    var activeMember = data[row][activeMemberColumn];
    
    // Only update for active members.
    if (activeMember == "y" || activeMember == "")
    {
      // Create a form response object to prefill.
      var formResponse = form.createResponse();

      // Cycle through all form items that have matching columns.
      for (var i = 0; i < formItems.length; i++) { // Get text of question for item var ques = formItems[i].getTitle(); // Get col index that contains this question var quesCol = headerRow.indexOf(ques); var resp = ques ? data[row][quesCol] : ""; var type = formItems[i].getType().toString(); //Logger.log("Question='"+ques+"', resp='"+resp+"' type:"+type); // Treat Membership Type separately because we always prepopulate the same response (Renewal) regardless of what the data says. if (ques == "Membership Type") { var item = formItems[i].asMultipleChoiceItem(); resp = "Renewal"; } else { // Need to treat every type of answer as its specific type. switch (formItems[i].getType()) { case FormApp.ItemType.TEXT: var item = formItems[i].asTextItem(); break; case FormApp.ItemType.PARAGRAPH_TEXT: item = formItems[i].asParagraphTextItem(); break; case FormApp.ItemType.LIST: item = formItems[i].asListItem(); break; case FormApp.ItemType.MULTIPLE_CHOICE: item = formItems[i].asMultipleChoiceItem(); break; case FormApp.ItemType.CHECKBOX: item = formItems[i].asCheckboxItem(); // Resp is a CSV string. Convert to array of separate choices, ready for createResponse(). resp = resp.split(/ *, */);   // Convert CSV to array break; case FormApp.ItemType.DATE: item = formItems[i].asDateItem(); resp = new Date( resp ); break; case FormApp.ItemType.DATETIME: item = formItems[i].asDateTimeItem(); resp = new Date( resp ); break; default: item = null;  // Not handling DURATION, GRID, IMAGE, PAGE_BREAK, SCALE, SECTION_HEADER, TIME break; } } // Add this answer to our pre-filled URL if (item && resp != "") { try { // Store the item response in formResponse. If it doesn't work, just log the exception and move on. var response = item.createResponse(resp); formResponse.withItemResponse(response); } catch (e) { Logger.log(e.toString()); } } // else if we have any other type of response, we'll skip it else { Logger.log("Skipping i = " + i + ", question = " + ques + " type: " + type); } } // Generate the pre-filled URL for this row. var editResponseUrl = formResponse.toPrefilledUrl(); var shortUrl = UrlShortener.Url.insert({longUrl: editResponseUrl}).id; // If there is a "Prefilled URL" column, update it. if (urlCol >= 0)
      {
        var urlCell = range.getCell(row+1,urlCol+1);
        urlCell.setValue(shortUrl);
      }
    }
  }
};


// Helper function that returns the size of an object.
// From http://stackoverflow.com/a/6700/1704355
Object.size = function(obj)
{
  var size = 0, key;
  for (key in obj)
  {
    if (obj.hasOwnProperty(key)) size++;
  }

  return size;
};
I had never worked with Google Apps Script (GAS) or JavaScript (which GAS is based on) before, so it took a lot of reading, searching, and trial and error, but I made sure to document my code to make it easy for myself or others to be able to modify the code in the future. I’m sure that there are some “rookie mistakes” in there but so far, no problems have been reported and on-time member renewals almost tripled from 2014 to 2015. ASN was quite pleased with my work!