Google spreadsheet custom function: How to get a continuously updated valued?

2
votes

I wrote a custom google app script function in a script associated with my google doc spreadsheet. The function calls a third party service to get data. I can put the function in a cell:

=myfunction("something")

and it returns the correct value from the service. However, how can I keep this value updated so that it's showing the latest data from the service?

Update

For example:

=temperature("90120")

For getting the current temperature in a given zip code. Also my sheet may have dozens or hundreds of these so I'd prefer something that is performant and maintainable. It doesn't truly need to be continuous, polling once a minute or ideally more frequently could work. I'm wondering if there's some way from the script to set a timer to run to update a range of cells?

2
google-apps-scriptgoogle-sheetscustom-function
It's not continually: =myfunction("something", GoogleClock()) where GoogleClock will fire once per minute which should cause your custom function to recalculate. – ScampMichael
@ScampMichael: thanks looks useful for one-off calculations, although I will have this in many cells so I'd rather have something simpler to type – User
Perhaps creating another sheet, in which the ZIP codes are being gathered by the FILTER funtion. You can prepare a script that fetches the temp. and set a trigger on the script for lets say every minute. By using VLOOKUP, you can retrieve the up-to-date temp. for a given ZIP code. – Jacob Jan Tuinstra

2 Answers

0
votes

Not sure why you need dozens or hundreds. 1. Is the spreadsheet used by another process? 2. Is the spreadsheet visually reviewed by actual users?

If #1, you could replace the spreadsheet with a custom API via the content service to return JSON results for all temperatures.

If #2, you may hit limits or performance issues with so many functions firing so often. Why should fire the functions if no one is viewing the results. Alternatively, you could make it an on-demand with a custom menu option.

0
votes

I have a similar problem.

This is how I am doing it atm, but its not the best solution. I am looking for a better one.

If any value at sheet Prices and column D changes. Meaning if any cell value changes in the whole column it updates the custom function value.

//Search Price sheet with the given name. Return price. dummy param updates google ss once the "Prices" sheet values changed.
function searchPrice(price,dummy)
{
  var SPREADSHEET_NAME = "Prices";
  var SEARCH_COL_IDX = 2;
  var RETURN_COL_IDX = 3;
  var values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();
  for (var i = 0; i < values.length; i++)
  {
    var row = values[i];
    if (row[SEARCH_COL_IDX] == price)
    {
      return row[RETURN_COL_IDX];
    }
  }
}

This is how you call it =searchPrice(B8,Prices!D:D)

Just give your custom function a dummy param. It doesn't do anything in the custom function.