google script column change email notification

Question

This is my first attempt at writing any kind of script, and also posting a question.

I have created a google sheet, and I would like to send an e-mail notification every time a row is added in this spreadsheet (or to keep things simple, every time a new cell is populated in column A).

I've been searching these forums for a couple of hours, and put together the following script, and tried installing both "onedit" & "onchange" triggers in google scripts but nothing working.

The following links have been helpful, but still not able to get the result I want. No notification is being triggered at all automatically upon the relevant edits.

how to attach onChange cell value event/script to google sheet

email notification if cell is changed

  //Open function

function sendNotification() {

//Get spreadsheet & sheet

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

//Define notification details

var recipients = "myname@email.com";
var subject = "New lead";

var body = 'A new lead has been added to Simba. For message: ' + message + '';

//Specify several sheet variables

var cell = ss.getActiveCell().getA1Notation();
var row = sheet.getActiveRange().getRow();
var cellvalue = ss.getActiveCell().getValue().toString();
var message = '';

//Specify if statement

  if(cell.indexOf('A')!=-1){ 
    message = sheet.getRange('B'+ sheet.getActiveCell().getRowIndex()).getValue()
  }

//Send the Email

MailApp.sendEmail(recipients, subject, body);

//Close function

};

score 0 · Answer 1 · answered Feb 25 '17 at 23:58

var body = 'A new lead has been added to Simba. For message: ' + message + '';

At this line, "message" is undefined. It occurs an error. By this error, mail cannot be sent. Because the definition of "message" is below from this line. So please change this line to below

message = sheet.getRange('B'+ sheet.getActiveCell().getRowIndex()).getValue()

And please install a trigger for this function. The method of installing trigger is shown at your reference URL.

google script column change email notification

1 Answers1