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.

  • I'm not aware of the details of how it works, but I'm using the following: ifttt.com/google_drive
    – tlewis3348
    Commented May 6, 2014 at 11:42
  • @pnuts Can you explain what you mean by "ensuring that the right version is used as the source"? I have the settings for both sheets set to update every minute and the tabs are set to refresh every minute. How, where, and in which sheet do I update the source?
    – tlewis3348
    Commented May 6, 2014 at 13:55
  • @pnuts Well, the data is showing up in the separate workbook okay. From all appearances, it looks like it was just typed in.
    – tlewis3348
    Commented May 6, 2014 at 14:29
  • Yes, this is the new Google Sheets. One workbook shows the data added, and the other workbook does not. I will add this to the original question for clarification.
    – tlewis3348
    Commented May 6, 2014 at 14:36

13 Answers 13


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:


And now it looks like:



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

  • 6
    I came across this answer when trying to do the same thing. It looks like Google have made it so that now(), rand() and randbetween() cannot be used inside an importrange() call.
    – Hugh
    Commented Jun 22, 2017 at 16:05
  • If I set the spreadsheet to update every minute, will the formula also get refreshed and fetch new data(if available)? Commented Jan 11, 2019 at 9:54

The following comes from Jimmy in this Web Apps answer.

  1. In both spreadsheets insert an =now() equation in a random cell, say Z1
  2. In both spreadsheets insert an =importrange() function that references the now function of the other spreadsheet.
  3. Go into your spreadsheet settings and choose to recalculate on every minute.

I tried a lot of other suggestions including using the =now() function, the now URL trick in this thread, or Apps Script to insert random text on a set interval, but nothing would force importrange to update except a manual edit of the source sheet.

  • 1
    Worked for me in December 2018 Commented Dec 4, 2018 at 3:07
  • 1
    Worked for me in January 2020
    – user1114
    Commented Jan 14, 2020 at 5:18
  • I think this answer gives the most stable solution. It should be enough to add =now() into the source file and set the calculation each minute. Commented Nov 3, 2021 at 7:08

I found the easiest way was to put a simple if statement around the importrange.

B1 = Sheet ID
B2 = Sheet Name and Range
B3 = Now()
=if (B3> now()-1, IMPORTRANGE(B1,B2),)

This works every time.

  • I've been trying to implement your solution but it's unclear to me how to use B3. You are you saying B3 = Now() then =if (Now() > now()-1, IMPORTRANGE(B1,B2),) Commented Aug 19, 2017 at 7:47

The datestamp workaround doesn't seem to work for me - there's an explicit note that using datestamps and rand functions aren't allowed.

My very hacky workaround is to delete the cell and then hit ctrl+z. It forces a refresh every time. It's trivial to script this onopen, or at specific time intervals to keep the data fresh.


As a workaround for the now() roadblock, you can change the settings of the spreadsheet to update every hour.

File -> Spreadsheet Settings -> Calculation

Update the sheet to recalculate itself on "On Change and every hour" or "On change and every minute". Though keep in mind, Selecting recalculation every minute may hang up the spreadsheet.

  • This is the most correct answer. I set up a repeater on a website to scrape current crypto values dynamically. The spreadsheet uses IMPORTHTML( url, "table", 1 ) The crypto I'm wanting is in the url and my script on the host is looking for specific terms to search.
    – Krista K
    Commented Feb 14, 2021 at 3:03

I found a simple hack to simulate a manual cell update and launch modifications in an external spreadsheet.

  1. Created a custom function like this:

    // Hack function used just to simulate a manual update
    function hack(value) {
      var list = []
      return list;
  2. Then, on my sheet I call it in this way:


    where tab!B1 is one of the cells I modify by code.

  • How does this simulate a manual cell update?
    – haemse
    Commented Aug 6, 2017 at 2:19

OR... you could do a simple script, first giving the value cero (0) then including the formula back and this do the trick:

  var cell = sheet.getRange("B1:B1"); //SHEET TO INSERT VALUE
   Utilities.sleep(2000); //JUST TO GIVE TIME TO UPDATE (OPTIONAL)
  var cell = sheet.getRange("B1:B1");
  • even better would cell.setFormula("=" & cell.getValue());, so the user does not see zero for 2 seconds.... Commented May 15, 2018 at 0:39

I was able to find a way to solve my problem (detailed here) using an Apps Script with a custom function.

If you replace =IMPORTRANGE(spreadsheet_url, range_string) in your spreadsheet with =DynamicImportRange(spreadsheet_url, sheet_name, range) and paste the code below into Tools --> Script Editor --> Blank Project, it should work (see this for more information on writing apps scripts).

Usage: =DynamicImportRange(spreadsheet_url, sheet_name, range)
Compare to: =IMPORTRANGE(spreadsheet_url, range_string)

Where the given arguments are placeholders that should be replaced
by arguments specific to your use-case.
function DynamicImportRange(sheet_url, sheet_name, sheet_range) {
  var values = SpreadsheetApp.openByUrl(sheet_url).getSheetByName(sheet_name).getRange(sheet_range).getValues();
  return values

function RefreshSheet() {
  // Update the following two variables to suit your particular situation
  var sheet_name = "sample_sheet_sheet"
  var range = "A1"

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_name);
  var formula1 = "Loading...";
  var formula2 = sheet.getRange(range).getFormula().toString().replace('"', '\"');
  // The following assumes that the data is being inserted into the sheet in the same
  // location as it is in in the source sheet.
  var form_range = formula2.split(",")[2].split("\"")[1];

If you want the sheet to be updated on a regular basis, you can set up a trigger by going to Resources --> Current project's triggers in the Apps Script window.

  • 4
    FOR ALL GOOGLERS END UP HERE: spreadsheet custom functions cannot open (anymore) other spreadsheets using SpreadsheetApp.openById() or SpreadsheetApp.openByUrl(). Check here and here Commented Mar 30, 2016 at 21:06

Things may have changed since 2014, but I have just created

  1. a "main" workbook whose data is populated by an external platform via sheets API
  2. a "secondary" workbook whose data is populated using IMPORTRANGE on the main workbook.

Which I believe is the same setup as the OP had implemented.

The difference here is whenever data is added to the main workbook (both through manual input AND through the API by the 3rd party platform), the secondary workbook (and any other workbooks that import the main data) automatically updates within a few seconds.


Just put =GOOGLEFINANCE("BTCUSD") anywhere in your original spreadsheet.

The Bitcoin market runs 27/4.

The price changes every 4 or 6 minutes, then automatically refresh the =IMPORTRANGE() spreadsheet.


You can use the Google Sheets add-on Sheetgo to automatically update your reference from another sheet. You can use 30 updates for free per month or obtain a paid subscription for more updates.This video should explain you the basic usage.

  • 2
    Please keep in mind that although it is cleverly stated on the site "Install for Free", it is an add-on with one limited free plan followed by 3 paid ones. The link to the Pricing page is only to be found at the very bottom of the site. Commented Jul 27, 2016 at 16:20

You can record a macro which deletes all the values in the sheet then pastes the IMPORTRANGE formula back into the cell you want.

That way you have a macro set to a shortcut than refreshes it.

Not ideal but works

Looking into how to make the macro run every minute or with a button. Not sure how to do either.


Now it has been super easy to use this.

You need to use this function like format below.

=IMPORTRANGE("spreadsheet_url", "Name of Sheet you want to copy!Range" )

Example Below

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/", "Form Responses 1!A1:B1000")

If it asks for permission, just allow it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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