In Google Spreadsheet run a function from cell programmatically

Question

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

Answer 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

Answer 2

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

• How to display a current cell value in sidebar without reload the sidebar?