How to connect Google Sheets to Google Data Studio

In this article, we are going to learn how to connect Google Sheets to Google Data Studio. We will explain how to correctly set up Google Sheets, before going on to explore how GDS imports information into a report.

There are two main scenarios when connecting Google Sheets to GDS may be useful:

1. When there is no connector available to pull in data directly from your platform.  An example of this might be offline sales.
2. When only paid connectors are available, for example, Supermetrics which is a paid connector for platforms like Facebook.  Using Google Sheets allows you to export and import data, allowing you to get the data into GDS for free, but it’s a more involved process.

We recommend reviewing the previous articles in our ‘how to’ series to give you a basic understanding of Google Data Studio.

• How to: Navigating Google Data Studio’s Toolbars
• How to: Connect Google Analytics to Google Data Studio
• How to: Make your Dashboard Interactive with Filters
• How to: Time Series Charts, Bar Charts and Pie Charts in Google Data Studio
• How to: Utilise Advanced Data Visualization

This article consists of 2 sections:

• Preparing Data in Google Sheets
• Connecting Google Sheets to Google Data Studio

Here are 15 steps to follow in order to create a report with GDS using imported data from Google Sheets.

Preparing Data in Google Sheets

1) It is possible to power a GDS report with a range of different data connectors, one of which is Google sheets – Google’s version of Excel.

2) In order to create a Google Sheet, click on this link and sign into your Google account.

3) Google Sheets is a web-based application which allows users to create and edit data stored in a spreadsheet, which is shared live online.

To ensure that GDS imports the information from Google sheets correctly, it is necessary to display the data in a specific format. Below you will find an example of a Google Sheets table showing results from a merchandise store.

The data in Google Sheets needs to be stored in a table format. In the first row of the table, each column needs to contain a header.

4) In this example, we have used the following headers; date, product type, country the product was sold in, average price, product revenue and tax rate. We are working with 4 main types of data: date, text, currency value, and percentage. It’s necessary to check that each type of data is correctly formatted.

Date example:

• The date column must be formatted correctly by selecting the ‘format’ drop-down menu and then selecting ‘number’ and then ‘date’. Dates must be in the following format: dd/mm/yyyy.

Text example:

• All data displayed as text is aligned to the left.

Currency example:

• Data displayed as currency needs to be formatted correctly by selecting the column, clicking on the ‘format’ drop-down menu and selecting ‘currencies’.

Percentage example:

• Data displayed as a percentage must also be formatted correctly by following the same steps as above and selecting ‘percentage’.

5) It is important to note that Google Sheets Connector can only connect to one sheet at a time. So, it is necessary to ensure that all the information that needs to be exported to GDS is displayed in a single sheet.

6) GDS cannot import data that is displayed in charts or graphs so it is necessary to ensure that all the information in Google Sheets is presented in a table format.

7) On the table in Google Sheets, a ‘total’ row displaying the sum of each column’s data cannot be included because this will result in double counting. The sum of each column can be added in GDS if it is required.

Connecting Google Sheets to Google Data Studio

8) In order to connect Google Sheets to GDS, it is necessary to create a new data source by selecting the ‘+’ in the bottom right-hand corner of the page in GDS.

The following screen will then appear:

9) Next, select the Google Sheets data connector and authorize the account.

10) Once GDS is able to access the Google Drive folder where the Google Sheet is stored, it can connect to the sheet in one of three ways:

• Selecting the relevant Google Sheets from a list of saved ones
• The URL of the spreadsheet
• Google Drive explorer

11) Select the option that best suits you. It’s important to make sure that the ‘Use first row headers’ checkbox is selected. Finally, click connect.

12)There are two important things to check before connecting the Google Sheet with GDS:

• Has GDS identified the correct ‘type’ of data for each column in the table on Google Sheets? GDS highlights columns that contain numerical values in blue, and ones that contain texts in green. If GDS doesn’t give you the option to select the data type, this indicates that the data in Google Sheets isn’t in the correct format.
• Should GDS ‘sum’, ‘average’ or ‘count’ the values in the columns that require these functions?

13) When you select a data visualization from the drop-down menu, you will see from the data tab that the dimensions and metrics are now available to use in your report. Add in a table to confirm that GDS is displaying the correct information with the correct data ‘types’ from Google Sheets.

14) It is possible to build a report by following the same steps outlined in our previous articles. To see a preview of the report, select ‘view’ in the top right-hand corner of the page.

15) Finally, in order to edit any information in the GDS report, amend the Google Sheets accordingly and select the refresh button at the top of the page.

We hope this article was helpful and has assisted you in improving your knowledge in Google data studio. Don’t miss the next article in our ‘how to’ series guide.