Google Spreadsheets: email notifications for single cell

Question

I am looking to set up something where notifications are only sent out if a specific single cell is changed. I have little coding experience and have a general idea of what needs to be in place. From what I have gathered I have created a script but it has notifications for all cell changes. Any suggestions on changing to only notify on single cell would be appreciated.

function emailNotification() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var cell = ss.getActiveCell().getA1Notation();
    var cellvalue = ss.getActiveCell().getValue().toString();
    var recipient = "mail@mail.com";
    var subject = 'Update to '+sheet.getName();
    var body = sheet.getName() + ' has been updated. Visit ' + ss.getUrl() + ' to view the changes on cell: «' + cell + '» New cell value: «' + cellvalue + '»';
    MailApp.sendEmail(recipient, subject, body);
};

score 0 · Answer 1 · answered Dec 03 '15 at 02:32

0

Try changing

var cell = ss.getActiveCell().getA1Notation();
var cellvalue = ss.getActiveCell().getValue().toString();
to
var cell = ss.getRange(row, column); //Put in the row and column of your cell
var cellvalue = cell.getValue().toString();

answered Dec 03 '15 at 02:32

Kelvin Chong

222
1
3
17

score 0 · Answer 2 · answered Dec 04 '15 at 08:42

assuming that with 'changes' you mean manual edits, in order to 'limit' the script to a certain sheet and/or a certain cell you will have to check what the currently edited cell is. For example: if you only want the script to fire off when cell A1 of Sheet1 is edited, try something like:

function emailNotification(e) {
var sheet = e.source.getActiveSheet();
if (sheet.getName() !== 'Sheet1' || e.range.getA1Notation() !== 'A1') return;
var recipient = "mail@gmail.com";
var subject = 'Update to ' + sheet.getName();
var body = sheet.getName() + ' has been updated.\nVisit ' + e.source.getUrl() + ' to view the changes on cell A1.\nNew cell value: «' + e.value + '»';
MailApp.sendEmail(recipient, subject, body);
};

Change the sheet name and cell to suit and see if this works ?

score -1 · Answer 3 · edited Dec 11 '15 at 12:07

-1

var cell = ss.getRange("F2:F50"); //Put in the row and column of your cell

I'm working with this as well. The script is still sending notification if any cell is edited, not just column F

edited Dec 11 '15 at 12:07

Willi Mentzel

27,862
20
113
121

answered Dec 03 '15 at 23:01

Tony D

3
2

JPV you are a hero! Thank you. It is now narrowed down to a single cell. To complete the project, I must send notification if ANY cell in ROW F edited. 'if (sheet.getName() !== 'Sheet1' || e.range.getA1Notation() !== 'A1') return;' I edited 'A1' to read 'F:F'. That didn't work. I also tried 'F2,F3,F4' ..etc. That didn't work either – Tony D Dec 04 '15 at 16:54

Google Spreadsheets: email notifications for single cell

3 Answers3