Google-sheets – How to get Google Sheets to auto-update a reference to another sheet

google sheetsimportrange

I am using an external application to enter data into a Google Spreadsheet. This workbook is then referenced by a separate workbook (with the IMPORTRANGE(spreadsheet_key, range_string) function) that manipulates and interprets the data. My problem is that whenever I personally modify the referenced workbook, the referencing workbook updates just fine, but whenever the external application modifies it, it does not get updated.

I have tried editing the "Spreadsheet settings" so that recalculation is done every minute and on every change. Furthermore, I have also installed an extension in Google Chrome that auto-refreshes the page every hour. However, the data does not get re-imported from the referenced workbook. Even if I copy the formula into a new cell, the data is still not re-imported.

Is there anything that I can do to the referencing workbook to trigger Google Sheets to re-import the data?

Edit: Just to be clear, I currently have one workbook with data entered by an external application in it (call it the "source sheet"), and another workbook with an IMPORTRANGE function in it (call it the "referencing sheet"). The data shown by the IMPORTRANGE function in the "referencing sheet" does not include any data entered by the external application since I last personally edited the "source sheet". Also, both workbooks are using the new Google Sheets.

Edit: Also, this question is not the same as How do I link a cell in Google Spreadsheets to a cell in another document? because I am using the function given as the solution to that question to import data from a spreadsheet. The problem is not how to import the data, but rather how to update the source for the data. My assumption would be that Google would have taken care of this for me, but the data in the "referencing sheet" does not get updated, and the only way I can find to get it to update is to physically go into the "source sheet" and edit it myself.

Best Answer

I've been struggling with the same problem. Instead of writing a custom function, I add a different query string to spreadsheet_url in IMPORTRANGE in the hope that each time the page is refreshed, Google thinks it needs to fetch data from a new spreadsheet. I simply append a new timestamp to make the url unique each time. It's a blatant hack, but it has been working for me across a lot of sheets.

My formula previously looked something like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174","'Sheet1'!A1:B25")

And now it looks like:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/123123123123/edit#gid=1816927174"&"?"&now(),"'Sheet1'!A1:B25")

Update:

This method no longer works since Google no longer permits now() inside importrange(). See comment from Hugh below.

Related Topic