Pages

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.


// ID of blank Google Doc to generate code in
var docID = '';
/*
Get row Headers from Google Sheet for later use in creating code.
MAIN FUNCTION TO RUN.
*/
function getSheetData() {
// get active sheet from spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// get last column so know where Header row ends
var lastColumn = ss.getLastColumn();
// get Header row values as 1-D array
var rowHeaders = ss.getRange(1, 1, 1, lastColumn).getValues()[0];
// run Function to get Google Doc
var docBody = getDoc();
// run Function to append code to Google Doc
addCode(rowHeaders, docBody);
}
/*
Get blank Google Doc where code will later be appended to.
*/
function getDoc() {
// open Google Doc
var doc = DocumentApp.openById(docID);
// get Doc body for appended code to
var docBody = doc.getBody();
// return Doc body to parent Function
return docBody;
}
/*
Append Apps Script code to Google Doc
*/
function addCode(rowHeaders, docBody) {
// get length of Header row so below loop knows when to stop
var headerLength = rowHeaders.length;
// loop through Header row *************************
for (var header=0; header<headerLength; header++) {
// capture Header item as variable so easier to manage
var headerItem = rowHeaders[header];
// remove any spaces from Header so can form variable name
var varName = headerItem.replace(/\s+/g, '');
// set first letter to lowercase to match variable naming standard
var varNameLo = varName.charAt(0).toLowerCase() + varName.slice(1);
// append line of code to Doc body for storing question answers as variables
docBody.appendParagraph("var " + varNameLo + " = formValues['" + headerItem + "'][0];");
// append line of code to Doc body for logging stored question answers
docBody.appendParagraph("Logger.log('" + headerItem + " is: ' + " + varNameLo + ");");
// append empty line to Doc body to separate out code for easier viewing
docBody.appendParagraph("");
}
// loop through Header row *************************
}

Generate Apps Script code for Forms download.

2 comments:

  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.

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

      Kind regards
      Phil

      Delete