formulasgoogle sheetsimportrange
I need to import a range of data from one Google spreadsheet to another but I keep getting the error:
"Cannot find range or sheet for imported range using this formula"
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit#gid=190222386","Retail Sales!G9:G39")
You are grabbing too much of the spreadsheet key...Try this piece:
0AupKcedJ0nhPdFFyMVhySWNvNzlYTV9RZ1FmYXdKc0E
So your line should read:
=importrange("0AupKcedJ0nhPdFFyMVhySWNvNzlYTV9RZ1FmYXdKc0E","sheet2!E9:E9")
This answer on Web Applications, explains the IMPORTRANGE in detail.
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:
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.
Best Answer
The issue to your question has nothing to do with the
IMPORTRANGEfunction.It is about your reference to the sheet.
You need to enclose the name of the sheet in single quotes, because the name of the sheet itself has a blank space between the words.
As from the official help site on "Get data from other sheets in your spreadsheet":
In your case use
'Retail Sales'!G9:G39and surround this with double quotes.You can also read more about
IMPORTRANGE