0

I have a google sheet that uses custom functions in apps script to retrieve some data from a third party API. Another web service uses a simple HTTP request to get values of cells that are based on those custom function values, with a request like:

https://sheets.googleapis.com/v4/spreadsheets/[SHEETID]/values/[SheetName]?majorDimension=ROWS&key=[API-KEY]

As long as someone is looking at the sheet or has looked at it recently (in the last 15 minutes or so), this all works fine. After about 15 minutes the google sheets API request starts returning #NAME? instead of the cell value.

It seems like the cell values are being cached somewhere and this cache expires after a while. Is there anything I can do to keep the values alive longer or to force the apps script custom functions to execute when the API request comes in?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Bob Wilkinson
  • 1
  • Can you share a copy of your script? – Jason E. Jul 08 '21 at 23:07
  • It's pretty complex and I'm not sure I have time to create a good minimal repro at the moment. I seem to be avoiding the problem by using a timed trigger that increments a cell value every 15 minutes, then adding that as the last argument to every single call to a custom function. It works, for now. – Bob Wilkinson Jul 12 '21 at 19:14

0 Answers0