Tuesday 6 October 2020

Generate Apps Script code for Forms

The following Google Apps Script is designed to automate the generation of further Apps Script code typically used when capturing Google Form submission data (create a Google Doc from a Form post). I tend to find there are 2 lines of code per Form question that I have to write which when scaled up to 60+ questions is a lot of time and chance for human error. They are:

  1. var data = formValues['question title'][0];
  2. Logger.log('data is: ' + data);

So this code is designed to gather all of the column headings from the response sheet attached to the Form, then loop through each one in turn and construct the necessary syntax to automatically produce the 2 above lines for each question into a Google Doc:

Auto generate Apps Script code lines
Screenshot of auto-generated code

1) getSheetData.gs

This Function gets the row headers from the Google Sheet for later use in creating the code and is the main Funciton to run:

// get Header row values as 1-D array
var rowHeaders = ss.getRange(1, 1, 1, lastColumn).getValues()[0];

2) getDoc.gs

This Function gets the body of a blank Google Doc to insert the generated Apps Script code into:

// open Google Doc
var doc = DocumentApp.openById(docID);

// get Doc body for appended code to
var docBody = doc.getBody();

3) addCode.gs

This Function takes the collated items from the above and begins to loop through each column heading. In order to try and generate a suitable variable name we can use a regular expression to remove any spaces from the question title:

// remove any spaces from Header so can form variable name
var varName = headerItem.replace(/\s+/g, '');

Next we want the first letter of the variable to ideally be lowercase to match the JavaScript naming convention. So we pull out the first letter of the question title, set it to lowercase and then add it back onto the original variable:

// set first letter to lowercase to match variable naming standard
var varNameLo = varName.charAt(0).toLowerCase() + varName.slice(1);

 We can then use appendParagraph() to construct and add the 2 lines of code to our Google Doc.

Generate Apps Script code for Forms download.


  1. Clever idea. Love it! An other way, if you ever need it, is you can add a trigger to `FormApp` submission that iterates over `getItems()` to get all of the questions, in order, and associated answers for that submission. This is how I create my `FormApp` notifications. The same ~15 lines of code will work on any Form.

    1. Thank you!
      Cool that's great thanks for sharing - I'll definitely have to take a look at 'getItems()'.

      Kind regards