How To Have Information Appear And Update In 2 Tabs In Google Sheets

Importing live data into a Google Sheet or Excel spreadsheet might seem similar a daunting job, but it'southward easier than yous recall…

If you lot're reading this you probably know spreadsheets are a useful way to aggregate, tidy and analyse information. But what you probably don't know is that with some simple techniques and plugins, any humble spreadsheet can exist transformed into an incredibly powerful, automated, data-gathering car that tin relieve you or your team hours of manual input.

In this post nosotros look at four dissimilar ways to get most any information into a spreadsheet.

How to become information into a spreadsheet

1. Manual input

Await, aren't we supposed to exist looking at ways to import live data into a spreadsheet automatically?

Well, yes, but adding information into a spreadsheet by manus shouldn't be dismissed entirely. Although it might seem inefficient, in that location are scenarios where this is the best or only pick for getting information into a spreadsheet:

If yous're working with pocket-size set of data on a one-off footing
If your data doesn't update oftentimes
If the data sources you're working with don't permit data to be exported
As a quick proof of concept that could be automatic afterward

In addition, manually adding data to a spreadsheet tin exist made more efficient through outsourcing to a virtual assistant. Sites like Upwork, Freelancer.com and Fiverr get in quick and easy to find talented individuals who tin can assist with big or repetitive data entry tasks. But we'll save that for another mail service.

2. Utilise a tertiary-party information import tool

A number of services, add-ons and tools specialize in getting data from a multifariousness of sources into Google Sheets and Excel spreadsheets.

One of our favourites is Supermetrics - a flexible improver for Google Sheets and Excel that lets you easily pull in information from many dissimilar tools, with a particular focus on marketing data.

For each data source you connect to Supermetrics, you can access a comprehensive range of metrics, data and information to bring in to your spreadsheet, and it'due south fifty-fifty possible to schedule automatic importing of new data and then your spreadsheet always contains up-to-date information.

Data sources that Supermetrics supports include: Adobe Analytics, AdRoll, Google BigQuery, Google Search Console, Moz, Optimizely, Pinterest, SEMrush, and more.

Here's a quick overview of how to import data into Google Sheets using Supermetrics:

Install the Supermetrics addition in the One thousand Suite Market place
In a Google Sheet, select Add together-ons from the main carte du jour, then Supermetrics > Launch
Select a data source from the list of available sources and authorize it to share data with Supermetrics
Build a query using the options available then click Become Data to Table

import-data-google-sheets-supermetrics

Once you're happy with the query you've built, you can then schedule it to re-run weekly, daily or even hourly which will fetch new data and add it to your spreadsheet. You can as well have Supermetrics transport a snapshot of your updated spreadsheet to an email address.

refresh-email-supermetrics-spreadsheet-report-1

There are a range of similar plugins and add together-ons that have their own strengths. Some of the ones nosotros like include:

Integromat: allows for circuitous workflows, filtering and blending of data earlier information is sent to a Google Sheet or Excel spreadsheet. Data sources supported include JSON, CSV files, RSS and XML feeds, and hundreds of tertiary party APIs.
Zapier: A huge range of data sources (2000+) that can dispense or add information to a spreadsheet automatically
Google Analytics' Google Sheets add-on: Import detailed information from Google Analytics into a Google Sheet
Written report Builder (Excel): If you lot apply Adobe Analytics, Study Builder is a plugin that enables yous to excerpt your Analytics information from Adobe into Excel.

Turn spreadsheet data into shareable dashboards

3. Import Functions (Google Sheets)

An entire library of functions in Google Sheets allows you to achieve powerful results using simple formulae.

Google Sheets offers five different import functions (listed below) that allow it to pull data into your spreadsheet from a multifariousness 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 information 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 brandish CSV data here: Using Google Sheets' ImportData function

IMPORTHTML(url, query, index)
Imports data from a table or list within an HTML webpage.Come across more detail about using Google Sheets' IMPORTHTML function here: Using Google Sheets' ImportHTML office

IMPORTXML(url, xpath_query)
Imports data from whatever 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 hither: 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 tin easily scrape data from web pages, feeds and files. Besides, a built-in finance part enables yous to pull dorsum market data.

Other useful Google Sheets functions

Creating a Countdown Timer using Google Sheets
GOOGLEFINANCE function to brandish market information

4. Scripting (Google Sheets)

Scripting (or in non-technical terms - writing a short bit of custom code) can be 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 deject-based scripting linguistic communication that provides easy means to automate tasks beyond Google products and other services.

Google Apps scripts are extremely flexible, and can be automated using a diverseness of triggers, and although they need more technical noesis to write and set upwards, an active community is on mitt to offer guidance and help.

At Geckoboard, nosotros use Discourse to power our Customs we use Google Apps Scripts to fetch information from Soapbox and add together information technology to a spreadsheet, which nosotros and so visualize on a live dashboard to encounter how key metrics are performing.

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

Created a new Google Sheets Spreadsheet
Created a new script for our Spreadsheet (Tools > Script editor) and re-create 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 role in our script to call the endpoint identified on step iii and update cell A1 with the response
Finally, added a trigger to run the office myFunction every hr.
Used Google Sheets standard functions to identify the rows containing topics created Today and count them.
Used Geckoboard's spreadsheets integration to get the information onto a dashboard (more than about this in the department below).

Google offers a variety of resource to help you lot make the most of Google Sheets using Google Apps Scripts. You might find some of their examples useful to get your information into Google Sheets.

Beyond spreadsheets: visualizing and sharing your data

Desire 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 easily visualize and share this information using a dashboard.

excel-example-dashboard-geckoboard

Larn more about this Excel dashboard example here.

The example above contains a diversity of visualizations powered by information in a spreadsheet, which updates automatically to prove up-to-the-minute data.

Here's how it works

Create your spreadsheet in Google Sheets or Excel (importing data from via the steps mentioned earlier in this mail)
Sign upwards for a free Geckoboard account
Select 'Add together dashboard', then 'Add widget'
Pick the 'Spreadsheet' integration from the list of data sources.
Select your data and cull a visualization
Build out your dashboard by adding more visualizations

Watch this in action in the video below!