1

I'm using a Google spreadsheet. I'm attempting to create an onChange trigger that will activate a script when any sheet is added or deleted. I was able to get the script to work when a new sheet is added using changeType === "INSERT_GRID". However when I'm using changeType === "REMOVE_GRID" the script does not activate when a sheet is deleted. Maybe I'm misunderstanding what REMOVE_GRID does?

Here is a simplified version of what I was trying to achieve:

function deleteSheet(e) {
  if (e.changeType === "REMOVE_GRID") {      
e.source.getSheetByName("Sheet1").getRange(1, 1).setValue("Deleted a sheet");
  }
}

I did change the trigger to: deleteSheet --> spreadsheet --> onChange

Any help would be appreciated! Also this is my first time posting so I apologize in advance for any formatting errors. Thanks!

  • 2
    Add the execution transcript. – Rubén Jun 29 '18 at 15:52
0

You can check to see what type of event is getting passed into that trigger by adding:

Browser.msgBox(Utilities.jsonStringify(e))

at the top. This will also confirm that the function is getting triggered.

From: https://stackoverflow.com/questions/17750589/google-apps-event-changetype-undefined-in-google-sheet/17751347#17751347

  • Thanks you! Tried adding that in and see what appears. Showing up as "REMOVE_GRID" whenever I delete a sheet (as it should). I'm using a conditional statement and it's evaluating as true but it does not execute the commands. Very frustrating. The strange thing is that it appears to work/execute for all other types of changeTypes (INSERT_ROW, INSERT_COLUMN, etc.). So this works: if (e.changeType === "INSERT_GRID") { e.source.getSheetByName("Sheet1").getRange(1, 1).setValue("Deleted a sheet");} But if I change it to "REMOVE_GRID", it doesn't execute the command. Thanks! – Petrus Jun 29 '18 at 21:00
  • Are there any errors in the logs? You can turn on immediate error notifications from where you set up the trigger – Josh Jun 29 '18 at 21:00
  • maybe there is an issue w/ your code inside the conditional? You can add the browser box inside the conditional to see if it's getting triggered. Or maybe e.source isn't what you think it is? – Josh Jun 29 '18 at 21:01
  • I think that there is a bug when using getSheetByName on function called by an installable change trigger. It doesn't work with e.source and even with SpreadsheetApp.getActive() – Rubén Jun 30 '18 at 23:11
0

Short answer

Instead of e.source.getSheetByName("Sheet1") use e.source.getSheets()[i] wher i is the index number corresponding to the Sheet1

Explanation

I made some tests using SpreadsheetApp.getActive() instead of e.source and using getSheets()[i] instead of getSheetByName("Sheet1"). getSheetByName throw the error

The sheet was not found.

when the change type is REMOVE_GRID

It looks to be a bug that is not reported yet on Apps Script Issue Tracker.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.