0

I made an Apps Script custom function in a Google spreadsheet.

I can call it from a cell and works fine (In Google Script, get the spreadsheet cell calling a custom function).

The function makes an API call to retrieve some information from the web. The data changes constantly, so I'd like to execute the function programmatically. I tried using triggers, but the only options I get when choosing the source "from a spreadsheet" are "when open", "when close" or "when change". Then, there is another source for choosing a programmatically call, but executes the function itself, not the one I call from the cell.

I want the value of the cell to be updated each XX seconds (a period of time).

NOTE: My function fetches a value from a free Web API, so I need it to fetch it using an interval of time, let's say every 5 seconds

1

In your comment you mention about an: "...interval of time, let's say every 5 seconds"

This is not possible.

Please check the Quotas for Google Services for further info.

Triggers total runtime  | 90 min / day  | 6 hr / day
0

The only way to fetch a value and add it to a Google Sheets cell every few seconds from a Web API by using Google Apps Script is using client side code to call a server side function. One way to do this is by using a sidebar and setInterval to execute google.script.run periodically.

This will work while the user account doesn't exceed the Google Apps Script quotas.


There are two ways to force a custom function to recalculate:

  1. Change the value of one of it's parameters
  2. Copy the formula, clear the cell, "flush" and add again the formula

The easier is one as you can use set a time driven trigger to change the value of a cell used as a custom function parameter.

=myFunction(arg1,arg2, B1)

Every time that B1 changes, myFunction will be recalculated (please note that custom functions cant't use NOW(), RAND(), RANDBETWEEN() as arguments.

The other method by "flush" I mean using SpreadsheetApp.flush() before adding again the formula.

Unfortunately the shortest period of time for a time driven trigger is one minute.

Related

2
  • Thank you @Rubén but it's not what I need. My function fetches a value from a free Web API, so I need it to fetch it using an interval of time, let's say every 5 seconds – Rodrigo E. Principe Feb 6 at 16:24
  • @RodrigoE.Principe I updated my answer – Rubén Feb 6 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.