Want to monitor your data but limited by in-app options? Maybe your data “lives” in an application that doesn’t offer a dashboard view of the metrics? Maybe your data is in too difficult a format to monitor? Perhaps you want other team members to be able to easily view the raw data without having to login? Regardless, monitoring your data is a hassle and you need a solution pronto.
Say hello to the spreadsheet integration. All you have to do is get your data from wherever it lives into an Excel spreadsheet or a Google Sheet. Easy peasy, right? Actually, it is!
In this post we look at four different ways to get almost any data into a spreadsheet:
- Manual Input
- Third-party tools (Excel Spreadsheets or Google Sheets)
- Import Functions (Google Sheets)
- Scripting (Google Sheets)
- (Bonus) Displaying your spreadsheet data into a dashboard (Google Sheets and Excel)
GETTING YOUR DATA INTO SPREADSHEETS
1. Manual Input
Entering data by hand (typing it in) is likely the most common and least efficient 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 we won’t dive into this process here. Instead, we’ll focus on other automated or real-time methods of getting data into spreadsheets.
2. Third-party tools (Excel Spreadsheets or Google Sheets
A variety of services, add-ons and tools specialize in getting data from your app into spreadsheets. Let’s look at some of those below.
Supermetrics (for Google Sheets)
Supermetrics lets you pull in data from lots of tools and onto Google Sheets. It allows you to schedule an automatic daily refresh of your data on Google Sheets.
Tools that works 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)
With just four quick steps, your data will be in Google Sheets.
- Install the add-on
- Choose your data source (see available sources below)
- Log in to data source and approve permission
- Start running queries in Google Sheets
Other third-party tools
Blink Reports (Google Sheets): If you use Xero for accounting, Blink Reports can automatically import your data to Google Sheets.
Report Builder (Excel): If you use Adobe Analytics, Report Builder is a plugin that enables you to 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 some of our favorite third-party tools, so if you’re looking for something specific you may want to do a quick online search.
3. Import Functions (Google Sheets)
An entire library of functions in Google Sheets allows you to achieve powerful results without having to write your own script.
Google Sheets offers five different import functions (listed below) that allow it to pull data into your spreadsheet from a variety of sources including XML, HTML, RSS and CSV - perfect for importing lists of blog posts, tweaks, product inventories or data from another service.
Here are the specific functions along with the type of data they collect:
IMPORTDATA(url)- Imports data at a given url in .csv (comma-separated value) or .tsv (tab-separated value) format
See here more detail about using Google Sheets’IMPORTDATA function to display CSV data.
IMPORTHTML(url, query, index)- Imports data from a table or list within an HTML page.
See here more detail about using Google Sheets’ IMPORTHTML function.
IMPORTXML(url, xpath_query)- Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
See here more detail about using Google Sheets’ IMPORTXML function.
IMPORTFEED(url, query, headers, num_items)- Imports a RSS or ATOM feed.
IMPORTRANGE(spreadsheet_key, range_string)- Imports a range of cells from a specified spreadsheet.
Using these functions, you can easily scrape data from web pages, feeds and files. Also, a built-in finance function enables you to pull back market data.
Other useful Google Sheets functions
- Creating a weather widget using Google Sheets
- Creating a Countdown Timer using Google SheetsUsing Google Sheets’
- GOOGLEFINANCE function to display market data
- Get more spreadsheet tips in our 6-part email course. Sign-up using the form on the right-hand side.
4. Scripting (Google Sheets)
Scripting (or in non-technical terms - writing a short bit of custom code) is a powerful way to get data into Google Sheets. It’s basically an easier way to write large spreadsheet formulas and functions.
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.
Using Google Apps Scripts to get data into our Developer Community on Discourse
At Geckoboard, we use Discourse for our Developer Community and have a dashboard widget to show the number of new topics created “Today”. We built that particular dashboard widget using a combination of Google Apps Scripts and our spreadsheets integrations.
Here are the 5 steps we took to pull data from our Developer Community ➡️ Google Sheets ➡️ a dashboard:
- Created a new Google Sheets’ Spreadsheet 2.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) 4.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. 6.Use Google Sheets standard functions to identify the rows containing topics created Today and count them.
- Use Geckoboard’s spreadsheet integration [link] to get display data onto a dashboard (more about this in the section below).
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.
Beyond spreadsheets: Getting the most out of your data
Wanting to take your spreadsheet game even further? Now that you’ve imported data from various sources into your Google Sheets and Excel spreadsheets, you can display this data in a visual format, using a dashboard.
Each of the widgets on the CEO dashboard example below pull in data from various Google Sheets spreadsheets via Geckoboard’s magical spreadsheets integration [link].
Steps to add spreadsheet data onto a dashboard with Geckoboard
- Create your spreadsheet in Google Sheets or Excel (importing data from via the steps mentioned earlier in this post)
- Login to your Geckoboard account and select ‘add dashboard’, then ‘add widget’.(you can sign-up for a free trial [here] to have a go yourself!)
Select the ‘spreadsheet’ integration from the list of integrations. You can upload a spreadsheet (either from your computer or through a URL).
Build your dashboard. From here you can play around with different graphs to visualize your data. You can choose which data to pull in and changes will appear in real time on the page as you customize your chart.
Need some more dashboard inspiration? Check out this example Spreadsheets Dashboard.