Monday, October 1, 2012

Google Spreadsheet ImportRange not working for me ... :-(

So I was hoping to use the Google Spreadsheet "ImportRange" function to cross-link some spreadsheets in the hopes of automating assignment status updates for my students, but no such luck:

http://support.google.com/docs/bin/answer.py?hl=en&answer=155183

I've tried to replicate in spreadsheets created from scratch.  Here's the source spreadsheet:

https://docs.google.com/spreadsheet/ccc?key=0Aq72y9iq5_1UdGFBRmc1VDYyS1p2UHZtLTd3U3REcHc#gid=0

and here's where I'm trying to pull in the data to:

https://docs.google.com/spreadsheet/ccc?key=0Aq72y9iq5_1UdHVnNzQ4SHBIRU9BS3ZhM2YyZFBqWWc#gid=0

and I have cell A1 set to:
ImportRange("0Aq72y9iq5_1UdGFBRmc1VDYyS1p2UHZtLTd3U3REcHc","Sheet1:A1")
but I get this error:
"#REF! error: The requested spreadsheet key, sheet title, or cell range was not found."
which is the error described here in the support document mentioned above,  which is supposed to be the error I get if I am not added as a collaborator on the source document, but since I am the author of both spreadsheets that doesn't make much sense.  I have tried playing with the openness settings of both spreadsheets, setting them to view with link, and completely public, but this has no impact on the error I get above

In my actual work case I get a completely different error trying to do approximately the same thing:


and of course I've searched Google for answers, and found various posts that describe similar errors, but haven't helped me fix my issue:

https://productforums.google.com/forum/#!topic/docs/HiqmmeeV0WI (incorrect sheet ids/syntax)

http://productforums.google.com/forum/#!topic/docs/6lczh2QEEXQ (wrap in expand statement)

Google spreadsheets is really great, and I can get loads done in the bits that I can get to work, but I really hate getting stuck like this and burning an hour on something where I can't get access to a more detailed error message, and the user community problem and solution dialogues are not organized around the documentation like they are in PHP and MySQL.

What I really really really want is some google spreadsheet people on IRC to actually talk through the problem ...

3 comments:

Sam Joseph said...

I've posted this issue into the google drive community

Sam Joseph said...

Try ImportRange("0Aq72y9iq5_1UdGFBRmc1VDYyS1p2UHZtLTd3U3REcHc","Sheet1!A1")

You forgot the exclamation mark. :)

Sam Joseph said...

I'm having the same issue as Sam and I checked that my spreadsheet_key and range_string syntax is correct. Could it be because "sheet1" in my source spreadsheet has actually been named "IT Project List"? If so, what is the proper syntax when a sheet has a name instead of just "sheet 1." sheet 2," etc....