Send an email when a new sheet is added to a Google workbook

Question

I have a script which extracts error report data from our CMS, creates a monthly archive sheet and appends it as a new tab to another Google sheet. The sheet name of the new workbook is 'Sheet1' and the new tabs which are created are March, April, May etc. I need to advise our team when a new sheet is added on 1st of each month (I know everyone knows it is the first of the month but getting them to remember to look at the new sheet is very hard!).

I came across this script: How do I set up notifications for when a new sheet is added in Google Sheets?

I have changed the sheet name line to; var sheet = ss.getSheetByName('Sheet1'); and the email address. The script works when I run it manually. Cell A1 increases by one each time and I receive an email. However, when I refresh the script on the CMS error report, it creates the new tab with the name of the month but doesn't trigger the 'notify' function. I only have the one trigger: notify / from spreadsheet / On change.

I feel I must have missed something on the sheet naming but can't see what it is.

Unknown Unknown · Accepted Answer · 2016-05-19T04:57:08

Triggers are fired by user actions. Script activity does not fire them. You can see this with the following test script, where "doSomething" inserts a sheet, and displayEvent shows the events in a browser message. Set an onChange trigger for displayEvent: manual insertion of a sheet will trigger it, but running "doSomething" will not.

function doSomething() { SpreadsheetApp.getActiveSpreadsheet().insertSheet(); }
function displayEvent(e) { Browser.msgBox(JSON.stringify(e)); }

As a workaround, set the script to run at some time intervals, rather than by a trigger (15 minutes, for example). By the way, instead of using cell A1 to store the number of sheets, this number can be stored in Script Properties. Here is how:

function notify() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var scriptProperties = PropertiesService.getScriptProperties();
  var numSheets = scriptProperties.getProperty('Sheets');
  if (numSheets > 0 && ss.getSheets().length > numSheets) {
    MailApp.sendMail('someone', 'subject', 'new sheet')  
  }
  scriptProperties.setProperty('Sheets', ss.getSheets().length);
}

Send an email when a new sheet is added to a Google workbook

1 Answers