No need to mess around with APIs or random connectors, use Google Sheets in your Data Studio reports to help you to start visualizing your data quickly.

Google Data Studio offers a free Google Sheets Connector, which creates a live connection between your data and dashboard. If you ever update the data within your sheet, one click on the refresh button will update your entire Data Studio Dashboard. It’s simple, reliable, and effective.

Check out the rest of this post for a complete guide to using Google Sheets with Data Studio.

Why Use Google Sheets As A Data Source

At the time of writing this, there are 253 different connectors in Google Data Studio. 18 are offered by Google, while the other 235 come from partners – the majority of which require paid accounts. Google Sheets is a straight forward and free way to easily connect your data with Data Studio.

The Major Google Sheets Benefits

  1. They are both Google Products
    • Google makes connecting to its products easy – including Sheets, Analytics, YouTube, and Google Ads.
    • These connectors are also free and reliable, which isn’t always the case with other connectors.
  2. You don’t need to be technical
    • Sheets makes it easy to see your data, so you can know exactly what’s going into your reports.
    • APIs and Sheets Add-Ons are not required to get started.
  3. You can clean up and customize the data first
    • Combine data, remove data, and adjust data to makes sure the right data is going into your report.
    • Data Blending is possible in Data Studio, but you can avoid a lot of headaches by handling this ahead of time.

The Major Drawbacks Of Using Sheets

  1. Sheets has size limitations
    • Some data sources hit the 5 million cell limit sooner than others, so Google Sheets is generally not recommended for those larger data sets.
  2. For live data, you’ll likely require an Add-On
    • Copying/pasting data works, but it requires your involvement to get the data updated.
    • For live data connections to platforms like Facebook, you’re going to need a Sheets Add-On.
    • Add-ons aren’t the end of the world, but it can add complexity to your setup.

Preparing Your Data Before Connecting

For this report, I’m going to be using 2019 State Population Data from Wikipedia. It’s a small dataset but is more than enough to demonstrate the best ways to leverage Google Sheets in your Data Studio Report.

If you’d like to follow along, make a copy of the data here.

Simplify The Structure Of Your Data

The image below contains the data from two different tables/sheets. While I could import each sheet as its own connection and then use the Data Blender to combine things, it will be significantly easier to combine the data before importing. The combined data can be found on the “State Populations” tab.

The two tables have been combined into the single table below. The order of the columns doesn’t matter, as long as the data in the rows are all lined up.

Format Your Sheets Data Correctly For Data Studio

  1. Clearly name your tab, so you can easily identify it in a list.
  2. Ensure columns are labeled correctly, with a unique column name in the first row.
  3. Make sure there are no images/charts in your data, and that no cells have been merged.
  4. Format the columns correctly.
    1. Highlight the column by clicking on the Letter above Row 1
    2. In the main menu, click Format > Number > (Number, Date, Time, etc)
    3. Choose the correct format for each of your columns, any text columns can remain as ‘Plain Text.’

Connecting Google Sheets To Data Studio

How To Connect Google Sheets To Data Studio

With your Google Sheet formatted correctly, we can now move onto the Data Studio side of things. This section will walk you through the complete process of adding your Google Sheet’s Data as a data source in your report.

Using The Google Sheets Connector

Step 1: Go to https://datastudio.google.com/ and click “New”

On the Data Studio Website, you can click to create a blank report. This is also where you’ll go to access existing reports you created, and where you can find a collection of templates that you can copy.

*If you have an existing dashboard and are looking to add a new source, go to Resource > Manage Added Data Source > Add A New Data Source, and then follow the remaining instructions below.

Step 2: Select the Google Sheets Connector

The connector is the functionality that allows the Google Sheets Data to pass into your Data Studio Report.

Step 3: Navigate to the Google Sheet you’re connecting

If you created the Google Sheet in the same account you’re building the report in, you’ll most likely find it at the top of the second column. Use the Owned By Me and Shared With Me options to narrow your search down, or you can just skip this step and search for your file – which can be seen where I typed ‘population’ above.

Step 4: Adjust the settings if needed

In most cases, the default options will work perfectly. The first option is if your column headers are not in the first row, and the second option is if you’d like to exclude hidden/filtered data. You can also select a specific range of data, but in most scenarios, you can skip this step.

Step 5: Click “Add” to connect to the data source

Clicking the Add Button will add this data source to your report. There are a few more steps below to get the dataset 100% usable, but it’s now officially connected.

Configure The Data Source

Now that the data is connected, we want to make sure that Data Studio is identifying the data correctly. Meaning we want to make sure it knows what columns are text, numbers, currency, dates, etc.

In the main menu, go to Resource > Manage Added Data Sources

And click “Edit” to the right of the new data source.

In the top left corner, click on the file name to change it to something more useful. Right now it’s using the naming conventions from the sheet. This can work in some cases, but I find it best practice to provide it a more clear name. I’ve changed mine to “GS – Population By State,” so I clearly know what data is on my Google Sheet.

The next step is to make sure the data is tagged correctly. We want to make sure that the column name on the left is lined up with the data type on the right. Abv = text, Division=text, Population = Number, etc.

Data Studio identified the State Dimension as text, which is technically correct. But if I’m going to use a mapping function, I want to make sure that Data Studio knows it’s actually a geographical region. (yes, I know region is another dimension we’re using, but I don’t choose how Google names things on their backend)

Double-check your data to make sure everything is aligned. For any money/dollar amounts change it to currency, and change any dates to YYYYMMDD. This will help avoid headaches later on.

Once you’ve reviewed your data, click “Done” in the top right corner. Now we can start on the report.

Working Inside Of Data Studio

Click “Add a chart” at the top of your screen to add a visualization/chart to your dashboard. You’ll see a drop-down list appear, where you can select the specific type of chart you’re looking for.

Navigating The Reports

Click on a visual, and you should see these items on the right side of your screen.

  1. Click to change the data source
  2. If your data has dates, add that here
  3. Change the chart’s dimension(s)
  4. Change the charts metric(s)
  5. List of metrics/dimensions, you can drag and drop these into the data menu

If you click to the left of the item (on the ABC or SUM) – you can rename the field.

Visualizing The Data

In this section, I’m going to work through a few different ways to visualize the data from your Google Sheet. As mentioned above, you’ll be clicking “Add a chart” for each of these visuals, and then adjusting the data source to show the correct metrics/dimensions you’re looking for.

Score Cards:

Score Cards are a great way to show a single metric. This is great for a business KPI like total transactions or total revenue, or in our case total population. When you have dates in your data set, you can show a date comparison field inside of these visuals, however, our population data doesn’t contain dates.

  • Chart Type: Chart > Scorecard
  • Metric(s): Population 2019
  • Dimension(s): none

Tables:

Tables help to organize data structurally. By default, Data Studio sorted our population data by the greatest population, which wasn’t originally apparent in our Google Sheet. We also could have put in Region or Division, and it would have automatically combined the population for each of those.

  • Chart Type: Chart > Scorecard
  • Metric(s): SUM Population 2019
  • Dimension(s): none

  • Chart Type: Chart > Table
  • Metric(s): Population 2019
  • Dimension(s): State

Pie Charts:

Pie charts are a great way to visually see the makeup of the data, such as what % of the US Population is coming from California. It helps add perspective to the data in understanding the breakout.

  • Chart Type: Chart > Pie
  • Metric(s): Population 2019
  • Dimension(s): State

Bar Charts:

Bar charts help to visually compare metrics across dimensions. Meaning I can easily see the population in one region compared to another, and the visual representation helps to understand the relationship between the two.

  • Chart Type: Chart > Bar
  • Metric(s): Population 2019
  • Dimension(s): Region

Map Charts:

When working with geographic data, it’s hard to beat a map chart when visualizing the data. It clearly shows the top regions based on any metric you select. While it’s normal for geographic data to follow the populations, visualizing the data in this format can provide surprising results.

  • Chart Type: Chart > Geo Map
  • Metric(s): Population 2019
  • Dimension(s): State
See the final report here – State Population Report

Export Data Studio To Google Sheets

Coming full circle, once you have data Inside of your Data Studio Reports, how can you push that data back into Google Sheets. This is great when you want to further analyze a smaller dataset or export specific data based on your filters set in the report.

  1. In “View Mode,” click the 3 dots in the top right corner of the visual you’re looking to export.
    • Creating a table with all of the dimensions/metrics you need is recommended.
  2. Click “Export To Sheets” and your data appear in a new sheet shortly.

What’s Next

This article just scratched the surface of what Google Data Studio is able to offer in terms of visualizations, connecting to different data sources, and making the most of your data/analysis. Let me know if I missed anything or if you have any questions, and thank you for reading!