Google Sheets – Set Up Notifications for New Sheets

gmailgoogle sheetsgoogle-apps

I am part of a large group of users that share a Google spreadsheet and would appreciate if anyone can help me figure a way to set up immediate notifications specifically only for when someone adds a new sheet to the spreadsheet.

I've tried setting up a notification rule through the sheet's tools for when any changes are made and when a new form is submitted but this will prove troublesome even with both the immediate email and daily digest email options as the spreadsheet is being shared by a large group who edit individual cells frequently in a day and also because the rules available currently will result in me receiving a throng of emails but I'm only interested in getting notifications (imperative for it to be immediate) from only two specific users in the group.

I do understand that it might be possible to write a script for this but I have no background or understanding of scripts and also I could not find a script for this in the Google script bank or the new add-ons feature. I have also done an intensive internet search on forums and backlog stack questions but to no avail. Would it also be possible to set notification email to be only sent to me personally and not anyone else on the list?

I hope I've been clear with my questions and would appreciate any help in this matter.

Best Answer

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".
Related Topic