Using IMPORTRANGE to import from Excel file in Google Drive

1
votes

To import an entire sheet of data from another spreadsheet using IMPORTRANGE I'd do something like this:

=importrange("google-drive-id-for-spreadsheet","A:AR")

This works fine for a Google Sheets spreadsheet source, but if the source file is an Excel spreadsheet, I get a #Ref! error in the cell and the hover comment is:

Error Spreadsheet cannot be found.

I'm presuming this is because IMPORTRANGE doesn't work with Excel files, so how can I achieve the same thing?

I don't mind working with scripts but would prefer a formula solution if possible.

Edit: This happens whether I use the full URL or just the spreadsheet key and if I use the sheet name with the range or not. I've tried several files and it always works with the Google Sheets files and never works with Excel files.

Something occurred to me about the ownership and location of these files. Somebody else is the owner of the spreadsheet that I want the IMPORTRANGE formula in. I have full edit permissions. The folder that the spreadsheet resides in is owned by the same guy, it has been shared with me and I have added it to my Drive. In a subfolder of this folder is where the source files are. I am the owner of the subfolder and the source files within, both Excel and Google Sheets files.

Could this setup have anything to do with the results I'm getting?

Edit: I've had the ownership of the folders (all the way up the hierarchy) and relevant files transferred to me and it's still doing the same thing.

1
google-sheetsgoogle-sheets-formulaimport-from-excelimportrange
Yes the file itself is fine. I can open it wherever I need. It's just that importrange returns cannot be found. I had to move on from here and the only way I could get this to work is to code a convert to .gsheet format first. Clearly only a workaround and not a solution but might point someone in a direction that gets their project moving until this can be answered. – Baddie

1 Answers

1
votes

This is clearly only a work around and not an answer, but I had to do something so that I could move on. The only way I could get what I wanted is to code a convert to .gsheet format first and point imortrange to that new sheet. Might help someone else get their project pointing in a working direction until this can be answered.