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

Connecting Google Sheets To Data Studio
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.
- Click to change the data source
- If your data has dates, add that here
- Change the chart’s dimension(s)
- Change the charts metric(s)
- 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


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.

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