Google Sheets IMPORTRANGE – How to Import a Range from One Spreadsheet to Another

google sheetsimportrange

I am getting the same error as described here except not for the same reason. I am using the new Google Sheets, so I am supposed to be able to paste the entire source URL into the sheet, like so:

=ImportRange("https://docs.google.com/spreadsheets/d/1zRHatplJ6AVUSWr1HXoycUrvtK05dw4-ZqeEYN1xJ4Y/edit?usp=sharing", "B:B")

However, that results in the error:

The requested spreadsheet key, sheet title, or cell range was not found.

I am the author of both sheets, so I see no reason for the error to occur. Also, since I'm trying to get the data from the second column of the first sheet in the source, I shouldn't need to include the sheet name. Nonetheless, I have tried it both with and without the sheet name, and got the same results.

Anyone have any idea what I'm doing wrong?

Edit: Here is the source sheet, and here is the destination sheet (in particular, the "Data" sheet).

Best Answer

Only after having requested that the documents were to be shared with us, was I able to determine what's going on.

The response sheet is new Google Sheet proof:
enter image description here

The sheet, in which you want to pull the data in, doesn't have this mark.

You are trying to import the data via the complete URL (new Sheets style) from within an old Google Spreadsheet. This is where Google says: No Can Do !!

If you use the key instead, it works just fine:

 =IMPORTRANGE("1zRHatplJ6AVUSWr1HXoycUrvtK05dw4-ZqeEYN1xJ4Y", "B:B")

See references for more information on this topic.

References

Related Solutions

Google Sheets – How to Auto-Update a Reference to Another Sheet

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.

Google-sheets – IMPORTRANGE #REF! Error

I'm not sure, but it could be some sort of glich.

This post may help you.

I had similiar problems. I tried doing this:

refreshing page
changing range in the formula to force it recalculate: "Sheet1!A:C" → "Sheet1!A1:C6000"
Also found smart way to make it with iferror: iferror(IMPORTRANGE("SHEET_KEY","Sheet1!A:C"))

If this won't help, please give example file.