25 January, 2017

Simple notification script for Google Sheets

Google Sheets has a nice feature to notify yourself if something has changed, but every person who wants to be notified must add a notification for themselves. With a simple script, you can send notifications to people of your choosing.

In this example, I've written a script to send an email when 1 or more rows are added.

Get started.

  1. Go to the sheet
  2. Find a cell in the sheet that wont ever be over-written.
    1. Enter the value of the current total number of rows in the sheet.
    2. Note the cell for use in your script. I used P1.
  3. Go to Tools > Script editor...

Write your script.

Add this script in the script editor. Change as required to meet your needs.


function check4NewLines() {
  // get the spreadsheet object
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set the first sheet as active
  SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[0]);
  // fetch this sheet
  var sheet = spreadsheet.getActiveSheet();
  
  // figure out what the last row is
  var lastRow = sheet.getLastRow();
  // the rows are indexed starting at 1, and the first row
  // is the headers, so start with row 2
  var startRow = 2;
  
  // cell used to track last row we checked. See the Get started section in this post.
  var lastRowChecked = sheet.getRange("P1").getValue();
  
  var msg = "";
  var newLines = lastRow - lastRowChecked;
  
  if (newLines > 0) {
    // This is the message that will be emailed.
    //Be sure to add the URL specific to your Google Sheet.    
msg = "There is " + newLines + " or more new entries on the Test sheet: https://docs.google.com/spreadsheets/mySheetURL"
    
    //send notice.
    //Be sure to update with appropriate email address(es). Multiple addresses should be comma delimited.
    MailApp.sendEmail("NotificationEmailAddress@SomeDomain.com", 
        "Email Subject Text Here", msg);
    
    // set new 'last row'
    sheet.getRange("P1").setValue(lastRow);
  }
}

Go to the menu and select File > Save.


Test your script.

Testing is easy.
  1. Make sure your email address is one of the notification email addresses.
  2. Either change the value in the row tracking cell to lower than the number of rows OR add a new row.
  3. Run the script.
    1. Click the 'play' button -- find it left of the 'bug' button.
      OR
    2. Click 'Run' from the menu and select the name of your function.
  4. Wait for your email.

Deploying.

  1. Clean up from your test.
    1. Make sure your last row number (in P1 in my example) is correct.
    2. Make sure the notification emails are correct.
    3. Save the script again if you changed anything, such as the email address(es).
  2. Select Resources > Project triggers from the menu.
  3. Select from the trigger options. For example you can have it check on the hour or once a day.
Need more help? Search Google of course.
Good luck!