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.
- Go to the sheet
- Find a cell in the sheet that wont ever be over-written.
- Enter the value of the current total number of rows in the sheet.
- Note the cell for use in your script. I used P1.
- 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("[email protected]",
"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.
- Make sure your email address is one of the notification email addresses.
- Either change the value in the row tracking cell to lower than the number of rows OR add a new row.
- Run the script.
- Click the 'play' button -- find it left of the 'bug' button.
OR - Click 'Run' from the menu and select the name of your function.
- Wait for your email.
Deploying.
- Clean up from your test.
- Make sure your last row number (in P1 in my example) is correct.
- Make sure the notification emails are correct.
- Save the script again if you changed anything, such as the email address(es).
- Select Resources > Project triggers from the menu.
- 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!
