I do not know GAS and I tried searching a lot of codes and found one very close.
I wish to receive email notification when the data in the current sheet changes. Right now when I make changes, I get an email. Which is fine but I will not make changes to the sheet actually because the data is being imported from another sheet/website
Basically I wish to
Dummy Sheet: https://docs.google.com/spreadsheets/d/1BTdA7Rx16W6la_gri4uP079yIzzgKfZKO610yfQ6724/edit?usp=sharing [Triggers Not Working on Imported Data]
Current Script:
function sendNotification() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var cell = ss.getActiveCell().getA1Notation();
var row = sheet.getActiveRange().getRow();
var cellvalue = ss.getActiveCell().getValue().toString();
var to = "ashwendra10625@gmail.com";
var message = '\n\nSignature';
if(cell.indexOf('G')!=-1){
message = sheet.getRange('D'+ sheet.getActiveCell().getRowIndex()).getValue()
}
var subject = 'Data Updated';
var body = 'Hi there,\n\nThe data on website was updated recently.\n\nKindly refresh the previous excel sheet or click ' + 'https://docs.google.com/spreadsheets/d/1BTdA7Rx16W6la_gri4uP079yIzzgKfZKO610yfQ6724/edit#gid=0' + ' to see changes.\n\n\nThe changes are:\nTotal Rows affected: ' + row + '\n' + message;
MailApp.sendEmail(to, subject, body);
};
Following on from cooper's idea:
Use the property service to store the value of the length of data in col A (for example). Create a time-based trigger to check the sheet every X minutes. If the data changes then update the property with the new value and send the email.
Depending on your data you can choose whatever range to test or if you have timestamps you could change the code to test for changes in time.
function onEdit() {
var getProps = PropertiesService.getUserProperties();
var lenProp = getProps.getProperty('Len');
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('-------');
var data = sheet.getRange('A1:A').getValues().toString().length;
if (data != lenProp) {
getProps.setProperty('Len', data);
MailApp.sendEmail('-------------', 'Sheet Has Changed', 'Previous value: ' + lenProp + ' New value: ' + data);
}
}