Use Google Sheet Formula in Script

Question

After each time a new entry is added to my google sheet I need to have a script validate a combination of cells then make sure the output of the validation has changed before sending an email. I know how to do the validation and provide the correct output using a formula in the spreadsheet and I know how to have the script check that field and run. This works when someone edits the sheet from the UI. The problem is the spreadsheet is not being updated via the UI (it's being done via a IFTTT recipe), so I don't think the validation field is recalculating before the script runs who means no email is sent.

The following formula and script work perfectly when it is updated via the UI. My question is - is there a way to calculate the "value" variable from the script below with the formula from the spreadsheet?

Formula which is in Cell "D2" of Sheet "state":

=IF((COUNTIF(INDEX(data!A1:D5032,(SUMPRODUCT(MAX((data!A:A="meloc")*(ROW(data!A:A))))),4),"entered")+COUNTIF(INDEX(data!A1:D5032,(SUMPRODUCT(MAX((data!A:A="youloc")*(ROW(data!A:A))))),4),"entered"))>0,9999999999999,1111111111111)

Script that works fine with onEdit trigger when sheet is edited via the UI:

function myNotification() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var value = ss.getSheetByName("state").getRange("D2").getValue();
  if( value > 0 ) {
    var last = ScriptProperties.getProperty("last");
    value = value.toString();
    if( value != last ) {
      MailApp.sendEmail('[email protected]', 'Status #'+value+'\n\n',
                        '1="not here", 9="here": '+value+'\n\n', {
                        cc: '[email protected]'
                        });
      ScriptProperties.setProperty("last", value);
    }
  }
}

Zig Mandel Zig Mandel · Accepted Answer · 2015-05-28T05:26:02

what you found cannot be worked arround. currently those triggers do not get called when external code uses the spreadsheets or drive api to change contents.

if you need it to work realtime, its not possible. else if you can live with a 1 minute delay, you could use a recurring 1minute trigger to check if the last modified date has changed on the spreadsheet ( by comparing with previously stored on script properties).

if it changed, process the entire sheet again.

Use Google Sheet Formula in Script

1 Answers