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.
There are a number of helpful services, add-ons, and tools that specialize in getting data into Google Sheets.
Here are some suggestions:
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.
- Install Supermetrics for Google Drive.
- When the Supermetrics sidebar opens, choose your data source to log in to.
- 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.
- 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.
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.
Fetches data from a given URL in .csv (comma-separated value) or .tsv (tab-separated value) file format.
Further reading: How to use the IMPORTDATA function to display your data.
Imports an RSS or ATOM XML feed.
IMPORTFEED(url, query, headers, num_items)
Imports data from a table or list within an .html page.
IMPORTHTML(url, query, index)
Further reading: How to use the IMPORTHTML function to display your data.
Imports a range of cells from a specified spreadsheet.
Imports data from any of various structured data types including .xml, .html, .csv, .tsv, and RSS and ATOM XML feeds.
- How to use the IMPORTXML function to display your data.
- How to use the IMPORTXML function to create a weather widget.
Using the built-in finance function GOOGLEFINANCE, you can pull back market data.
Fetches current or historical securities information from Google Finance.
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.
TODAY() and MINUS(value1, value2)
Further reading: How to use the TODAY and MINUS functions to create a countdown timer.
Scripting offers an easier way to write large spreadsheet formulas and functions.
Further reading: Google’s tutorials for using Google Apps Scripts with Google Sheets.
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.
Report Builder for Adobe Analytics
Report Builder is an Excel plugin that enables you to import your Adobe Analytics data into Excel.