0

I have a formula in Google Spreadsheet (Cell G4):

=AVERAGE(INDEX( GoogleFinance(B4 , "all" , WORKDAY( TODAY(), -50 ) , TODAY() ) , , 3))

where, B4=INDEXBOM:SENSEX

I am trying to use following script to send email when cell value changes:

function checkValue()    
{ 
var ss = SpreadsheetApp.getActive(); 
var sheet = ss.getSheetByName("Sheet1");
var valueToCheck = sheet.getRange("G4").getValue();

  if(valueToCheck > 10000)  
    {
      MailApp.sendEmail("xxxxxx@gmail.com", "Subject", "Context" + valueToCheck+ ".");
    }  

}

I set up time driven trigger to automatically run the script every hour. However, script does not send email.

After debugging, I found that since above function takes some time to calculate, valueToCheck output is a string ("#REF!") instead of a number.

Is there a way to work with this? I already tried using Utilities.sleep function to allow spreadsheet to finish calculation before script is executed but I am not successful with it.

I also tried using SpreadsheetApp.flush() but it didn't help as well.

I am not a programmer and have got stuck here. Thanks for help!

Non-programmer
  • 1
  • 2

1 Answers1

0

I suspect following statement given in GoogleFinance function document could be the reason:

Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet.

Non-programmer
  • 1
  • 2