Google Sheets – Receive Notification When Specific Tab Changes

Once you are running code in the Sheet, you can loop through the column to see if the values you care about are set or not. You can then only send the email if your criteria are met. Keep in mind that if your logic is if (empty > 20) you will get an email on entry 20, 21, 22, etc. You may want to send one every 5 entries over 20 or something like that.

One approach is to use the event object: the eventType property of INSERT_GRID means a sheet was added. Here is such a script. You should fill in YOUR_EMAIL placeholder, and set a trigger (using Resources > Current project's triggers) to run the function "notify" on every change.

function notify(e) {
  var ss = e.source;
  if (e.changeType == 'INSERT_GRID') {
    var subject = 'New sheet added to ' + ss.getName();
    var body = 'New sheet was added to spreadsheet ' + ss.getName() + ' located at ' + ss.getUrl();
    MailApp.sendEmail('YOUR_EMAIL', subject, body);      }
}

Another approach is to use a script to store the number of sheets, and compare the stored and current values. It should be triggered in the same way as the first version.

function notify() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var scriptProperties = PropertiesService.getScriptProperties();
  var numSheets = scriptProperties.getProperty('Sheets');
  if (numSheets > 0 && ss.getSheets().length > numSheets) {
    var subject = 'New sheet added to ' + ss.getName();
    var body = 'New sheet was added to spreadsheet ' + ss.getName() + ' located at ' + ss.getUrl();
    MailApp.sendEmail('YOUR_EMAIL', subject, body);      }
  scriptProperties.setProperty('Sheets', ss.getSheets().length);
}

Insertion by other scripts

The above assumes that sheets are only added to the spreadsheet manually. If some other script adds a sheet programmatically, this will not trigger the "change" event: triggers are only fired by user actions.

So, if you expect the sheets to be modified programmatically, there are two options:

1. The script that adds a sheet should send an email itself.
2. The function notify (second version, not the first) should be set to run at some intervals (for example every hour) instead of being triggered "onChange".