7

I'm trying to IMPORTRANGE from a range containing 240,000 cells (40 columns and 6,000 rows). The IMPORTRANGE function is erroring out with "Results too large". I can't seem to find documentation on the function's limitations.

What are the limitations of IMPORTRANGE?

How can I get around this so I can import that data into my sheet?

Improve this question
4

5 Answers 5

Reset to default
3

My experiments on this would suggest that there is a max limit on the number of items that you can import using importrange() which id not linked to the total number of cells in your Google Sheet.

This figure seems to be around 175,000 cells. I'm not sure if this is the offical max, but that's the most I have been able to import without getting a 'too large' error.

Improve this answer
11

I was having a similar problem too.

Try splitting up your import range with an array formula.

Example:

Importrange("sheet", "A:ZZ")

becomes

ARRAYFORMULA({Importrange("sheet", "A:ZZ5000");Importrange("sheet", "A5001:ZZ10000")...etc...})

Test this with different data sizes to get the shortest version you can and it should do what you need.

Improve this answer
4
  • 1
    This solution worked for me!! +1
    – mongoose36
    Aug 7, 2017 at 21:02
  • This option works perfectly!
    – Sandeep C
    Nov 1, 2017 at 10:26
  • This one worked for me too.
    – TheOnlyAnil
    Mar 30, 2019 at 6:49
  • Better to split the Columns Example: rather than Importrange("sheet", "A:Z") just split them Importrange("sheet", "A:K") and then Importrange("sheet", "L:Z")
    – Yasin Hassanien
    Sep 6, 2020 at 14:09
2

Empty cells may factor. We observed importrange breaking at 23573x11 or 259k cells, typical growth is 10 or so rows daily, so we have been over 250k cells for a while. One column is mostly empty, a couple others have a few blanks.

I could not get ARRAYFORMULA to parse as shown above, or with other guesses, so I used this in my hidden "Ingest" tab.

=importrange("sheet", "A1:K10000") in cell A1
=importrange("sheet", "A10001:K") in cell A10001

My working/presentation tab uses

=query(Ingest!A:K,,1)

to allow persistent formatting, our source sheets are overwritten daily.

Improve this answer
1

From my experience using IMPORTRANGE, the number of cells was not the cause at all but any time I exceeded 36 columns it failed. My results could be 600 rows or 6000 rows as long as I didn't exceed 36 columns. Ironically you can get around this by combining IMPORTRANGE functions.

Example: =QUERY({IMPORTRANGE("Spreadsheet_Key", "Sheet1!A:AI") , IMPORTRANGE("Spreadsheet_Key", "Sheet1!AJ:AM")}, "WHERE Col38= 'test'")

Note the braces {} used before and after the two IMPORTRANGE functions

Improve this answer
1

Using the answer of Sam and reading documentation, I found the way how to get result of BIG DATA without error. For that you need to make an export step by step. In one query. For example if you need to export data sheet!A3:X100000.

Try to do the following: first make a query and select only 

=QUERY(importrange("link_sheet", "sheet!A3:X10000"), "select *", 0);

after get result just edit query from 

=QUERY(importrange("link_sheet", "sheet!A3:X10000"), "select *", 0);

to 

=QUERY(importrange("link_sheet", "sheet!A3:X20000"), "select *", 0);

after getting data edit a query again 

=QUERY(importrange("link_sheet", "sheet!A3:X300000"), "select *", 0);

and continue while you won't rich 

=QUERY(importrange("link_sheet", "sheet!A3:X100000"), "select *", 0);

with that way I could to import around 800 000 cells with data. For my task it was enough, but I think If I need longer result data, I could continue and it will works.

You should also remember that Google Spreadsheets have a limit on one document maximum can have only 2 million cells.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.