Use Google Sheets’ ImportRange function to display data in Geckoboard

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.

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 A and B on 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 A and 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.