Google-sheets – In Google Spreadsheet run a function from cell programmatically

google sheetsgoogle-apps-scriptgoogle-sheets-custom-function

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

Best Answer

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

Related Solutions

Google Sheets – How to Refresh a Cell

Add the following line of code:

SpreadsheetApp.flush()

See reference: flush

Google Sheets – Using Triggers with Parameter-Requiring Functions

How to use triggers with functions that require parameters from the spreadsheet?

AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll, inside which you call all the wrapper functions (GetValue) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob).

Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue and call RealFunctionThatDoesTheJob directly from within the function that is being triggered (RefreshAll).

Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);)

Here is the example:

function RefreshAll() {

  var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");

  var Value1 = GetValue1();
  currentSheet.getRange(Row,Column).setValue(Value1);

  var Value2 = GetValue2();
  currentSheet.getRange(Row,Column).setValue(Value2);   

  var Value3 = GetValue3();
  currentSheet.getRange(Row,Column).setValue(Value3);

}

function GetValue1() {
  return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}

function GetValue2() {
  return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}

function GetValue3() {
  return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}

Best Answer

Related Solutions

Google Sheets – How to Refresh a Cell

Google Sheets – Using Triggers with Parameter-Requiring Functions

Related Topic