How to identify which worksheet triggered an onChange event in Google Sheets

[Zack_45Gaming]

I’m building a script for a collaborative Google Sheets document where multiple users can make edits. I need to track when changes happen on specific worksheets, like when someone adds or deletes rows and columns.

The problem is that while I can detect changes using the onChange trigger, I cannot figure out which specific worksheet was modified. The event object gives me access to the spreadsheet, but when I call getActiveSheet() it always shows the first worksheet instead of the one that was actually changed.

Here’s my test setup with a workbook containing 3 worksheets:

function setupTriggerOneTime() {
  ScriptApp.newTrigger("handleChange")
   .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
   .onChange()
   .create();
}

function handleChange(event) {
  console.log(event)
  console.log("Workbook: " + event.source.getName())
  console.log("Current Sheet: " + event.source.getActiveSheet().getName())
  console.log("Sheet List: " + event.source.getSheets().map(function(sheet){return sheet.getName()}))
}

When I delete a row from the second worksheet, the output shows:

{changeType=REMOVE_ROW, source=Spreadsheet, authMode=FULL}
Workbook: Test Spreadsheet
Current Sheet: Sheet1
Sheet List: Sheet1,Sheet2,Sheet3

Even though I made the change on Sheet2, the event handler reports Sheet1 as the active one. Is there a way to determine which worksheet actually triggered the change event?

[sofiag]

Yes, it’s true that Google Apps Script’s onChange trigger does not specify which sheet was modified, which can be a limitation. The event object primarily provides the change type and the reference to the spreadsheet without detailing the specific sheet involved.

One approach to handle this is to implement a polling system that compares the state of each sheet. You can save checksums or relevant metadata associated with each worksheet and then periodically compare them to detect changes. However, keep in mind that this method may impact performance and will not effectively capture real-time updates.

If applicable for your project, you might consider using onEdit triggers instead. They provide detailed range information, including the specific sheet reference, but this is limited to changes in cell values rather than structural modifications like row or column additions or deletions.

Some developers tackle structural changes by maintaining a separate tracking sheet that logs timestamps and properties of the sheets. This information can then be cross-referenced during onChange events to identify which sheet has changed.

[Claire29]

no direct way to get sheet info from onchange events, unfortunately. I’ve run into this too - ended up storing sheet metadata in scriptproperties before changes happen. when onchange fires, I loop through all sheets and compare row/column counts against the stored values to figure out which sheet changed. It’s hacky but works well for tracking structural changes.

[Gizmo_Funny]

Yes, the onChange trigger in Google Apps Script indeed lacks the capability to specify which sheet was altered, which is frustrating when managing multiple worksheets. One effective workaround is to set up a hybrid monitoring system. You can combine onChange with periodic checks of the properties of each sheet. For instance, use PropertiesService to keep track of row and column counts of each sheet before any changes occur. When an onChange event triggers, compare the current state of the sheets with the previously stored values.

Another solution I’ve implemented involves creating a hidden logging sheet that maintains essential metrics for each worksheet. By utilizing the onChange event to execute a comparison function, you can determine which specific sheet underwent changes with minimal added complexity. Essentially, since onChange doesn’t directly identify the modified sheet, implementing your own detection mechanism can prove beneficial.