0

I have a script attached to a spreadsheet that is handled by a time trigger. The script is pretty simple; it reads some values from the spreadsheet, then sends them by email.

If a run the script manually when the spreadsheet is open, it works properly, but if the trigger runs the script, it does not. I have done some testing, and when the trigger runs it, the function reads the spreadsheet with some cells empty when they should contain values calculated by complex formulas and queries. The cells that are not calculated by formulas are read fine.

Is there any reason why the spreadsheet does not correctly store the values calculated by the complex formulas when it is not open?

The trigger had been working for about two years, but for some reason, it stopped working some weeks ago.

Any ideas?

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
Ricotero
  • 13
  • 4

1 Answers1

0

Functions like GOOGLEFINANCE doesn't work well with scripts. The problem probably is caused by this or other similar functions.

From the question:

... the function reads the spreadsheet with some cells empty when they should contain values calculated by complex formulas and queries. The cells that are not calculated by formulas are read fine.

There are several types of spreadsheet functions,

  • basic functions like ADD, SUM
  • server functions like GOOGLEFINANCE, IMPORTRANGE
  • volatile functions like NOW, RAND

Basic functions usually work well but the server and volatile functions might cause that the spreadsheet recalculation exceeds the maximum time for this or doesn't work at all.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166