CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaimport-from-excel
Baddie
Baddie

Reputation: 345

Using IMPORTRANGE to import from Excel file in Google Drive

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.

Upvotes: 6

Views: 12490

Answers (2)

Thang Dang
Thang Dang

Reputation: 493

There are there way to fix this problem:

  1. Convert the Excel spreadsheet to a Google spreadsheet. You can then use the importrange() function to import the data from the converted file.
  2. Export the Excel spreadsheet to a CSV file first, then you can use a different function called the importdata() function to import that data into a Google spreadsheet.
  3. Using Add-on: "Sheetgo"

You should watch this video:

How to automatically import Excel Data to Google Sheets?

Upvotes: 0

Baddie
Baddie

Reputation: 345

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.

Upvotes: 1

Related Questions