Use Google Sheets’ ImportData function to display data in Geckoboard

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 integration to display visualizations out of CSV files on Geckoboard.

By default, the ImportData function doesn't update on its own if the spreadsheet is not being edited. We've written a script that you can adapt to ensure your data is kept up to date even when the spreadsheet is closed. 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 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://).

Note

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:

  1. Go to Quandl.
  2. Use the Database browser to find Bitcoin's Spot Exchange Rates databases. We'll use Bitcoin Markets (bitfinexUSD) to demonstrate its usage.Quandl_Bitcoin_info_in_CSV
  3. Once you find the database you are after, look for its Export Data menu on the right of your screen.
  4. Click on CSV and copy the link for this resource (in this case https://www.quandl.com/api/v3/datasets/BCHARTS/BITFINEXUSD.csv)Export_Data_API
  5. 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:

  1. Open a new Google Sheet.
  2. Type the following formula into cell A2:

    =IMPORTDATA("https://www.quandl.com/api/v3/datasets/BCHARTS/BITFINEXUSD.csv")Import_CSV_file
  3. 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.

To visualize the exported data, follow these steps:

  1. From your Geckoboard dashboard, click Add widget.
  2. Choose the Spreadsheets integration.spreadsheets_button.jpeg
  3. Select the Google Sheets File that you just saved.
  4. Select the Line Chart widget (default).
  5. Select the appropriate Sheet (Sheet1 in this example).
  6. Enter the Date values (column A) as X-Axis.
  7. Enter the Close values (column E) as Series A.CSV_widget_setup
  8. Leave the Goal and other Chart options Disabled and adjust the title.
  9. Click Add to dashboard to add your widget to your dashboard.

The end 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.CSV_widget.png

Step-by-step guide: How to automatically update your data

Unfortunately, the Google Sheets ImportData function doesn't automatically update. But you can adapt our script to update the market data every minute, even when the Google Sheet is closed.

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. Setup_script_blank_project.png
  3. Replace the default function with following script:

    Important note

    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")')
    }
    Replace_default_function_with_script
  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. trigger_for_daily_update.png
  2. Click the + Add Trigger button in the bottom right corner of the page. add_trigger.png
  3. Set a trigger for the myFunction function, that runs from Head on a Time-driven source that's triggered on an Hourly timer.Google Analytics authentication box
  4. Once you set the trigger (and so that you don't have to wait 1 hour to get data), run the script for the first time. run_script.png

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 every hour (if you prefer -and your GSuit accounts allows it- you can lower the refresh to every minute instead). 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 15 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.Google_server_error.png

Further reading

Was this article helpful?

Awesome! 👍 Thanks so much for your feedback!

Sorry about that! Start a conversation now.

We're ready to help, 24 hours a day, 5 days a week

Get support
  • Fernanda Customer Success Avatar

    Megan

    USA
  • Hariharan Customer Success Avatar

    Hariharan

    India
  • Fernanda Customer Success Avatar

    Yasmin

    Spain
  • Luis Customer Success Avatar

    Luis

    UK
  • Richard Customer Success Avatar

    Richard

    UK
  • Fernanda Customer Success Avatar

    Fernanda

    Brazil
  • Heather Customer Success Avatar

    Kirby

    USA
  • Heather Customer Success Avatar

    Heather

    USA