Custom functions in Google Sheets - auto-refreshing

0
votes

I am using Google Sheets to parse API data from an external data provider - I created a number of custom functions via the script editor that will ping this data provider using my unique key, and parse the data into a series of tables.

The way the API is set up I request category data in column A and the data is parsed into Columns B-F. I have a logic clause where function will not run unless I set a separate cell to "On" -the reason being that I am charged for every call that I make. If after calling the data I set the cell to "Off" (or anything other than "On") the data disappears, so I generally leave it set to On so I can work with the data.

I notice that on occasion, google sheets is auto-refreshing/re-calling my functions, as if I had set the button to "Off" and then to "On" again. This is a problem because essentially I am being charged again for data I already pulled. I can confirm 100% that this is without me touching anything - sometimes it happens after a few minutes, sometimes after a few hours.

Is this a known issue/feature with google sheets? Is there anyway I can prevent this from happening? Once I call the data I don't want to "re-call" it for any reason.

EDIT - to try and give more specifics

To try and give a visual example, A1 can bet set to "On" or "Off". A5:A100 is where I enter the categories and B5:B100 has the below (using B5 as an example)

=IF($A$1="ON",Transpose(CustomFunction(A5)),"Button Off")

So when I enter the sheet, the button is set to "On" and data is already parsed in B5:F5, once in awhile I'll see the "Loading..." as if I re-ran everything. This happens without me touching anything

1
google-sheets
As far as I know, Google Sheets make no firm promise regarding when the custom functions are recalculated. If I were in your position, I'd implement this as a function invoked by a menu command "pull data". This gives (a) full control over when it's invoked; (b) the latest data always stays in the spreadsheet, instead of disappearing when "Off" switch is slipped in your case. - user3717023

1 Answers

1
votes

Your custom function could:

  • Use the Cache Service with a very long time expiration time, up to a maximum of 21600 seconds (6 hours). This would still perform the external call several times a day, so it's not exactly what you're looking for.

  • Use the Properties Service to store the fetched information. You could pass in a unique key as a parameter, e.g. the cell id in A1Notation. Then the custom function would simply return stored info if it has it, or go get it and store it if it doesn't.

If you really need something that only runs once, don't use a custom function. As per soup's suggestion, a menu-driven function would be more appropriate.