Refresh data retrieved by a custom function in Google Sheet

Question

I've written a custom Google Apps Script that will receive an id and fetch information from a web service (a price).

I use this script in a spreadsheet, and it works just fine. My problem is that these prices change, and my spreadsheet doesn't get updated.

How can I force it to re-run the script and update the cells (without manually going over each cell)?

Surfbird

For those encountering similar (defined and logical, but sometimes unfortunate) behavior, it might help to go upvote this feature request in Google Issue Tracker: issuetracker.google.com/issues/36763858. – Serviceman

Here is a simple answer I did. – Fraunhofer

Answer 1

Ok, it seems like my problem was that google behaves in a weird way - it doesn't re-run the script as long as the script parameters are similar, it uses cached results from the previous runs. Hence it doesn't re-connect to the API and doesn't re-fetch the price, it simply returns the previous script result that was cached.

See more info here(Add a star to these issues, if you're affected):

• https://issuetracker.google.com/issues/36753882
• https://issuetracker.google.com/issues/36763858

and Henrique G. Abreu's answer

My solution was to add another parameter to my script, which I don't even use. Now, when you call the function with a parameter that is different than previous calls, it will have to rerun the script because the result for these parameters will not be in the cache.

So whenever I call the function, for the extra parameter I pass "$A$1". I also created a menu item called refresh, and when I run it, it puts the current date and time in A1, hence all the calls to the script with $A$1 as second parameter will have to recalculate. Here's some code from my script:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Refresh",
    functionName : "refreshLastUpdate"
  }];
  sheet.addMenu("Refresh", entries);
};

function refreshLastUpdate() {
  SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());
}

function getPrice(itemId, datetime) {
  var headers =
      {
        "method" : "get",
        "contentType" : "application/json",
        headers : {'Cache-Control' : 'max-age=0'}
      };

  var jsonResponse = UrlFetchApp.fetch("http://someURL?item_id=" + itemId, headers);
  var jsonObj = eval( '(' + jsonResponse + ')' );
  return jsonObj.Price;
  SpreadsheetApp.flush();
}   

And when I want to put the price of item with ID 5 in a cell, I use the following formula:

=getPrice(5, $A$1)

When I want to refresh the prices, I simply click the "Refresh" -> "Refresh" menu item. Remember that you need to reload the spreadsheet after you change the onOpen() script.

Surfbird

why not using now() as an additional parameter? – Prescription

You see, just like my function will not be re-evaluated because it's parameters haven't changed (ie the values of the cells), now() will not be re-evaluated either since it has no parameters, hence it's return value will not change and so my function's parameters will not change. Also, using now() would cause my function to re-evaluate all the time which is a bit heavy considering that it generates several HTTP calls... – Surfbird

Good find. I had this problem while using named ranges as input. Using your answer, I figured out it's often good enough to pass a dummy sum over the range of inputs, as in "sum(B:D)", where rows B-D are in the range looked up by the custom function. Changing any cell will trigger the sum to change and the custom function to refresh. By the way, it seems the custom function does not even have to declare the ignored parameter. – Olson

A shame that this is still the best solution I found to this problem. Rather than Google just allowing us to disable cache for particular function calls, we arbitrarily increase what is being stored in the cache just to make sure we don't get a cached value... Still, thanks for post, – Lemkul

Try using a parameter for your custom function like this example – Fraunhofer

Looks like addMenu is deprecated: developers.google.com/apps-script/guides/menus – Aviva

Conveniently, you don't need to add the parameter to the script. So you can leave the script as function getPrice(itemId) and just call it as =getPrice(5, $A$1). The extra argument seems to be ignored. For me, this is best. All I then do is type a single character into A1 to trigger a refresh. As quick as using a menu item and more convenient to setup. – Esra

Answer 2

You're missing the fastidious caching bug feature. It works this way:

Google considers that all your custom functions depend only on their parameters values directly to return their result (you can optionally depend on other static data).

Given this prerequisite they can evaluate your functions only when a parameter changes. e.g.

Let's suppose we have the text "10" on cell B1, then on some other cell we type =myFunction(B1)

myFunction will be evaluated and its result retrieved. Then if you change cell B1 value to "35", custom will be re-evaluated as expected and the new result retrieved normally. Now, if you change cell B1 again to the original "10", there's no re-evaluation, the original result is retrieved immediately from cache.

So, when you use the sheet name as a parameter to fetch it dynamically and return the result, you're breaking the caching rule.

Unfortunately, you can't have custom functions without this amazing feature. So you'll have to either change it to receive the values directly, instead of the sheet name, or do not use a custom function. For example, you could have a parameter on your script telling where the summaries should go and have an onEdit update them whenever a total changes.

Crossed

Hmmm, that is annoying, but thankyou for the answer! I have got a nasty workaround by adding a dummy parameter to the function; if I then pass a cell reference to that I can force the data to refresh by just incrementing a number in that cell. Don't suppose there is anyway I can create a refresh button that will do that on a click, is there? That would be fine, it doesn't need to be automatic really, just easy and obvious. – Republic

Well, you can create a button, actually a drawing and assign an script function to it, that will increment you dummy parameter cell that will force the update. – Crossed

"For example, you could have a parameter on your script telling where the summaries should go" - what summaries? – Biliary

You can invalidate the cache by storing a value in the cell, then storing the formula again. – Biliary

Answer 3

What I did was similar to tbkn23. This method doesn't require any user action except making a change.

The function I want to re-evaluate has an extra unused parameter, $A$1. So the function call is

=myFunction(firstParam, $A$1)

But in the code the function signature is

function myFunction(firstParam)

Instead of having a Refresh function I've used the onEdit(e) function like this

function onEdit(e)
{
   SpreadsheetApp.getActiveSheet().getRange('A1').setValue(Math.random());
}

This function is triggered whenever any cell in the spreadsheet is edited. So now you edit a cell, a random number is placed in A1, this refreshes the parameter list as tbkn23 suggested, causing the custom function to be re-evaluated.

Hedgepeth

Works great. Downside is, undo (ctrl+z) history is messed up. – Amoeboid

Nice trick with an annoying downside as Jon pointed out... I hope someone will improve it :-) – Corkhill

Small and pedantic caveat with this answer; in the incredibly unlikely event Math.random returns the same number, on that occasion it will not update, as that particular number has already been cached. – Cierracig

Doesn't work at scale and in highly dynamic situations unfortunately. – Warmongering

Answer 4

There are settings where you can make NOW() update automatically:

Punctual

NOW() is a built-in function, not a custom function. – Ddt

@Rubén The point is you could include NOW() function in any custom function you want to have it update – Punctual

At this time custom functions arguments should be deterministic, in other words, they don't allo NOW() as argument. See developers.google.com/apps-script/guides/sheets/functions – Ddt

It throws an error if you try to use NOW() inside a custom function – Heavyarmed

Answer 5

If your custom function is inside a specific column, simply order your spreadsheet by that column.

The ordering action forces a refresh of the data, which invokes your custom function for all rows of that column at once.

Melon

only works if the sheet isn't already ordered – Mutter

This is a particular case of asking the user to make a pervasive change to the sheet. It's simpler to insert a blank first row, then delete it, actually. But a good solution would require less or no user intervention. – Biliary

Answer 6

Script Logic:

• Custom Functions don't update unless it's arguments changes.

• Create a onChange trigger to change all arguments of all custom functions in the spreadsheet using TextFinder

• The idea to add a extra dummy parameter by @tbkn23 and use of the triggers by @Lexi Brush is implemented here with a random number as argument. This answer mainly differs due to usage of class TextFinder(a relatively new addition to Apps script), which is better because

  • No extra cell is required.

  • No menu is needed > No additional clicks needed. If you need a custom refresher, a checkbox is a better implementation

  • You can also change the formula itself instead of changing the parameters

  • The change/trigger can be configured to filter out only certain changes. For eg, the following sample script trigger filters out all changes except INSERT_GRID/REMOVE_GRID(Grid=Sheet). This is appropriate for the custom function that provides sheetnames. A edit anywhere isn't going to change the list of sheets/sheetnames, but inserting or removing sheet does.

Sample custom function(to refresh):

/**
 * @customfunction
 * @OnlyCurrentDoc
 * @returns Current list of sheet names
 */
const sheetNames = () =>
  SpreadsheetApp.getActive()
    .getSheets()
    .map((sheet) => sheet.getName());

Refresher function:

/**
 * @description Automatically refreshes specified custom functions
 * @author TheMaster https://stackoverflow.com/users/8404453
 * @version 2.0.0
 * @changelog 
 *    Updated to support all custom functions and arguments
 *    Avoid eternal loops
 */

/**
 * @listens to changes in a Google sheet
 * @see https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually
 */
function onChange(e) {
  /* Name of the custom function that is to be refreshed */
  const customfunctionName = 'SHEETNAMES',
    regexPattern = `=${customfunctionName}${String.raw`\(([^)]*?)?(?:,\s*?"RANDOM_ID_\d+")?\)`}`,
    replacementRegex = `=${customfunctionName}${String.raw`($1,"RANDOM_ID_${
      Math.floor(Math.random() * 500) + 1
    }")`}`;

  /* Avoid eternal loop
   * Increase timeout  if it still loops
   */
  const cache = CacheService.getScriptCache(),
    key = 'onChangeLastRun',
    timeout = 5 * 1000 /*5s*/,
    timediff = new Date() - new Date(JSON.parse(cache.get(key)));
  if (timediff <= timeout /*5s*/) return;
  cache.put(key, JSON.stringify(new Date()));

  /* Following types of change are available:
   * EDIT
   * INSERT_ROW
   * INSERT_COLUMN
   * REMOVE_ROW
   * REMOVE_COLUMN
   * INSERT_GRID
   * REMOVE_GRID
   * FORMAT
   * OTHER - This usually refers to changes made by the script itself or sheets api
   */
  if (!/GRID|OTHER/.test(e.changeType)) return; //Listen only to grid/OTHER  change
  SpreadsheetApp.getActive()
    .createTextFinder(regexPattern)
    .matchFormulaText(true)
    .matchCase(false)
    .useRegularExpression(true)
    .replaceAllWith(replacementRegex);
}

To Read:

• Installable triggers
• TextFinder

Megass

OK. So to use this, I'd make an onChange() function like you have, substituting SHEETNAMES with the name of my custom function that needs to be re-run and replacing GRID with the change type I want the trigger to run on (ex EDIT). ... But is the .replaceAllWith() call correct? I think you'd need a back-reference to the group in original query like '=SHEETNAMES(\1' + (Math.floor.....). And do you need to register the onChange(e) function, or does google do that automatically due to the function name/signature? – Norsworthy

@Norsworthy You need to register the trigger manually. See the @see link in the code. The sheetnames custom function doesn't take any parameters that change the output(e is unused). So I didn't include a backreference. But if you have any non-optional parameters, you do need to change the regex as needed. – Megass

Answer 7

As noted earlier:

Custom Functions don't update unless it's arguments changes.

The possible solution is to create a checkbox in a single cell and use this cell as an argument for the custom function:

1. Create a checkbox: select free cell e.g. [A1], go to [Insert] > [Checkbox]
2. Make this cell an argument: =myFunction(A1)
3. Click checkbox to refresh the formula

Kazimir

Answer 8

I use a dummy variable in a function, this variable refers to a cell in the spreadsheet. Then I have a Myfunction() in script that writes a Math.Random number in that cell.

MyFunction is under a trigger service (Edit/Current Project Triggers) and you can choose different event-triggers, for example On-Open or time driven, there you can choose for example a time period, from 1 minute to a month.

Checkrow

Answer 9

Since google app script is an extension of JS, functions should be able to handle more args than defined in function signature or fewer. So if you have some function like

function ADD(a, b) {
  return CONSTANTS!$A$1 + a + b
}

then you'd call this func like

=ADD(A1, B1, $A$2)

where $A$2 is some checkbox (insert -> checkbox) that you can click to "refresh" after you needed to change the value from the sheet & cell CONSTANTS$A$1

Cuttler

Answer 10

Use a google finance function as a parameter. Like =GOOGLEFINANCE("CURRENCY:CADARS")

Those function force reload every x minutes

Monserratemonsieur

x = 20+ // minutes – Tufthunter

Answer 11

Working off of Lexi's script as-is, it didn't seem to work anymore with the current Sheets, but if I add the dummy variable into my function as a parameter (no need to actually use it inside the function), it will indeed force google sheets to refresh the page again.

So, declaration like: function myFunction(firstParam,dummy) and then calling it would be as has been suggested. That worked for me.

Also, if it is a nuisance for a random variable to appear on all of your sheets that you edit, an easy remedy to limit to one sheet is as follows:

function onEdit(e)
{
  e.source.getSheetByName('THESHEETNAME').getRange('J1').setValue(Math.random());
}

Gstring

@Rubén it is very similar but with a good nuisance; instead of using the active sheet it uses a defined sheet name, improving your history – Susurration

Answer 12

There are several good answers here that did approximately, but not exactly, what I wanted. I aggregated several of these answers. I also used some new features that probably weren't available when this question was first asked.

The problem I wanted to solve was to have custom functions update in close-to-real time without manual refreshing and to have this be transparent to the Spreadsheet user (but not the extension programmer).

1. I created a new sheet called update vars and placed a named range on here for each update granularity I wanted. For example, I created a named range update_minute for functions I wanted to automatically refresh every minute. I hid this sheet for cleanliness.

2. For the functions I wanted to refresh I created an additional argument forceUpdate that would take an auxiliary variable (as many other answers here mentioned). For example function VPRICEUPDATE(fund, forceUpdate) takes a Vanguard fund name to retrieve pricing data for and an auxiliary variable that only serves to invalidate the cache entry.

3. I created a named function in my spreadsheet called VPRICE that makes this auxiliary variable invisible to the user. It takes a single argument, fund, and aliases to VPRICEUPDATE(fund, update_minute). Note that I would use update_hour in my alias if I wanted hourly updating.

4. Finally, to make this all work, the named range update_XXX has to be updated at every XXX interval. I did this using a refresh function and the built-in triggers functionality. For example, for the minute granularity I define:

function refresh_minute(){
SpreadsheetApp.getActiveSpreadsheet().getRangeByName("update_minute").setValue(Date(Date.now()));
}

I then added a trigger to my project to call refresh_minute() every minute. For functions that only need to be updated daily, I repeated this with a different timer set on the trigger. It seems like you can also get daily update granularity using TODAY() as your auxiliary variable, but anything sub-daily is hard since Google makes it an error to reference NOW() in a formula.

Baobaobab

Since you mentioned "the extension programmer", note that if you publish the code as an add-on, it won't be able to run triggers every minute. The highest frequency is "once per hour at most". – Biliary

Answer 13

Highest performance

If you're fine with your script having permission to "See, edit, create, and delete all your Google Sheets spreadsheets", there's a much simpler and faster way to update a set of cells, than any other answers have mentioned so far.

The Advanced Sheets Service lets you write to multiple disjointed ranges at once, using the batchUpdate method and the operation is very fast (compared to .setValues / .setFormulas):

function recalculateCells(recalcData) {
  const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId();
  // Effectively, quote the values/formulas - https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
  Sheets.Spreadsheets.Values.batchUpdate({
    data: recalcData,
    valueInputOption: 'RAW',
  }, spreadsheetId);

  // Restore them as entered by the user - https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
  Sheets.Spreadsheets.Values.batchUpdate({
    data: recalcData,
    valueInputOption: 'USER_ENTERED',
  }, spreadsheetId);
}

recalculationData is an array of objects, where each object has two fields of interest:

• range - the cell to update, in A1 notation, e.g. 'My Custom Sheet'!B2:B2
• values - the value to write, which should be the formula in the cell that you want updated (expressed as a bidimensional array with one row and one column, e.g. [[cellFormula]]).

You may notice that the spreadsheetId is superflous if the script is run for the current spreadsheet. However, batchUpdate required the spreadsheet ID because it can be run on any spreadsheet - that's why your script will need permission to "See, edit, create, and delete all your Google Sheets spreadsheets". Please vote for this issue to enable the Advanced Sheets Service to be scoped to only the current spreadsheet.

Slightly lower performance

If you can't obtain the permission to "See, edit, create, and delete all your Google Sheets spreadsheets", you can use a RangeList to store the cells you want to refresh, then .setValues(), flush(), and finally .setFormulas()andflush()` again to force their recalculation.

const activeSS = SpreadsheetApp.getActiveSpreadsheet();
const rangesInSheet = [];

for (const range of sheet.getActiveRangeList().getRanges()) {
  const formulas = range.getFormulas();
  // Prepare a set of values to store in the cell briefly until
  // storing the formulas again. This serves two purposes:
  // 1. Provide visual feedback to the user
  // 2. More importantly, if the script gets terminated (e.g. due to
  //    Apps Scripts limits), the original formula is preserved as a value
  const recalcValues = [];
  for (let row = 0; row < formulas.length; row++) {
    recalcValues[row] = [];
    for (let col = 0; col < formulas[row].length; col++)
      recalcValues[row][col] = `[⚠ RECALCULATING ⚠] ` + formulas[row][col];
  }
  rangesInSheet.push({
    range,
    formulas,
    recalcValues
  });
}

// Flush all recalculation values first, THEN all formulas. This reduces the number of flush() calls from N*2 to 2.
for (const r of rangesInSheet) {
  // Storing noop formulas like =NOOP(...) or IFERROR(1/0, ...) didn't work because Sheets apparently caches the
  // result of the original formula and returns it right away to the noop. The only way to trigger calling the
  // custom functions again was to set the *values* of the cells, then setFormulas() again.
  console.log(`Storing raw values for ${r.range.getA1Notation()}...`);  // won't display the sheet name
  r.range.setValues(r.recalcValues);
}
SpreadsheetApp.flush();  // force changes to be written

for (const r of rangesInSheet) {
  console.log(`Restoring original formulas for ${r.range.getA1Notation()}...`);  // won't display the sheet name
  r.range.setFormulas(r.formulas);
  console.log('Formulas recalculated.');
}
// Force restoring original formulas; both flush()es are necessary
SpreadsheetApp.flush();

I've benchmarked this approach to 50+ cells per second, but haven't tried larger numbers of cell. For reasonable numbers of cells, it may be practically as fast as the batchUpdate() approach.

Biliary

Answer 14

another solution to the caching problem.

have a dummy variable in your method. pass

Filter(<the cell or cell range>,1=1)

as the value to that parameter.

e.g.

=getValueScript("B1","B4:Z10", filter(B4:Z10,1=1))

the output of filter is not used. however it indicates to the spreadsheet that this formula is sensitive to B4:Z10 range.

Aalii

it doesn't work anymore. You'll see the error "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()" if you try to do it in this way – Tumbledown

You can invalidate the cache by storing a value in the cell, then storing the formula again. – Biliary

Answer 15

I had a similar issue creating a dashboard for work. Chamil's solution above (namely using Sheet's Filter function passed as the value to a dummy variable in your function) works just fine, despite the more recent comment from Arsen. In my case, I was using a function to monitor a range and could not use the filter on the same range since it created a circular reference. So I just had a cell (in my case E45 in the code below) in which I changed the number anytime I wanted my function to update:

=myFunction("E3:E43","D44",filter(E45,1=1))

As Chamil indicated, the filter is not used in the script:

function myFunction(range, colorRef, dummy) {
  variable 'dummy' not used in code here
}

Fallacy

Answer 16

Given that feature explained by Henrique Abreu, you may try the out-of-box spreadsheet function QUERY , that SQL liked query is what I use often in work on raw data, and get data as summary to a different tab, result data is updated in real time following change in raw data.

My suggestion is based on the fact that your script has not advanced work such as URL fetch, just data work, as without actual data read, I cannot give a precise solution with QUERY.

Regarding the cache feature mentioned by Henrique Abreu (I don't have enough reputation to comment directly under his answer), I did testing and found that:

1. looks there is no cache working, testing function's script shown below:

   function adder(base) { Utilities.sleep(5000); return base + 10; }

applying that custom function adder() in sheet by calling a cell, and then changed that cell value forth and back, each time I see the loading message and total time more than 5 seconds. It might be related to the update mentioned in this GAS issue:

This issue has now been fixed. Custom functions in New Sheets are now context aware and do not cache values as aggressively.

2. the issue mentioned in this topic remains, my testing suggests that, Google sheet recalculate custom function each time ONLY WHEN

   • value DIRECTLY called by function is changed.

   function getCellValue(sheetName,row,col) { var ss= SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheetByName(sheetName); return sh.getRange(row, col).getValue(); }

   
   A change of any value in yellow cells will lead to recalculation of custom function; the real data source value change is ignored by function.

   • function containing cell's location is changed in sheet. ex. insert/remove a row/column above or left side.

Benkley

Answer 17

What you could do is to set up another cell somewhere in the spreadsheet that will be updated every time a new sheet is added. Make sure it doesn't update for every change but only when you want to do the calculation (in your case when you add a sheet). You then pass the reference to this cell to your custom function. As mentioned the custom function can ignore this parameter.

Cerebration

Answer 18

I did not want to have a dummy parameter. YMMV on this.

1 A cell that is a 'List of Items', one is "Refresh"

2 Script with 'onEdit', if the cell is "Refresh":

a)Empty out the document cache

b)Fill doc cache with external data (a table in my case)

c)For all cells with my 'getStockoData(...' custom function

• get the formula

• set '=0'

• set the fromula

d)Set the cell in (1) with a value of "Ready"

This does refresh the bits you want BUT IS NOT FAST.

Saiff

How exactly do you "empty out the document cache"? – Biliary

Answer 19

I followed this video, from 1:44, and this worked for me.

You should use a specific update function, initialize a "current time" variable and pass this permanently updated variable to your custom function. Then go to Triggers and set up an "every-minute" time-driven trigger for the update function (or choose another time interval for updates).

The code:

function update() {
  var dt = new Date();
  var ts = dt.toLocaleTimeString();
  var cellVal = '=CustomFunction("'+ ts + '")';
  SpreadsheetApp.getActiveSheet().getRange('A1').setValue(cellVal);
}

Timmytimocracy

Answer 20

Just add GOOGLEFINANCE("eurusd") as an additional argument to your custom function, like:

=myFunction(arg1, arg2, GOOGLEFINANCE("eurusd"))

Screwball

Already mentioned here: stackoverflow.com/a/58043635 – Megass

Answer 21

As @Brionius said put an extra dinamic argument on the function. if you use now() you may have timeout problems make the update a little bit slower...

cell A1 = int(now()*1000)
cell A2 = function(args..., A1)

Reverberatory

it doesn't work anymore. You'll see the error "This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()" if you try to do it in this way – Tumbledown

Answer 22

If you have written a custom function and used it in your spreadsheet as a formula, then each time you open the spreadsheet or any referencing cell is modified, the formula is recalculated.

If you want to just keep staring at the spreadsheet and want its values to change, then consider adding a timed trigger that will update the cells. Read more about triggers here

Mele

That would be just great, except it doesn't work... Reloading the page doesn't refresh the values. Moreover, deleting the cell and re-entering the same function call, still keeps the old value. If I call the same function exactly from another cell, it will show the new value, but not in the old cell. – Surfbird