Back to Blog

Why IMPORTRANGE Is Not Working: Errors and Fixes

Let’s say you opened your spreadsheet and found out that all your IMPORTRANGE formulas were not working? The previously imported data disappeared and refreshing won’t help get it back. Many Google Sheets users have already suffered from this known IMPORTRANGE drawback. To avoid it, you’d better use an alternative solution, which turns your spreadsheet into a sort of relational database. We’ll show you how to do this a bit later. But for now, let’s troubleshoot your IMPORTRANGE formula and fix the current error.

Common IMPORTRANGE errors

IMPORTRANGE Formula parse error

It’s kid’s stuff! Formula parse error means that you’ve made a mistake in the IMPORTRANGE formula syntax. Check out our IMPORTRANGE Tutorial to refresh your knowledge about this Google Sheets function.

How to fix

Verify the formula syntax. Make sure to also validate the ID of the spreadsheet, as well as the sheet name specified in the range. These are the most common reasons for the formula parse error.

IMPORTRANGE #REF! Permission error

This error states that “You don't have permissions to access that sheet.” In most cases, this means that you’re trying to import data from an unshared Google Sheets doc that is not stored on your Google Drive. 

How to fix

Share the source spreadsheet with the owner of the target spreadsheet or make the file shareable with “Anyone with the link.

IMPORTRANGE #REF! Allow access

This is more of a warning than an error. When you import a range from an unshared Google Sheets document stored on your Google Drive, IMPORTRANGE will require you to connect the source and the target sheets.

How to fix

Click the Allow access button to connect the sheets.

IMPORTRANGE #Error! Result too large 

You’ll see this error when you’re importing too many cells. Unfortunately, the exact amount of cells you can import with IMPORTRANGE is undisclosed. In our example, we tried to import 60 columns and 6000 rows (360,000 cells). After we decreased the data range to 4300 rows (258,000 cells), the IMPORTRANGE formula worked. 

How to fix

Split the data range into two or more pieces, either vertically (by rows) or horizontally (by columns). Nest IMPORTRANGE formulas for each piece within the ARRAYFORMULA function as follows:

For horizontally split pieces (use commas between IMPORTRANGE formulas):

=ARRAYFORMULA({IMPORTRANGE("sheet-id","data-range-piece#1"),IMPORTRANGE("sheet-id","data-range-piece#2"),...})

For vertically split pieces (use semicolons between IMPORTRANGE formulas):

=ARRAYFORMULA({IMPORTRANGE("sheet-id","data-range-piece#1);"IMPORTRANGE("sheet-id","data-range-piece#2");...})

For example, here is a failed IMPORTRANGE formula:

=importrange("1bS7FGBbA7nInZJ2VBMaPxqf5B35RXpn-Z3vEcHlTwQo","Data!A:BH")

We split the data range "Data!A:BH" by columns into "Data!A:AM" and "Data!AN:BH" and applied the following formula:

=arrayformula({importrange("1bS7FGBbA7nInZJ2VBMaPxqf5B35RXpn-Z3vEcHlTwQo", "Data!A:AM"),importrange("1bS7FGBbA7nInZJ2VBMaPxqf5B35RXpn-Z3vEcHlTwQo", "Data!AN:BH")})

IMPORTRANGE #REF! and frozen formulas 

This glitch is well known among Google Sheets users. Yesterday, your IMPORTRANGE formulas worked well. Today, they return #REF! and seem to be broken for no reason. 

It happens randomly and sometimes fixes itself. For many years, Google has failed to find a stable solution to get rid of this ongoing issue with IMPORTRANGE. 

How to fix

There are many approaches to fixing this issue:

  • Hard refresh of the sheet and/or browser
  • Re-adding the IMPORTRANGE formula to the same cell (use the Google Sheets shortcuts Ctrl+X and then Ctrl+V or clear the cell and use Ctrl+Z to restore it)
  • Nest IMPORTRANGE with IFERROR
=IFERROR(IMPORTRANGE("sheet-id","range"))

The sheet will reattempt the data import again and again automatically.

  • Use the =now() trick:
    • Insert a NOW formula (=now()) in a random cell of the source and target spreadsheets
    • Insert an IMPORTRANGE formula that references the NOW formula of the other spreadsheet
    • Go to File => Spreadsheet settings => Calculation and select Recalculation “On change and every minute
  • Split large chunks of data into pieces using ARRAYFORMULA + IMPORTRANGE, just like with Error! Result too large

If you know other solutions/approaches to dealing with IMPORTRANGE #REF!, please share them with us to include in the article. 

How to fix all IMPORTRANGE errors at once: A peace of mind solution

The best way to fix IMPORTRANGE fails is to avoid them. Let’s say you have 100 source sheets from which you import data to 30 sheets using IMPORTRANGE formulas. From the target sheets, you import data to another 10 sheets again with IMPORTRANGE. If all these formulas are stuck, you will face issues with troubleshooting and fixing them! 

IMPORTRANGE is a function, and it takes some time to process calculations, which slows down the general performance of a spreadsheet. Instead, you can use the IMPORTRANGE alternative – Google Sheets importer. It is free of the mentioned IMPORTRANGE performance issues, since no calculations are performed in the spreadsheet. It also pulls the static data and saves it in your spreadsheet, just in case anything goes wrong.

To set up the Google Sheets importer, you need to install Coupler.io. It is a Google Sheets add-on to import data from third-party data sources such as spreadsheets, CSV files, and numerous apps. Install Coupler.io from the G Suite Marketplace.

Import data with the Google Sheets importer

Once installed, you need to set up the Google Sheets importer to retrieve your data. For this, open your target sheet and run Coupler.io:

Add-ons => Coupler.io => Open dashboard

Click +Add Importer and select Google Sheets. Then complete the following steps:

1. Fill out the “Title” field

Enter the name of your importer.

2. Set up your data source

2.1. Add your Google Account
  • Click Connect.
  • Pick the Google Account you want to connect to.
  • Sign in to the chosen Google Account.
  • Click Allow to grant access.
2.2. Fill out the “Spreadsheet URL” field

Enter the URL or ID of the spreadsheet to import data from.

2.3. Fill out the “Sheet name(s)” field

Enter the name of the sheet to import data from. If you want to import and concatenate data from multiple sheets, enter their names each in a new line.

Fill out this field if you want to pull data from multiple sheets into one. Make sure the column headers are identical across all sheets that are going to be merged.

Additionally, you can set up the range to import. For this, click Show advanced and specify the range in the “Range” field.

3. Set up your data destination

3.1. Fill out the “Sheet name” field

Name the sheet, which will be receiving data. 

Additionally, you can set up the first cell where the data range will be imported. For this, click Show advanced and specify the cell in the “Cell address” field.

4. Configure importer’s settings

4.1. Enable the Automatic data refresh

If you want your data to refresh automatically, toggle the automatic data refresh on and set up the schedule.

  • Select Interval
  • Select Days of week
  • Specify Time range

Check out more about Automatic data refresh.

Additionally, you can pick an import mode (replace or append) and add a column specifying the date of the last data refresh. For this, click Show advanced and turn on the respective functions. 

In the end, click Save & Run and welcome your data in the spreadsheet. 

IMPORTRANGE or Coupler.io: Which is better?

You’d better say NO to IMPORTRANGE and YES to Coupler.io if:

  • You import data recurrently
  • You import huge amounts of data
  • You import data from other sources like CSV, Pipedrive, etc.
  • It’s crucial for you to have access to data no matter what
  • You’re already tired of IMPORTRANGE issues

In all other cases, you can go with IMPORTRANGE easily because it’s a good function, though not reliable at all. Make the right choice and good luck with your data!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free