Tips for debugging Google Form ‘onSubmit’ functions in Google Apps Script

A question came up recently in the Google Apps Script G+ community about accessing submitted Google Form values. It can be tricky to do this sometimes as the Google Apps Script debugger can’t be triggered on the onsubmit event. The form submit event object also returns the data in different ways. My approach to this problem is to temporarily add the line below into your onsubmit handler, editing it to send to my email address, and then completing the Google Form with some test values:

function myOnSubmitHandler(e) {
   MailApp.sendEmail('youremail@example.com', 'test values', JSON.stringify(e));
   // rest of your code
}

[you may need to run the function to trigger the permissions dialog if MailApp is a new scope to your project]

On submission you should receive an email with a stringified version of the event object, for example:

{"values":["02/03/2017 20:42:19","Martin","https://drive.google.com/open?id=0BymYUA-4kKX-Z2JsdUdmVjg4SVE"],"namedValues":{"Files":["https://drive.google.com/open?id=0BymYUA-4kKX-Z2JsdUdmVjg4SVE"],"Timestamp":["02/03/2017 20:42:19"],"Name":["Martin"]},"range":{"columnStart":1,"rowStart":4,"rowEnd":4,"columnEnd":3},"source":{},"authMode":{},"triggerUid":1257191811}

Usually at this point I would paste the email body into one of the many online JSON viewers like JSON Editor Online to see how the data looks:

JSON Editor Online

From this you see how you can access the form data. For example to get the Google Drive file upload you could use either e.values[2] or e.namedValues[‘Files’][0] (this would change if you change the form question order or text.

I was interested to hear what other techniques people used for Google Form debugging so asked the question in the G+ Community. The community came back with a couple of options:

Mocking by +Adam Morris

As Adam explains “there is a concept known as mocking that facilitates debugging of the response handler. What you do is create an “event” object (just a regular javascript object) that contains the values you are debugging for, and send that object to the response handler. Then you can just run the mocking function again and again until you have debugged successfully. In other words, instead of debugging by filling in a form and seeing what happens, you “mock” what happens.”

“Using your JSON.stringify(e) email example would be one way to recreate the mocked object. If you see what I mean, you could fill out the form once and have the raw data be sent to you by email, and then copy and paste that into a function, and send that object on to the response handler. That way you can use the normal Logger.log functions to inspect stuff”:

/* the function that mocks a submission, used for debugging */
function mock_myOnSubmitHandler() {
   var event = {}; // replacing {} with emailed values
   // building the event range
   var r = event.range;
   event.source = SpreadsheetApp.getActiveSpreadsheet();
   event.range = event.source.getSheets()[0]
                             .getRange(r.rowStart,1, 1, r.columnEnd+1);
   myOnSubmitHandler(event);
}

/* the real function that triggers on user submission */
function myOnSubmitHandler(event) {
   // the real function that is triggered
   var sheetName = event.range.getSheet().getName();
   // .. do whatever
}

Firebase Logging by +Riël Notermans

Another suggestion by Riël Notermans was to use Firebase for logging. This requires a bit more initial setup but once done easy to recycle in other projects. There are several advantages of using Firebase, the main being the Firebase console has lets you browse the stored data similar to the JSON viewer mentioned in the previous example:

Firebase Console

This is method can be implemented with a couple of lines of code thanks to the FirebaseApp library developed by Romain Vialard and to use you need to add the library with the key detailed in the code snippet below:

/* Library:
 * MYeP8ZEEt1ylVDxS7uyg9plDOcoke7-2l 
 * 
 * Many greetings from Riël Notermans ;)
 * https://plus.google.com/+rielnotermans
 *
 * 1) Create firebase project on https://console.firebase.google.com/ and get the database URL from the Database tab:
 *    this demo is 'loggerdemo-97c8f'
 *
 * 2) find API key in general settings / service accounts / firebase secrets
 */
var LOGPATH = "this_demo";

function myOnSubmitHandler(event) {
   var fb = FirebaseApp.getDatabaseByUrl( "https://loggerdemo-97c8f.firebaseio.com/", "YOUR_FB_SECRET" );
   fb.pushData( LOGPATH , event);
}

The first step or getting the database url is fairly straightforward. If you are not familiar with Firebase getting the YOUR_FB_SECRET can be more tricky Frank van Puffelen has provided some clear directions copied below:

  1. click on the settings/cog wheel icon next to your project name at the top of the new Firebase Console
  2. Click Project settings
  3. Click on the Service Account tab
  4. Click on the Database Secrets link in the inner left-nav
  5. Hover over the non-displayed secret and click Show

Bonus – Stackdriver Logging

Stackdriver Logging is a Google Apps Script service that’s currently available via one of the Early Access Programs. Stackdriver Logging is primarily designed for situations where you “require logging that persists (up to 30 days), or need a more complex logging solution for a multi-user production environment”. This service has a number of logging methods for errors and timings but you can also send data. For example to log submitted form data you can use:

function myOnSubmitHandler(event) {
   console.log({message: 'Form Submit', formData: event});
}

Similar to Firebase the JSON data can be explored in the Cloud Platform project console:

Stackdriver Logging

Once out of early access this service is ease to enable from the Script Editor:

Console Logging Launch

So there you go a number of different ways you can approach Google Form data debugging :)