I am working with a Google Docs sheet that many people will have access to edit. I need to be able to tell when sheets are changed, i.e. added or removed rows or columns. I am able to get an onChange event when this occurs, but I can't seem to find which sheet was changed. The event contains a reference to the SpreadSheet object but getActiveSheet always returns the first sheet regardless of which sheet was active.
I created a new spreadsheet with 3 sheets, added the following script and authorized it.
function runMeOneTimeToSetUpTheTrigger() {
ScriptApp.newTrigger("onChange")
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onChange()
.create();
}
function onChange(e) {
Logger.log(e)
Logger.log("Name: " + e.source.getName())
Logger.log("Active Sheet: " + e.source.getActiveSheet().getName())
Logger.log("All Sheets: " + e.source.getSheets().map(function(s){return s.getName()}))
}
The I went to Sheet 2 and deleted a column. The log showed:
[14-07-17 19:13:32:774 EDT] {changeType=REMOVE_COLUMN, source=Spreadsheet, authMode=FULL}
[14-07-17 19:13:32:861 EDT] Name: Untitled spreadsheet
[14-07-17 19:13:32:944 EDT] Active Sheet: Sheet1
[14-07-17 19:13:32:946 EDT] All Sheets: Sheet1,Sheet2,Sheet3
So event though I had Sheet 2 active and the operation affected only sheet 2, the event passed to the onChange handler seems to indicate that Sheet 1 was active at the time.
How can I find out which sheet was really changed?
It looks like the change event isn't supposed to return 'source' even though it does in the logs.
I'm having the same behaviour as you. I am able to get the sheet name with 'on edit' but that doesn't suit what you're trying to do.
I also tried using a combindation of onedit and onchange triggers, but they won't fire at the same time, only one or the other.