Google app script integration event

apt · 08-20-2021 12:18 PM

Hi! There is a post (How to Integrate Google Apps Script & Trigger with an AppSheet App) describing a way to “capture” AppSheet change on the spreadsheet and continue the workflow on our Google Workspace side.

The thing is, maybe things changed, but the OnChange event which is described as the right one for this integration doesn’t provide the source object anymore (Event Objects | Apps Script | Google Developers). So we can’t find out what was changed and the actions to take.

Does anyone know how to do this integration?

Marc_Dillon

Did you actually try it? I believe it still does, regardless of what that doc says.

apt

Of course I tried. And tried many different ways, like trying to getting activeRange and selecting Spreadsheet and sheet from ID. Which doesn’t provide the target range that AppSheet is editing.

Source object is empty as you can see in the attached image, each trigger makes 2 changes: OTHER and EDIT. Probably adding row and writing values to cells.

Marc_Dillon

I have a manually installed on change trigger for this script

Which as you can see uses e.source

This script has been running successfully for many months.

Marc_Dillon

Coming back to this to update since I created a new onChange script today.

onChange Event Object definitely does include a source object. I do not know why it shows as blank in the log output, it does for me as well. JSON.stringify(e.source) does not work either. Maybe the logging system decided it was too much data to output?

You also need a certain oAuth scope for your script, which may not have been added if you were missing some bits of code. See this stackoverflow question:

Once you have that figured out, you can get your range via

e.source.getActiveSheet().getActiveRange()