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)?

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: https://code.google.com/p/google-apps-script-issues/issues/detail?id=888

and here: Script to summarise data not updating

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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 script(to get list of sheets):

/**
 * @customfunction
 * @returns Current list of sheet names
 */
function sheetNames(e) {
  return SpreadsheetApp.getActive()
    .getSheets()
    .map(function(sheet) {
      return sheet.getName();
    });
}

/**
 * @listens to changes in a Google sheet
 * @see https://developers.google.com/apps-script/guides/triggers/installable#managing_triggers_manually
 */
function onChange(e) {
  /* 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/.test(e.changeType)) return; //Listen only to grid change
  SpreadsheetApp.getActive()
    .createTextFinder('=SHEETNAMES\\([^)]*\\)')
    .matchFormulaText(true)
    .matchCase(false)
    .useRegularExpression(true)
    .replaceAllWith(
      '=SHEETNAMES(' + (Math.floor(Math.random() * 500) + 1) + ')'
    );
}

To Read:

• Installable triggers
• TextFinder

Answer 6

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());
}

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

Answer 8

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);
}

Answer 9

Today I solved this by

1. adding another parameter to my function:

function MY_FUNC(a, b, additional_param) { /* ... */ }

2. adding a value to this parameter as well, referencing a cell:

=MY_FUNC("a", "b", A1)

3. and putting a checkbox in that referenced cell (A1).

Now, it takes only one click (on the checkbox) to force recalculating my function.

Answer 10

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