The manual way and with Airboxr
If you're an avid user of Google Sheets, you would know about the variety of import functions, namely IMPORTRANGE, IMPORTDATA, IMPORTHTML, IMPORTFEED, or IMPORTXML. Those simple formulas make pulling data from external sources so much easier.
However, performance is greatly reduced when a large influx of data is involved and the sheet will take forever to load. Import formulas tend to load slower in the first place since they need to scour the Internet for information.
This is one of the core issues with Google Sheets—if the raw data is on a heavy sheet (a sheet with loads of data) and a lot of people are pulling data from that sheet using the import functions, all associated sheets will slow down.
Does that mean we can't use Google Sheets when we want to pull huge amounts of data? Not quite. This article is going to introduce two ways to make your Google Sheets run faster. However, do note that many factors can slow down a sheet. The tips provided here are not a foolproof way, but they're definitely worth trying especially if you're importing huge chunks of data.
Use multiple import formulas
Since there is a limit to how many rows the import formulas can work on before it gets stuck at the loading stage (about 20,000 rows), we can import fewer rows at a time and string these formulas into one.
Let's say we have 20,000 rows of data in column A on Sheet1 of our source sheet that we want to import to a new spreadsheet. Instead of importing all 20,000 rows at once, we can import 10,000 rows twice and combine them like this:
={IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sheet1!A1:A10000"); IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sheet1!A10001:A20000")}
Even though this will speed up your Google Sheets, you can only use a maximum of 50 cross-workbook reference formulas (IMPORTRANGE) and 50 functions for external data (IMPORTDATA, IMPORTHTML, IMPORTFEED, or IMPORTXML) per workbook.
No formula needed with Airboxr
You don't need to use any formulas when you pull data from external sources using Airboxr.
All you have to do is launch the add-on and click the source you want to import data from.
Choose the columns you wish to pull. There is no limit to how many rows of data you can pull.
You can choose to filter the data to a specific time period.
And sum up the values using the summarize function. Like the filter feature, this is optional.
Click on import and you're done! ⚡️
There's no need to go through the hassle of typing multiple formulas and combining them to make Google Sheets faster when you use Airboxr.
No more sheet slow down. Query and import your data with Airboxr. Download it now.