The ImportRange function allows you to import data from one Google Sheet into another and keeps the data being imported in sync. This function can be helpful if you:
Need a subset of the data in the main spreadsheet for your metrics.
Don't want to edit the source spreadsheet but need to make calculations or changes to the data.
Your spreadsheet is very large in size and exceeds the 5MB limit.
ImportRange syntax
We recommend taking a look at the guide from Google on the use of this function and syntax. For reference, we've also included the basics below:
=IMPORTRANGE([spreadsheet_url], [range_string])
spreadsheet_url: The URL of the original spreadsheet. You can use the entire URL or just what's known as the spreadsheet key.range_string: The specific cells in the original spreadsheet you want to import and sync with the new spreadsheet. You can also use a named range here too.
How to use ImportRange in Google Sheets
Copy the URL of the Google Sheets from which you want to import the data.
You can also copy the short key from the URL instead of the full URL.
Type the
=IMPORTRANGE(part of the function, then inside quotation marks, paste the URL or short key.Add a comma and specify the range to import.
You need to specify the sheet name as well as the range inside double quotes.
You can use named ranges here, too, if using import ranges from several sources.
After entering this formula for the first time you'll see the
#REF!error in the sheet.Hover over the cell and click Allow access.
Connect the new sheet to Geckoboard build your widget as usual.
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, you can use Apps Script within your Google Sheet to speed up this refresh rate.
Within your Google Sheet, click the Extensions menu, then Apps Script.
Replace the default function with the following script.
function myFunction() {SpreadsheetApp.getActive().getRange('A1').setValue('=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A:B")') }Edit the script:
First, replace the
.setValue('IMPORTRANGE')function with your own URL.Then make sure the
.getRange('cell')in your sheet exactly matches the one in the script, otherwise ImportRange will not update automatically.
Click the floppy disc icon to save your project and give it a name.
Click the clock icon on the left to create a trigger for your function.
Click the + Add Trigger button in the bottom right corner of the page.
For Choose which function to run field, select myFunction function.
For Choose which deployment should run field, select Head.
For Select event source drop-down, select Time-driven.
For Select type of time-based trigger drop-down, select Minutes timer.
For Select minute interval field, select the interval you want it to run (every minute, every 5 minutes, or every 10 minutes are the options that will run faster than the default rate in Geckoboard).
When ready, click Save.
Go back to the Editor tab, then click Run at the top to trigger the first refresh now.