How can I test a trigger function in GAS?

Question

Google Apps Script supports Triggers, that pass Events to trigger functions. Unfortunately, the development environment will let you test functions with no parameter passing, so you cannot simulate an event that way. If you try, you get an error like:

ReferenceError: 'e' is not defined.

Or

TypeError: Cannot read property *...* from undefined

(where e is undefined)

One could treat the event like an optional parameter, and insert a default value into the trigger function using any of the techniques from Is there a better way to do optional function parameters in JavaScript?. But that introduces a risk that a lazy programmer (hands up if that's you!) will leave that code behind, with unintended side effects.

Surely there are better ways?

Answer 1

You can write a test function that passes a simulated event to your trigger function. Here's an example that tests an onEdit() trigger function. It passes an event object with all the information described for "Spreadsheet Edit Events" in Understanding Events.

To use it, set your breakpoint in your target onEdit function, select function test_onEdit and hit 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 curious, this was written to test the onEdit function for Google Spreadsheet conditional on three cells.

Here's a test function for Spreadsheet Form Submission events. It builds its simulated event by reading form submission data. This was originally written for Getting TypeError in 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);
  }
}

Tips

When simulating events, take care to match the documented event objects as close as possible.

• If you wish to validate the documentation, you can log the received event from your trigger function.

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

• In Spreadsheet form submission events:

  • all namedValues values are arrays.
  • Timestamps are Strings, and their format will be localized to the Form's locale. If read from a spreadsheet with default formatting^*, they are Date objects. If your trigger function relies on the string format of the timestamp (which is a Bad Idea), take care to ensure you simulate the value appropriately.
  • If you've got columns in your spreadsheet that are not in your form, the technique in this script will simulate an "event" with those additional values included, which is not what you'll receive from a form submission.
  • As reported in Issue 4335, the values array skips over blank answers (in "new Forms" + "new Sheets"). The filter(Boolean) method is used to simulate this behavior.

---

^*A cell formatted "plain text" will preserve the date as a string, and is not a Good Idea.