2

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.

2

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".
  • Thank you so much for the work around! However, it appears that the script is unable to run for some reason. Here's what I've done so far: 1) I've replaced my email in the placeholder. 2) I've tried to debug the script for any errors. 3) I've tried arranging the sheet 'tracking' to the first and last sheet of the spread. 4) I've set the trigger to run on edit, on change and on open. 5) In the email body variable I tried removing the space before the close quotation marks. 6) I've tried testing and deploying the script as an add on. I will try again with the spreadsheet as open to public. – oneqeightyfour Oct 11 '15 at 7:30
  • The script is still unworkable even with the spreadsheet set to public. I hope to get to the root of this somehow. – oneqeightyfour Oct 11 '15 at 7:36
  • What happens when you try to execute it manually, by "run" button from the script editor? Has it shown a dialog requesting permissions? Also, if the spreadsheet is public, can you share a link to it? – user79865 Oct 11 '15 at 7:36
  • I did that and the prompt window came out the first time round and I approved it. The script seems to be running fine every time I try it manually but somehow the notification mail does not appear in my email (even the spam or trash). Here's the link to the spreadsheet. – oneqeightyfour Oct 11 '15 at 7:45
  • The script tries to write to the cell A1 of "tracking" sheet, but you have something else in there. Perhaps deleting that header row will help? Alternatively, write to the second row: replace (1, 1) with (2, 1) in both places in the script. – user79865 Oct 11 '15 at 7:47

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.