How to check trigger function in GAS?

Question

Google-apps-script supports Triggers , which pass Events to run functions. Unfortunately, the development environment allows you to test functions without passing parameters, so you cannot simulate an event this way. If you try, you will get an error message:

ReferenceError: 'e' is not defined. 

You can consider the event as an optional parameter and insert the default value in the trigger function using any of the methods from Is there a better way to make optional function parameters in Javascript? "But that poses a risk for the lazy programmer (getting up if it's you!) Will leave this code behind, with unintended side effects.

Surely there are better ways?

Answer 1

You can write a test function that passes the simulated event to your trigger function. Here is an example that tests the onEdit() trigger function. It passes the event object with all the information described for "Editing Spreadsheets" to the General Event Information .

To use it, set a breakpoint in the onEdit target function, select the test_onEdit function and click Debug .

 /** * Test function for onEdit. Passes an event object to simulate an edit to * a cell in a spreadsheet. * * Check for updates: https://stackoverflow.com/a/16089067/1677912 * * See https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events */ function test_onEdit() { onEdit({ user : Session.getActiveUser().getEmail(), source : SpreadsheetApp.getActiveSpreadsheet(), range : SpreadsheetApp.getActiveSpreadsheet().getActiveCell(), value : SpreadsheetApp.getActiveSpreadsheet().getActiveCell().getValue(), authMode : "LIMITED" }); } 

If you're interested, this was written to test the onEdit function for Google Sheets on three cells .

Here is a test function for spreadsheet form submission events. He creates his simulated event by reading form submission data. Was it originally written to get a TypeError in an onFormSubmit trigger? .

 /** * Test function for Spreadsheet Form Submit trigger functions. * Loops through content of sheet, creating simulated Form Submit Events. * * Check for updates: https://stackoverflow.com/a/16089067/1677912 * * See https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events */ function test_onFormSubmit() { var dataRange = SpreadsheetApp.getActiveSheet().getDataRange(); var data = dataRange.getValues(); var headers = data[0]; // Start at row 1, skipping headers in row 0 for (var row=1; row < data.length; row++) { var e = {}; e.values = data[row].filter(Boolean); // filter: https://stackoverflow.com/a/19888749 e.range = dataRange.offset(row,0,1,data[0].length); e.namedValues = {}; // Loop through headers to create namedValues object // NOTE: all namedValues are arrays. for (var col=0; col<headers.length; col++) { e.namedValues[headers[col]] = [data[row][col]]; } // Pass the simulated event to onFormSubmit onFormSubmit(e); } } 

Advice

When simulating events, try to match the objects of the documented events as close as possible.

• If you want to check the documentation, you can register the received event from your trigger function.

   Logger.log( JSON.stringify( e , null, 2 ) ); 

• In spreadsheet form submission events:

  • all namedValues ​​are arrays.
  • Timestamps are strings, and their format will be localized in the form locale. If you read from a table with default formatting ^* these are Date objects. If your trigger function depends on the format of the timestamp string (which is a bad idea), make sure you mimic the value correctly.
  • If you have columns in your spreadsheet that are not in your form, the method in this script will simulate an “event” with additional values ​​added, which is not what you get from the form view.
  • As reported in Issue 4335 , the values array skips empty responses (in "new forms" + "new sheets"). The filter(Boolean) method is used to model this behavior.

---

^* Formatted in a cell "plain text" will save the date as a string and is not a good idea.

Answer 2

Update 2017: Debugging Event Objects with Recording in Stackdriver for Google Apps Script. In the menu bar in the script editor go to: View > Stackdriver Logging to view or stream logs.

console.log () will write DEBUG level messages

OnEdit () example:

 function onEdit (e) { var debug_e = { authMode: e.authMode, range: e.range.getA1Notation(), source: e.source.getId(), user: e.user, value: e.value, oldValue: e. oldValue } console.log({message: 'onEdit() Event Object', eventObject: debug_e}); } 

Example onFormSubmit () :

 function onFormSubmit (e) { var debug_e = { authMode: e.authMode, namedValues: e.namedValues, range: e.range.getA1Notation(), value: e.value } console.log({message: 'onFormSubmit() Event Object', eventObject: debug_e}); } 

Example onChange () :

 function onChange (e) { var debug_e = { authMode: e.authMode, changeType: changeType, user: e.user } console.log({message: 'onChange() Event Object', eventObject: debug_e}); } 

Then check the logs in the Stackdriver UI marked as message string to see the output