If you’ve ever tried importing large volumes of data using the IMPORTRANGE function in Google Sheets, chances are you’ve encountered the frustrating “Result too large” error. But fear not, my friend, because I have a simple workaround that will help you overcome this issue.
The Problem: “Result too large” Error
The IMPORTRANGE function, as you may know, returns the dreaded #ERROR! value when you try to import more cells than it can handle. Unfortunately, Google doesn’t provide official documentation specifying the exact limit, as it varies from user to user depending on the types of data in the cells. Personally, I came face to face with this error when attempting to import 26 columns and a whopping 20,000 rows. However, when I reduced the number of rows to 12,606, the formula worked like a charm. Sadly, it couldn’t handle any more for my specific data.
The Solution: VSTACK and HSTACK
Luckily, there are two fantastic ways to solve the “Result too large” error: using the VSTACK or HSTACK functions alongside multiple IMPORTRANGE formulas.
VSTACK with IMPORTRANGE
Let me show you an example formula that uses VSTACK to stack imported data vertically:
=LET(url,"https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=1707494620",VSTACK(IMPORTRANGE(url,"Sheet2!A1:Z10000"),IMPORTRANGE(url,"Sheet2!A10001:Z20000")))In this formula, we use IMPORTRANGE twice to import two separate sets of data. You can add more IMPORTRANGE formulas after the second one, separated by a comma.
HSTACK with IMPORTRANGE
Alternatively, you can use HSTACK to stack the imported data horizontally. Take a look at this example formula:
=LET(url,"https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=1707494620",HSTACK(IMPORTRANGE(url,"Sheet2!A1:M20000"),IMPORTRANGE(url,"Sheet2!N1:Z20000")))Just like with VSTACK, you can add more IMPORTRANGE formulas after the second one, separated by a comma.
Improving Performance
Now that you know how to handle the “Result too large” error, let’s talk about improving the performance of importing large volumes of data.
One option is to use the QUERY function in combination with VSTACK and HSTACK to filter and reduce the imported data. For instance, you can filter out blank rows based on a specific column using the QUERY function.
=LET(url,"https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=1707494620",VSTACK(QUERY(IMPORTRANGE(url,"Sheet2!A1:Z10000"),"Select * where Col5 is not null"),QUERY(IMPORTRANGE(url,"Sheet2!A10001:Z20000"),"Select * where Col5 is not null")))Similarly, you can remove columns using the QUERY function with HSTACK and IMPORTRANGE.
=LET(url,"https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=1707494620",HSTACK(QUERY(IMPORTRANGE(url,"Sheet2!A1:M20000"),"Select Col1,Col2,Col3"),QUERY(IMPORTRANGE(url,"Sheet2!N1:Z20000"),"Select Col10,Col11,Col12")))Remember, each formula returns 13 columns. So, select columns from 1 to 13 in each formula, rather than selecting 1-13 in the first formula and 14-26 in the second formula.
Conclusion
In conclusion, there are three methods to solve the IMPORTRANGE “Result too large” error and enhance performance:
- Split the data into smaller chunks and import each chunk. Then, use the VSTACK or HSTACK function to join the data, reducing the amount of data imported at once.
- Use the QUERY function to filter rows conditionally, shrinking the data.
- Use the QUERY function to remove columns, reducing the number of columns.
For more information on filtering IMPORTRANGE and utilizing the QUERY function, check out the tutorials available on Crawlan.com.
Now you have the tools to conquer the “Result too large” error and import large volumes of data seamlessly in Google Sheets. Happy importing!
