0

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.

3 Answers 3

0

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

Resources

7
  • Thanks for the suggestion. I am using an installable trigger not the simple onOpen event. I've updated my script to suround the importData with flush() as suggested. I'll need to wait for a host update to test but will report back. A1value = sheet.getRange('A1').getFormula(); // get the formula of A1 SpreadsheetApp.flush(); sheet.getRange("A1").setFormula(A1value); // set the formula of A1 to A1 SpreadsheetApp.flush();
    – Neil Lamka
    May 11 at 16:59
  • @NeilLamka You are welcome. Please keep us posted
    – Rubén
    May 11 at 17:04
  • Hi again Rubén, I'm not sure if my problem is solved yet so I'll keep you posted. I created a custom function to fetch the data I needed. I'm using the UrlFetchApp.fetch() then getting the response. The response using importdata and urlfetchapp return a body that is CSV that I parse with a standard Utilities.parseCsv function. What I noticed is that importData properly detects and sets the format for number and date fields in the CSV it retrieves but my custom function using url and utility and returning the resulting parsed array doesn't do that. Is there a method I could use instead?
    – Neil Lamka
    May 11 at 19:47
  • @NeilLamka I suggest you to post a new question.
    – Rubén
    May 11 at 20:44
  • OK, thanks, I'll do that
    – Neil Lamka
    May 12 at 15:44
0

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

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.