Getting email notification via Google Sheets when =importxml cell value changes

Question

I'm working on a spreadsheet that uses multiple =IMPORTXML functions to import changing text and price values from a webpage. At this moment I have the following columns in my Google Sheet:

A: 'URL info'
B: 'URL'
F-N: 'Price' (in every column a different price value)

What I have

Via a script, found on this page (thank you Umesh Agarwal) I will receive an email notification once a change has been made within the spreadsheet. Once I make a change in a cell within the range of F2:N200 I will receive an email with the cell that have been changed. The problem is that I have the script to sent me an email with the changed cell once the cell with a value of the =importxml function is changing.

At this moment, when a cell is changing due to the =importxml function the script is sending me an email that cell A1 has changed... it is not sending me the right cell that has been changed which makes it difficult to see what changed. How can I solve this problem?

function sendEmailonEdit() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var cellValue = ss.getActiveSheet().getActiveRange().getA1Notation();
var getColumn = ss.getActiveSheet().getActiveRange().getColumn();
var sheetname = ss.getActiveSheet().getName();
var user = Session.getActiveUser().getEmail();
var Toemail = '[email protected]';
var subject = 'New Entry in ' + data + '.' + ss.getName();
var body = 'Your file has a new entry in - ' + sheetname + ' Updated by - ' + user + data
' check file- ' + ss.getUrl();

if(data.indexOf('F2:N200')!=-1.23456789) {

MailApp.sendEmail(Toemail,subject, body);
 }

};

Answer 1

I'm afraid this is not possible with triggers the way you have it set up.

For the trigger to fire when a formula is pulling data from an external source "On change" is the only trigger that will pick up the change. Unfortunately, it won't return which cell or value has changed, it will only return which sheet has changed.

The other alternative trigger you might run into is "on Edit", however, this trigger will not fire when the sheet is updated by formulas pulling data from an external source ¯\_(ツ)_/¯

Avenue for possible workaround:

You might be able to work around this with a Time-driven trigger AKA clock trigger. Writing a script that fires every so often to check for changes in the worksheet, and to send an e-mail if it does. You might copy all the data to another sheet and then compare the values, or use the Properties Service, to persist data within the script.