Geckoboard has pre-built integrations with over 60 cloud services, but there are hundreds of services in the cloud and thousands of traditional systems, which means there are services that aren't covered by our integrations.
If your data “lives” on an application we don’t have a pre-built integration for, you have mainly two alternatives to visualize your data on a Geckoboard dashboard: using our Spreadsheets integration or sending data to us via our API (i.e. using Datasets). Of the two, Spreadsheets is likely the easiest one.
All you have to do is get your data into an Excel spreadsheet or Google Sheet and from there to your dashboard using our Spreadsheets integration. The latter - spreadsheets to Geckoboard - is a given, but how can you get your data into a spreadsheet in the first place?
Getting your data into spreadsheets
Entering data by hand (typing it in) is likely the most common way to get data into a spreadsheet. This method implies you regularly interact with spreadsheets and this is where your data is kept. Manual input is better suited to data that doesn’t update as frequently so this article doesn’t cover it. Instead, it focuses on other methods of getting data into spreadsheets.
Third party tools (Excel or Google Sheets)
A variety of services, add-ons and tools specialist in getting data from your app into spreadsheets.
For example, if you use one of the tools listed below, Supermetrics would allow you to schedule an automatic daily refresh of your data on Google Sheets.
Tools that work with Supermetrics:
Bing Ads, Facebook Ads, Facebook Insights, Google Adwords, Google Analytics, Google BigQuery, Google Search Console, Google+, Instagram, LinkedIn, MailChimp, Moz, Pinterest, Reddit, SEMrush, Stripe, Tumblr, Twitter, Vimeo, VKontakte, Yahoo Gemini, YouTube or your own database (MySQL, Oracle, Google Cloud SQL, SQL Server)
In a similar way, if you use Xero for accounting, Blink Reports can automatically import your data to Google Sheets.
A third party tool we know some customers have found useful is the Google Sheets’ add-on for Google Analytics.
Of course third party tools are also available for Microsoft Excel. For example, if you use Adobe Analytics, Report Builder is a plugin that enables you extract your Analytics data from Adobe into Excel.
An advantage of Google Sheets over Excel is that some of these tools can run in the background even when your spreadsheet isn’t opened.
This is only a short list of the available third party tools, so if you’re looking for something specific we definitely recommend a quick online search. You can also let us know about it in our Developer Community
Import Functions (Google Sheets)
In Google Sheets, an entire library of functions allow you to achieve powerful results without having to write your own script. Using Google Sheet’s import functions, you can scrape data from web pages, feeds and files. Also, a built in finance function enables you to pull back market data.
We have a few articles explaining how to use these functions:
- Using Google Sheets’ IMPORTHTML function to display data in Geckoboard
- Using Google Sheets’ IMPORTDATA function to display CSV data in Geckoboard
- Creating a Countdown Timer using Google Sheets
- Using Google Sheets' GOOGLEFINANCE function to display market data in Geckoboard
Scripting (Google Sheets)
Scripting is a very powerful way to get data into Google Sheets. Google Apps Script is a cloud based scripting language that provides easy ways to automate tasks across Google products and other services.
These scripts can run in the background, can be automated via triggers and have a community-based support model.
The following example demonstrates use of this method to get data into Google Sheets.
At Geckoboard, we use Discourse for our Developer Community and have a widget to show the number of new topics created “Today”. We built that particular widget using a combination of Google Apps Scripts and our Spreadsheets integration. Here are the steps we took:
- Created a new Google Sheets' Spreadsheet
- Created a new script for our Spreadsheet (Tools > Script editor) and copy and pasted chrislkeller's ImportJSON functions (to be able to import the output of Discourse’s API which is JSON).
- Looked in Discourse's API documentation for a call that returned the latest topics created in the Community (which turns out to be https://community.geckoboard.com/latest.json)
- Added a new function in our script to call the endpoint identified on step 3 and update cell A1 with the response.
- Finally, added a trigger to run the function myFunction every hour.
With the data already in Google Sheets, it was simply a matter of using Google Sheets standard functions to identify the rows containing topics created Today and count them.
Google offers a variety of resources to help you make the most of Google Sheets using Google Apps Scripts. You might find some of their examples useful to get your data into Google Sheets.
Hopefully you’ve seen how useful Google Sheets and Excel can be for importing data from other sources and reshaping it to fit your needs.
The final step is getting the data onto your dashboard which our Spreadsheets article covers in detail.