Getting your data into spreadsheets

To begin, you need to get your data into a spreadsheet from wherever it’s located. Google Sheets and Microsoft Excel are particularly useful for importing data from other sources and reshaping it to fit your needs.

Which tool are you using to import your data?

If you’re using Google Sheets

Third-party tools

There are a number of helpful services, add-ons, and tools that specialize in getting data into Google Sheets.

Here are some suggestions:

Supermetrics

Supermetrics enables you to schedule an automatic daily refresh of your data on Google Sheets.

With just four quick steps, your Supermetrics data will be in Google Sheets.

  1. Install Supermetrics for Google Drive.
  2. When the Supermetrics sidebar opens, choose your data source to log in to.
  3. Log in to your chosen data source and approve permission when prompted.

    Note: You need to log in with a user account that has access to your chosen data source. For example, if you choose Google Analytics, you must log in with a Google Account that has Analytics enabled.

  4. You can now start running queries in Google Sheets.

Blink Reports for Xero

If you use Xero for your online accounting, Blink Reports is an add-on that can automatically import your financial data to Google Sheets.

Google Analytics add-on for Google Sheets

Google Analytics offers an extension for Google Sheets that enables you to pull any data that you’d be able to access in the Analytics API directly into a spreadsheet.

Import functions

Google Sheets offers 5 built-in import functions that enable it to pull data from a variety of sources into your spreadsheet. Using these functions, you can easily scrape data from web pages, feeds, and files.

ImportData

Fetches data from a given URL in .csv (comma-separated value) or .tsv (tab-separated value) file format.

Syntax: IMPORTDATA(url)

Further reading: How to use the IMPORTDATA function to display your data.

ImportFeed

Imports an RSS or ATOM XML feed.

Syntax: IMPORTFEED(url, query, headers, num_items)

ImportHTML

Imports data from a table or list within an .html page.

Syntax: IMPORTHTML(url, query, index)

Further reading: How to use the IMPORTHTML function to display your data.

ImportRange

Imports a range of cells from a specified spreadsheet.

Syntax: IMPORTRANGE(spreadsheet_key, range_string)

ImportXML

Imports data from any of various structured data types including .xml, .html, .csv, .tsv, and RSS and ATOM XML feeds.

Syntax: IMPORTXML(url, xpath_query)

Further reading

Using the built-in finance function GOOGLEFINANCE, you can pull back market data.

GoogleFinance

Fetches current or historical securities information from Google Finance.

Syntax: GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])

Further reading: How to use the GOOGLEFINANCE function to display your data.

A combination of Google Sheets’ in-built functions and scripting can also be used to create custom widgets for your dashboard.

Today and Minus

Can be combined to calculate the time difference between a date in the future and today. Use in conjunction with a script to create a countdown widget for your dashboard.

Syntax: TODAY() and MINUS(value1, value2)

Further reading: How to use the TODAY and MINUS functions to create a countdown timer.

Scripting

Scripting offers an easier way to write large spreadsheet formulas and functions.

Google Apps Script is a scripting language based on JavaScript that provides easy ways to automate tasks across Google products and other services. There's nothing to install and your scripts run on Google's servers.

Further reading: Google’s tutorials for using Google Apps Scripts with Google Sheets.

If you’re using Excel

Tip: One advantage of Google Sheets over Excel is that some of these tools can run in the background even when your spreadsheet isn’t open.

Third-party tool

Report Builder for Adobe Analytics

Report Builder is an Excel plugin that enables you to import your Adobe Analytics data into Excel.

Suggested next steps

Format your spreadsheet

Connect your spreadsheet with Geckoboard

Create a visualization from your spreadsheet

Was this article helpful?
👍

Thank you for your feedback!