ImportRange helps you import data from one Google Sheet into another and keeps the two spreadsheets in sync at all times. This function can be helpful when you:
Only need a subset of the data in the main spreadsheet for your metrics.
Want to run calculations on your metrics, but don't want to edit the source spreadsheet.
See the 'This worksheet is too large to import' error, which means your spreadsheet file exceeds the 5MB upload limit.
ImportRange syntax
=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 used 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 and paste the URL or short key inside quotation marks.Type 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.When your new sheet is complete, switch to Geckoboard and connect your sheet using our Google Sheets data source.
Once connected, you're ready to build dashboards widgets from your spreadsheet. For guides to creating spreadsheet dashboard widgets, select from the following visualization types:
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.
Click on the Extensions menu and select the option Apps Script.
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("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A:B")') }Save your project and give it a name.
To create a trigger for your function, click on the project's trigger's menu.
Click the + Add Trigger button in the bottom right corner of the page.
Set a trigger for the myFunction function, that runs from Head on a Time-driven source that's triggered on a Minute timer.
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.