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 log in? Regardless, monitoring your data is a hassle and you need a solution pronto.

...drumroll please...

Say hello to our spreadsheets 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:

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.

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.

Google Sheets’ add-on for Google Analytics

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 more detail about using Google Sheets’ IMPORTDATA function to display CSV data here.

IMPORTHTML(url, query, index) - Imports data from a table or list within an HTML page.

See more detail about using Google Sheets' IMPORTHTML function here.

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 more detail about using Google Sheets’ IMPORTXML function here.

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

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 integration.

Here are the 5 steps we took to pull data from our Developer Community to Google Sheets, to a dashboard:

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 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. Used Google Sheets standard functions to identify the rows containing topics created Today and count them. Used Geckoboard’s spreadsheets integration to get the 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

See Excel dashboard example.

Want 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.

Steps to add spreadsheet data onto a dashboard with Geckoboard

Watch this video or follow the steps below to get your data onto a TV dashboard.

Create your spreadsheet in Google Sheets or Excel (importing data from via the steps mentioned earlier in this post)

Log in to your Geckoboard account and select ‘add dashboard’, then ‘add widget’ (or sign up for a free trial here to have a go).

Select the ‘spreadsheet’ integration from the list of integrations.

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.