Force the refresh of a Google Sheet

Question

I know that there have been several attempts to answer this type of question that I've found through google searches, but none have worked for me, so I thought I'd ask again.

I have a Google Sheet with multiple tabs. There is a summary type tab followed by a series of tabs that use the =importData() function to retrieve data from an external site. These function calls all work as expected, loading the correct data into each tab.

My issue is that I've tried all the suggestions I've found online to refresh the summary sheet but none of them seem to force the recalculation.

My app script does the following using an OnOpen trigger (or a menu item I've added)

1. Iterate through the data tabs, fetching the =importData function from the cell (A1 in my case on each sheet), then setting the function which causes each sheet to refresh/fetch the data I need.
2. Once that process is complete, I select the summary tab and attempt to refresh the calculations that pull from the updated data sheets.... this never works for me.

What have I tried,

1. Fetiching the formula from the range of cells that need to be refreshed and setting them again in the range
2. Setting a =now() formula in one of the cells and doing the same to try to force a recalculation
3. Inserting a column in the sheet and then removing the column

None of these give me the desired result. What does work is to manually

1. Select a cell that I know has a changed state, delete then undo that cell refreshes that cell with the correct value, or
2. Select the entire sheet, delete then undo refreshes the sheet as expected.

Any suggestions on what to try next? I'm moving this sheet from Excel to Sheets... using the Excel equivalent of importData and that works correctly every time so hoping to find a solution.

Answer 1

I did, finally, find a solution that seems to work. I ended up creating a couple of named ranges (just to keep the cells I needed to update flexible and not hard coded). Then used these ranges and used

  var cellarRange = ss.getRangeByName(rangeName);
  var theFormulas = cellarRange.getFormulas();
  cellarRange.clearContent();
  cellarRange.setFormulas(theFormulas);

This finally worked for me ... reliably. I had thought I had done something equivalent before but given my current results, I guess I had not.

Answer 2

You can get importdata() to refresh by modifying its parameter, i.e., the URL endpoint. This also lets you duck Google's cache and fetch new results.

With many endpoints, you can append a no-operation URL parameter like this:

=importdata("https://www.example.com/page?refresh=" & C1

In the above, the ?refresh=" & C1 bit is superfluous and will be ignored by most endpoints. To get the formula to refresh, increment the value in cell C1. That can be done with a simple Apps Script function that runs on demand or on a time-driven trigger.

Here is an example of a script that increments a cell value:

/**
* Increments a number in a spreadsheet cell.
*
* Run this function through the onOpen(e) function, __or__ on a time-driven trigger.
* To run the function on a time-driven trigger, choose Extensions > Apps Script, click
* the clock icon in the left and click Add trigger > incrementCounter_ > Time-driven
* > Day timer > 11pm to midnight > Save.
* When prompted, click Review Permissions > choose account > Advanced > 
* Go to incrementCounter_ > Allow (you only need to do this once)
*/
function incrementCounter_() {
  // version 1.1, written by --Hyde, 5 March 2021

  ////////////////////////////////
  // [START modifiable parameters]
  const range = SpreadsheetApp.getActive().getRange('Sheet1!C1');
  const incrementBy = 1;
  // [END modifiable parameters]
  ////////////////////////////////

  range.setValue((Number(range.getValue()) || 0) + incrementBy);
}

Answer 3

For more reliable scripts, limit the simple onOpen trigger to add the custom menu. If you need to do some something else when the spreadsheet is opened, use a installable trigger instead. This because simple triggers might run with limited authorization, might have problems to identify correctly the active user, have a execution limit of 30 seconds, etc.

Regarding using a script to refresh a function like IMPORTDATA, as you are doing manually, clear the cell having the formula, use SpreadsheetApp.flush() to apply this action, then add the formula again.

Another option is to do the import task by the script instead of doing it by using a built-in function. If the import task is quick it might be done using a custom function.

Related

• How to Refresh a cell in Google Spreadsheet?
• Does IMPORTDATA() refresh when an Apps Script runs even if the sheet isn't open?

Resources

• https://developers.google.com/apps-script/guides/triggers?hl=en
• https://developers.google.com/apps-script/guides/sheets/functions