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. This article details how this function can be used with our Spreadsheets integration to display visualizations out of CSV files on Geckoboard.
This function doesn't update on its own by default so we've written a script that you can adapt to ensure your data is kept up to date. Our Bitcoin exchange rate example below shows the basics of using this function and does not use this script. If you'd like your data to update automatically, you will need to create the script once the example is completed.
- Syntax of the Function
- Geckoboard Example - Bitcoin exchange rate
- Updating Your Data Automatically
- Other Resources
Syntax of the Function
Here's the syntax of the IMPORTDATA function:
url- The url from which to fetch the .csv or .tsv-formatted data, including protocol (e.g.
- The value for
urlmust either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
- The value for
Geckoboard 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: 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.
Go to Quandl and use the Database browser to find Bitcoin's Spot Exchange Rates databases. We will use Bitcoin Markets (bitfinexUSD) to demonstrate its usage.
Once you find the database you are after, look for it's EXPORT DATA menu (top right), click on CSV and copy the link for this resource (in this case https://www.quandl.com/api/v3/datasets/BCHARTS/BITFINEXUSD.csv)
Step 2: Google Sheets
- To import the CSV output from Quandl into Google Sheets, open a new Google Sheet and type the following formula into cell A2:
- 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.
- From your Geckoboard dashboard, click + Add Widget.
- Choose the Spreadsheets integration.
- Select the Google Sheets File that you just saved.
- Select the Line Chart widget (default).
- Select the appropriate Sheet (Sheet1 in this example).
- Enter the Date values (column A) as X-Axis.
- Enter the Close values (column E) as Series A.
- Leave the Goal and other Chart options Disabled and adjust the title and size.
- Click Add to dashboard to add your widget to your dashboard.
Step 4: The result!
Geckoboard will plot a Line Chart with the information found in columns A and E on your dashboard - Bitcoin's exchange rate (historic) values.
Updating Your Data Automatically
Unfortunately, the Google Sheets IMPORTDATA function doesn't automatically update. This isn't particularly helpful so we've written a script that you can adapt to update the market data every minute, even when the Google Sheet is closed.
Step 1: Write a Script
- Click on the Tools menu and select the option Script Editor...
- A Google Apps Script window will appear. Click the Blank Project option.
- Replace the default function with the script shown below (Important: Make sure you follow the aforementioned instructions to the letter, otherwise cells in the Sheet might not match the ones in the script and your countdown will not update automatically):
- Save your function and give it a name.
Step 2: Set up a Trigger
- From inside the Script Editor, click on the Resources menu and select the option Current project's triggers
- In the window that appears, click the link No triggers set up. Click here to add one now.
- This will create a trigger for your function. Change the default Hour timer to the one that best fits your needs. In this case Day timer as updating once per day is enough for this example.
- Click Save.
- You'll be prompted to authorize your app by clicking Continue and then Accept. More information on Google Apps authorization can be found here.
- You can now close the Script Editor.
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 every minute. This will continue to refresh even when your Google Sheet is closed.
Your Geckoboard widgets don't require any changes to reflect the addition of this script and trigger; they will refresh every 10 minutes with the new data from your Google Sheet, just as they always have. The difference now is that your Google Sheet will have new data in it!
Note: 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.
If you're looking for other ways to get data into Google Sheets to use with Geckoboard's Spreadsheets integration, check out our other articles:
- An alternative method for getting Google Analytics data on to your dashboard using Google Sheets
- Using Google Sheets' GOOGLEFINANCE function to display market data in Geckoboard