google apps script trigger event upon update from external source

Question

I have a Google Sheet that is updated via "Zapier" from my CRM (Capsule) application. I need to auto trigger an email to a given address whenever the spreadsheet is updated. The CRM software successfully adds a new record to the end of the spreadsheet when a new organization is created. I have a script that monitors the last row and sends an email to the addressee by an on change event but this only happens if I go in and change the spreadsheet data myself.

function sendEmail() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var dataRow = sheet.getLastRow();
var emailCell = sheet.getRange(dataRow, 4);
var emailAdd = emailCell.getValues();
var newRecordSource = sheet.getRange(dataRow, 1, 1, sheet.getLastColumn());
var newRecord = newRecordSource.getValues();

//var message = "This record has just been added:" +newRecord;
var message = "New record added to Workflow Sheet!: " +newRecord;
var subject = "Test from Workflow Sheet!";

MailApp.sendEmail(emailAdd, subject, message);

}

Can anyone help?

The Zapier documentation has this: Send Email when Sheet Updated — Alan Wells

Cameron Roberts Cameron Roberts · Accepted Answer · 2015-01-30T13:43:07

If the emails will be sent to a Gmail address then you can do this without Apps Script: Log into the account that will receive the emails, open the relevant spreadsheet, and select "Notification rules..." from the Tools menu and set it up as desired.

If you must do it from Apps Script, you will probably need to use a time-based trigger to monitor for changes, which is considerably more complicated than using onChange, unfortunately.

google apps script trigger event upon update from external source

1 Answers