How to Use Google Data Studio with Google Sheets

Connecting a Google Sheet to Looker Studio (formerly known as Google Data Studio) transforms your static spreadsheet data into a dynamic, sharable, and interactive dashboard that updates automatically. This tutorial walks you through every step, starting with how to properly format your data in Google Sheets and ending with building and sharing your first real-time report.

Why Connect Google Sheets to Looker Studio?

While Google Sheets is fantastic for data collection and basic calculations, its charting capabilities can be limiting and manual. Creating weekly or monthly reports often means repeatedly copying data, creating charts, and sharing static files. Connecting your Sheet to Looker Studio solves these problems directly.

• Automation and Real-Time Data: Once connected, your Looker Studio dashboard will automatically update as you add or change data in your Google Sheet. Say goodbye to manually refreshing reports every Monday morning.

• Interactive Visualizations: Viewers of your dashboard can interact with the data directly. They can change date ranges, filter by campaign, or drill down into specifics without ever touching or risking changes to the original spreadsheet.

• Combine Multiple Data Sources: Looker Studio allows you to pull data from different sources into a single report. You can create a dashboard that shows your marketing spend from a Google Sheet alongside live website traffic data from Google Analytics.

• Secure and Easy Sharing: Share a link to a live dashboard instead of emailing a clunky PDF or spreadsheet file. You can control access with view-only or edit permissions, ensuring everyone is looking at the same up-to-date information.

Before You Connect: Prepping Your Google Sheet

Most connection errors happen because the spreadsheet isn’t structured in a way that Looker Studio can understand. Think of your Google Sheet less like a scratchpad and more like a simple database table. Following these rules will prevent nearly all common issues.

1. Use a Clear, Unbroken Header Row

The very first row of your sheet must contain the headers for your columns. These headers become the names of your "dimensions" and "metrics" in Looker Studio.

• Each column header must be unique (e.g., don't have two columns named "Date").

• Keep names clear and simple (e.g., 'Ad Spend' instead of '$$$ Spent on Ads This Month').

• Do not have any blank rows above your header row. The data table must start in cell A1.

2. Ensure Consistent Data Formatting

Looker Studio needs to correctly identify the type of data in each column. Mismatched formats can cause errors or prevent your data from appearing correctly.

• Dates: Ensure all dates in your date column are formatted as dates (e.g., 2024-05-20), not just text strings.

• Numbers and Currency: Make sure numerical columns (like Clicks, Sessions, or Spend) only contain numbers. Avoid adding text or currency symbols (e.g., use '150.75', not '$150.75' or '1,200 clicks'). You can format these as currency or numbers within Looker Studio itself.

• Geography: For geographical data, use standard names (e.g., "United States" or "California") for Looker Studio to correctly recognize them for mapping visualizations.

3. Remove Merged Cells and Blank Rows

Looker Studio reads your data row by row. Any formatting that disrupts this simple grid structure can cause problems.

• No Merged Cells: Never use merged cells within your data range. Every piece of data should live in its own unique cell.

• No Blank Rows: Remove any blank rows within your data set. Looker Studio might interpret a blank row as the end of your data file and stop importing anything below it.

Here’s an example of a well-formatted Google Sheet, ready for Looker Studio:

Connecting Google Sheets to Looker Studio: A Step-by-Step Guide

Once your sheet is clean and well-structured, the connection process is straightforward.

Step 1: Create a New Data Source in Looker Studio

Navigate to lookerstudio.google.com. In the top-left corner, click the Create button and select Data Source from the dropdown menu.

Step 2: Select the Google Sheets Connector

You will see a list of available data connectors. Google Sheets is usually one of the first options under "Google Connectors." Click on it.

Step 3: Authorize and Select Your Spreadsheet

You may be asked to authorize Looker Studio to access your Google Sheets. After granting permission, you'll see a list of your spreadsheets. Select the Google Sheet you want to connect to.

Step 4: Choose the Worksheet and Configure Options

Next, you’ll need to specify which tab (worksheet) within your spreadsheet you want to work with. You'll also see a few options:

• Use first row as headers: Always keep this checked if you followed the formatting tips above.

• Include hidden and filtered cells: Check this if you want Looker Studio to pull in all data, even if certain rows or columns are hidden in your Google Sheet.

• Optional range: You can specify a range (e.g., A1:F500) if you only want to use a portion of the worksheet. If left blank, it will include all the data on the sheet.

When you're ready, click the Connect button in the top-right corner.

Understanding Your New Data Source

After clicking "Connect," you're taken to the data source configuration screen. This is a critical step where you define how Looker Studio interprets each column from your sheet.

You'll see a list of all the headers from your spreadsheet. They are categorized into two types:

• Dimensions (Green Fields): These are your categorical data - the things you want to measure by. Examples include ‘Date’, ‘Campaign Name’, or ‘Source’.

• Metrics (Blue Fields): These are your quantitative numbers - the things you actually want to measure. Examples include ‘Impressions’, ‘Clicks’, or ‘Spend’.

Looker Studio does a good job of guessing, but you should double-check that each field is set correctly. Pay close attention to the Type column. If your ‘Date’ column is being read as ‘Text’ or your ‘Spend’ column is showing up as Text, you won’t be able to use them properly in charts. Simply click the dropdown menu for that field to change it to the correct type (e.g., Date, Number, Currency).

Once you are happy with the setup, click Create Report in the top-right.

Building Your First Dashboard: A Quick Example

Now that your data is connected, you’ll be taken to a blank canvas. Let's build a simple marketing performance dashboard using the sample data from earlier (Date, Campaign Name, Source, Impressions, Clicks, Spend).

1. Add Scorecards for Key Metrics

Scorecards are great for showing high-level totals.

• Go to Add a chart > Scorecard.

• Place it on your report canvas.

• In the Setup panel on the right, make sure the Metric is set to 'Spend'. Change the format to your desired Currency.

• Copy and paste this scorecard. On the new one, change the Metric to 'Clicks'. Now you have a simple Clicks card as well.

2. Create a Time Series Chart

Let's visualize clicks over time.

• Go to Add a chart > Time series chart.

• Make sure the Dimension is set to ‘Date’.

• Set the Metric to ‘Clicks’. You will now see a chart showing your daily clicks.

3. Build a Performance Table

A table is perfect for breaking down performance by campaign.

• Go to Add a chart > Table.

• For the Dimension, drag in 'Campaign Name' from the "Available Fields" list.

• For your Metrics, add 'Spend', 'Impressions', and 'Clicks'. Now you can see a performance summary for each campaign.

4. Add a Date Range Control

This is what makes your dashboard truly interactive.

• Go to Add a control > Select Date range control.

• Place it somewhere visible, like the top-right corner of your dashboard.

• Now, you or anyone you share the report with can select a date range (e.g., "Last 7 Days" or "This Month"), and all the charts on the page will update automatically.

Final Thoughts

By connecting Google Sheets to Looker Studio, you elevate your data from static rows into an automated analytics engine. This enables you to spend less time on tedious manual reporting and more time discovering business insights. Remember to keep your source data clean and structured, and you’ll have a reliable, self-updating dashboard at your fingertips.

Once you're comfortable with dashboards, the next step is often unifying data from multiple platforms, not just a single spreadsheet. That process used to require complex setups, but tools now make it much simpler. We built Graphed to be your AI data analyst — it connects directly to all your key sources like Google Analytics, Shopify, Facebook Ads, and Google Sheets, and lets you build dashboards and get answers just by asking questions using natural language. It’s the difference between building a dashboard yourself and having one built for you in seconds.