3

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?

2

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.

7

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.

  • 1
    This solution worked for me!! +1 – mongoose36 Aug 7 '17 at 21:02
  • This option works perfectly! – Sandeep C Nov 1 '17 at 10:26
  • This one worked for me too. – TheOnlyAnil Mar 30 at 6:49
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.

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.

0

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

protected by Community Jul 30 '18 at 22:34

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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