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)
- Iterate through the data tabs, fetching the
=importDatafunction 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.
- 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,
- Fetiching the formula from the range of cells that need to be refreshed and setting them again in the range
- Setting a =now() formula in one of the cells and doing the same to try to force a recalculation
- 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
- Select a cell that I know has a changed state, delete then undo that cell refreshes that cell with the correct value, or
- 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.