Google Sheets has a built-in function called ImportRange, which imports a range of cells from a specified spreadsheet. This function comes in handy when working around limitations and constraints that could otherwise prevent you from getting data from Google Sheets onto Geckoboard.
Here's a short but thorough video quite popular on YouTube for explaining ImportRange
We recommend using ImportRange when:
- Your spreadsheet file exceeds the 5 MB upload limit
- You want to run calculations, but don't want to edit an existing spreadsheet
Your spreadsheet file exceeds the 5 MB upload limit
Our Spreadsheets data source has a 5MB file size limit for uploaded spreadsheets. If your file is already 5 MB or greater, or you suspect it could reach that size in the future,
ImportRange can make all the difference, as normally you just need a subset of the data in the worksheet for your metrics.
Example of using ImportRange
Imagine that a fictional spreadsheet called All sales since 2001 contains all your sales data for the past 20 years or so, and in as many as 10 individual sheets. The file is already 5 MB in size, but you only need the information in columns
sheet1 for a metric of sales so far this month.
So, because you cannot connect All sales since 2001 to Geckoboard, you could create a second spreadsheet in Google Sheets called something like Sales this month. You could then use
ImportRange on this new spreadsheet to get the data you want. For this, you'd type
IMPORTRANGE('https://docs.google.com/spreadsheets/d/abcd123abcd123', 'sheet1!A:B') in cell
A1. That will import all the data in cells
B in All sales since 2001.
Once the data you need is in Sales this month , you can connect this spreadsheet to Geckoboard and start building visualizations from the data.
You want to run calculations, but don't want to edit an existing spreadsheet
This scenario often occurs when an entire team shares a spreadsheet (i.e. to keep it up-to-date amongst them). The spreadsheet is used like a database to keep information, and you want to preserve the spreadsheet's main function as a source of raw data or log. However, you need to perform some operations in order to calculate your metrics.
In this case,
ImportRange can be used to keep the original spreadsheet untouched and import the data you need for your metrics from the original source onto a second spreadsheet using
ImportRange – similar to the above example.