IMPORTRANGE Errors? Here are ways to solve them
IMPORTRANGE is an effective data consolidation tool of Google Sheets. Learn how to harness this element and use it to better your work in Google Sheets.
The IMPORTRANGE function is a powerful way of consolidating data from different Google Sheets spreadsheets into a single spreadsheet. You can learn about it again here: How to Import Multiple Sheets Using the IMPORTRANGE Function
If you are having problems with IMPORTRANGE, here is your guide to solving them!
Common Solutions to Fix IMPORTRANGE errors:
- Click the refresh button on your browser to refresh the google sheet
- Make sure you are not calling the same data multiple times. Add it to a dedicated tab if necessary.
- Make sure you aren't importing a large number of empty rows.
- Try changing the case for example IFERROR(IMPORTRANGE("id", "A:A"), IMPORTRANGE("id", "A:a"))
- Check for dynamic functions like GOOGLESHEETS, DAY and TIME that update on every edit. Use static values instead.
Formula Parse Error
This simply means that you have a syntax error or two in the formula. Remember that the formula is formatted this way:
=importrange("url_of_workbook","data_range")
You should check the formula a few times to make sure it is correctly formatted. You should also consult our guide if you actually tried combining two or more IMPORTRANGE formulas into one.
Spreadsheet cannot be found
If this error appears, it is because you had a small mistake in copying the link. This simply means that you have a syntax error or two in the formula. Simply copy the link again, making sure you copy the entire link. If you got the link from another source and you correctly copied the link, contact your source to confirm if it is the right link.
You don’t have permissions to access that sheet
It means that the privacy setting of the source sheet is not set to public, and that your Google account was not added to the list of users that can access it. Contact the owner of the source sheet to have your Google account added.
In case you’re sure you already have access to the sheet and are using more than one Google account in the same computer (which I do), check if you are accessing the sheet using the right Google account:
- Go to sheets.google.com, then click on the account icon on the upper-right corner of the screen. The list of Google accounts logged in your browser will appear.
- You may discover that you are using a different Google account; click the Google account that you are supposed to use.
You can now create the spreadsheet again and add the same formula.
You need to connect these sheets
Don’t worry; this is just one step in using the IMPORTRANGE function. This means that you have access to the source sheet. Simply click the Allow access button. The data will now load to your own sheet.
Cannot find range or sheet for imported range
In this case, there is an error in typing the range to be imported from the source sheet. Simply check if you have added it correctly in the space for the range.
Result was not automatically expanded
This means that the range is too big for the current space allotted for the sheet.
The first question you need to ask is: do you need all the rows and/or columns from the source sheet? Consider the fact that your computer may have a hard time processing a huge amount of data. If you don’t need all the data,, you should check the source sheet first for the rows and/or columns to include.
If you actually plan to summarize them, consider the following workarounds:
- Create a Pivot Table, then use IMPORTRANGE to import the summary. This requires you to have Editor rights, as you will create the Pivot Table in the same spreadsheet. We have tutorials for it here:
Spreadsheets How to Format Pivot Tables in Google Sheets
How to Add Custom Formulas in a Google Sheets Pivot Table
How to Sort the Rows in the Pivot Table in Google Sheets
How to Consolidate Several Sheets into a Single Pivot Table
- Use the QUERY function to filter the data. We have tutorials that you can follow:
How to Use Basic SQL Commands + Query Another Sheet in Google Sheets
How to Use SQL Queries to Search by Date on Google Sheets
How to Select Multiple Columns through SQL Query in Google Sheets
How to Use SQL Queries to Search for Specific Words in Google Sheets
How to Use SQL Labels in Google Sheets
How to Use SQL Queries to Format Google Sheets
Data & Analytics How to Use the SQL Pivot Clause in Google Sheets
How to Use SQL Query with Multiple Criteria in Google Sheets
If the error states that you have to insert more rows and/or columns and you really need all of them, then check out our tutorial on doing so: How to Add/Remove Rows and Columns in Google Sheets
Sometimes, IMPORTRANGE doesn’t work
It has been reported that sometimes, IMPORTRANGE simply doesn’t work. Unfortunately that problem is something we can’t solve. You can refresh the Google Sheets, load the spreadsheet again, or check it again later or tomorrow.
However, I know a better way of handling huge amounts of data. It’s called Lido app. You see, you don’t need to use IMPORTRANGE or even Pivot Table and QUERY functions. Lido app will handle the torrential amounts of data from the marketing and e-Commerce services you use, process them, and give you the charts and metrics that you are looking for in the first place. Interested? sign up for free.
