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.

Video tutorial: 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

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.

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.

Note: Widgets powered by spreadsheets using the ImportRange function refresh approx. every 15 minutes. If this isn't fast enough for your needs, you can run a script and set your own refresh interval.

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("", "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.

Optional: Speed up refresh periods for ImportRange

Widgets powered by spreadsheets using the ImportRange function refresh approx. every 15 minutes. If this isn't fast enough for your needs – and your G Suite account allows it – you can write a script that can further reduce the refresh times.

Step 1: Write a script

To write a script using Google Apps Script Editor, follow these steps:

  1. Click on the Tools menu and select the option Script Editor.

  2. You'll see a script.

  3. Replace the default function with following script. Then edit the script by first replacing the .setValue('IMPORTRANGE') function with your own, then making sure the .getRange('cell') in your Sheet exactly match the one in the script, otherwise ImportRange will not update automatically.

    function myFunction() {SpreadsheetApp.getActive().getRange('A1').setValue('=IMPORTRANGE("", "sheet1!A:B")')
  4. Save your project and give it a name.

Step 2: Set up a trigger

To create a trigger for your function, follow these steps:

  1. Click on the project's trigger's menu.

  2. Click the + Add Trigger button in the bottom right corner of the page.

  3. Set a trigger for the myFunction function, that runs from Head on a Time-driven source that's triggered on a Minute timer.

    trigger settings in Google Sheets
  4. Once you set the trigger, run the script for the first time so that you don't have to wait 1 hour to get data.

You're done!

Now that you've written the script and set the trigger running, you'll be able to see the data on your Google Sheet refresh faster than the default 15 minutes. This will continue to refresh even when your Google Sheet is closed.

Potential Google server errors

We've found that a few times a week, a server error will occur on Google's end and the script will fail to run. By default, you'll receive an email to notify you that this has happened. Considering the script runs every minute of the day which totals 1440 times, a few times a week is nothing to be concerned about.

Did this answer your question?