These days I dealt with a Google Forms application which did many things, breaking a little the standard use cases, and I considered useful for those who want to squeeze more from its functionality, to share some points from the quest of developing it. From the very beginning, Google Forms is a great application if you want to design a form in just a few clicks and collect the submitted data. Very useful when you want to implement a survey or, if you are a teacher, you could easily prepare an examination grid and collect the answers from your students.
Google Forms helps to design a user interface, offering a collection of predefined form controls, allowing some validations over the fields, but it is a little rigid in respect with the look and user experience, I mean here the positioning in the page and customization. In fact, this is not its purpose, and they can be ignored. Once the design of the form being done, the application is ready to collect the submitted data, in a spreadsheet, without writing a line of code.
If you want to do more with your form, Apps Script programming language is your ally. Every application from Google Apps suite has its own programming API, exposing different objects and methods. The application framework supports stand-alone methods and event handlers. You can write modular code spreaded in many distinct files (having .gs extension) but at the end it is the same as they are in a single big file. To debug the code, Google offers an integrated debugger that can execute step-by-step, and a Logger object which helps log some useful data to an execution console. Besides this, there is an Execution Transcript process which logs every interpreted line of code.
Suppose you collect all the data from a form in a spreadsheet, but besides this, you want to have one or more columns in the spreadsheet based on some processed data after the form submission. This is not one of the usual use cases, so a little coding is necessary. First of all, two applications are involved: Google Forms and Google Scripts. The flow could be:
- Collect the data in Google Forms
- Submit the form
- In the form submit handler of Google Forms, open the background spreadsheet and write the processed data
Or the flow could be:
Collect the data in Google Forms
- Submit the form
- In the form submit handler of Google Spreadsheet, get the collected data from the form and write the processed data
Both flows look pretty the same but let's see what happens.
In the first case, the form submission handler is attached to the Google Form app:
function onFormSubmit(e){
var processedData = "Data processed from some of the form fields";
var sheet = SpreadsheetApp.openById("1gIqbtpsttN5Z8EKuHBrA1YI0eqPh6AGQo6tYtsFm7YY");
var activeRange = e.range;
var rangeToWrite = sheet.getRange(activeRange.getRow(), activeRange.getLastColumn()+1);
rangeToWrite.setValue(processedData);
}
|
Running this code will fall into:
Execution failed: You do not have permission to call openById (line XX, file "Code") [0.002 seconds total runtime]
because Google Forms app tried to open the SpreadsheetApp, and I didn’t find any solution to make it work.
A workaround for this situation is to place the code in the onFormSubmit handler of the Google Spreadsheet.
function onFormSubmit(e){
var processedData = "Data processed from some of the form fields";
var sheet = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
var activeRange = e.range;
var rangeToWrite=sheet.getRange(activeRange.getRow(), activeRange.getLastColumn()+1);
rangeToWrite.setValue(processedData);
}
|
In this case the value fields is received through the Event object e and no need to open the Forms App, to fall in the same error, so the write operation works fine.
Other useful things to consider is about sending emails from inside Google Spreadsheets. Usually MailApp is used for this, but you cannot change the “From” field of the email. The From address will be always the email address of the entity that created the trigger for form submission. If GmailApp is used instead of MailApp, the From field can be changed to an alias of the entity that created the trigger, but not to a complete different email address.
