Google Sheets has a built-in function called ImportData which fetches data from a given url in CSV (comma-separated value) or TSV (tab-separated value) format.
We'll show you how this function can be used with our Spreadsheets data source to display visualizations out of CSV files on Geckoboard.
Note: Widgets powered by spreadsheets using the ImportData 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.
Syntax of the ImportData function
The syntax of the ImportData function is IMPORTDATA(url), where url is the url from which to fetch the CSV or TSV-formatted data, including protocol (e.g. http://).
The value for url must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
Step-by-step example: Bitcoin exchange rate
In this example, we'll use the ImportData function and the Spreadsheets integration to display Bitcoin's historic price (in $USD) on a dashboard, but more generally CSV files can help adding all sorts of proprietary data.
Step 1: Export exchange rate database from Quandl
Quandl helps data analysts save time, effort and money by delivering high-quality financial and economic data in the precise format they need. In this example, we'll use data in CSV format from their API.
To export a CSV database from Quandl for this example, follow these steps:
Go to Quandl.
Use the Database browser to find Bitcoin's Spot Exchange Rates databases. We'll use Bitcoin Markets (bitfinexUSD) to demonstrate its usage.
Once you find the database you are after, look for its Export Data menu on the right of your screen.
Click on CSV and copy the link for this resource (in this case https://www.quandl.com/api/v3/datasets/BCHARTS/BITFINEXUSD.csv). You're now ready for Step 2.
Step 2: Import CSV file into Google Sheets
To import the CSV output from Quandl into Google Sheets, follow these steps:
Open a new Google Sheet.
Type the following formula into cell A1:
=IMPORTDATA("https://www.quandl.com/api/v3/datasets/BCHARTS/BITFINEXUSD.csv")
Save your Google Sheet and give it a name.
Step 3: Use Spreadsheets to visualize the data in Geckoboard
With Bitcoin's exchange rate information in your Google Sheet, create a new Spreadsheets widget in Geckoboard to display this information on the dashboard.
Optional: Speed up refresh periods for ImportData
Widgets powered by spreadsheets using the ImportData 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:
Click on the Tools menu and select the option Script Editor.
You'll see a script.
Replace the default function with following script. Make sure the cell in your Sheet exactly match the one in the script, otherwise ImportData will not update automatically.
function myFunction() { SpreadsheetApp.getActive().getRange('A1').setValue('=importdata("https://www.quandl.com/api/v3/datasets/BCHARTS/BITFINEXUSD.csv")') }
Save your project and give it a name.
Step 2: Set up a trigger
To create a trigger for your function, follow these steps:
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.
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.
Further reading