How to connect your Google Sheets data to Power BI in 3 simple steps

You don’t necessarily need a SQL server to handle data storage when you’re loading a small volume of data to Power BI. 

Why deal with the annoyance of Power BI’s built-in direct query options when you can connect your data from Google Sheets directly to Power BI? 

To top it off, you’ll get refreshed data using Supermetrics for Google Sheets from the get-go. Win-win. ?

Lucky for you, our friends at Refyne put together a step-by-step guide to get you set up.

Google Sheets is great for everyday analytics but has some limitations. Its visualization options and data storage capabilities are limited, blending data from different sources can be a hassle, and its deeper analytics capabilities pale in comparison to more powerful tools.

Because of these limitations, many companies add additional software to the mix for their analysis and visualization purposes like Tableau, Google Data Studio, and Power BI.

In this case, we’ll use Power BI because quite a few companies are consolidating their marketing (SEO, paid search, paid social) & business data (CRM, ERP) in Power BI to get a more holistic representation of their data. Power BI does offer some pre-built connectors to the interface. However, these are scarce and quite limited how they can be used.

This means we’re usually forced to either build our own connectors, use Excel flat files, or pull data from a database.

Most marketers are not familiar with building their own API connections or configuring a SQL server. So they usually turn to Excel flat files, which poses new challenges for data freshness and manual updates.

An option for the less tech-savvy marketers is to use Supermetrics for Google Sheets which comes with a very user-friendly UI and extract this data to Power BI using the ‘Get Data from Web’ function in Power BI.

1. Get your data to Google Sheets with Supermetrics

2. Set up Google Sheets for Power BI

3. Set up your data in Power BI

How to get fresh data from Google Sheets to Power BI

1. Get your data to Google Sheets with Supermetrics

If you’ve ever used Supermetrics for Google Sheets, you probably already know how to perform this step, but here is a quick overview for those new to the process.

How to install the Supermetrics for Google Sheets connector

1. Once you are in Google Sheets, navigate to the ‘Add-ons’ tab
2. Click ‘Get Add-ons’
3. Search for Supermetrics
4. Click on ‘Supermetrics’ and ‘Install’

How to get your data to Google Sheets

1. Go to ‘Add-ons’ — Supermetrics should now appear
2. Hover on Supermetrics and click on ‘Launch Sidebar’
3. Select the data source you want to connect. For this example, we’ll be using Google Search Console
4. Follow the authentication process
5. Select the dates, metrics and, dimensions you want to pull to your sheet
6. Click on  ‘Get data’

How to refresh your data

No one likes to manually refresh their data, even if it’s quite simple to do. So if you want to get your year-to-date compared to the previous year’s data, for example, follow these five steps to set up automatic refreshes.

1. Set ‘Select dates’ to ‘Year to date’
2. Compare to: ‘Same dates a year ago’

3. Click the ‘Schedule’ tab up top
4. Click ‘Add trigger’

5. Select the option ‘Refresh Daily’, or however often you want your data refreshed

2. Set up Google Sheets for Power BI

Before you can fetch data from your reports in Google Sheets, you need to do a few modifications to allow Power BI to interact with it.

1. Name your file as sharing is not enabled for untitled files
2. Click the ‘Change’ button in the left corner that pops up

3. Change the option to ‘Anyone on the internet with this link can view’

4. Go to the file options and select ‘Publish’

5. Keep the URL generated for the web page

3. Set up your data in Power BI

1. Once in Power BI, click on the ‘Home’ tab and select ‘Web’

2. A small window will appear on your screen. Paste the URL for the published web page that we created and click ‘OK’

3. Then, you’ll see a new screen where you can connect your data to Power BI

4. Once you’re done, you should be able to see this view ?

5. Now our data can be transformed ?

You can, for example, change the currently selected column headers and remove columns that are not part of your table. These few changes only take a couple of minutes, and now you’re ready to integrate your Google Sheets data directly into your Power BI reports.

That’s it!

Now you’re ready to get the best of both worlds with your data automatically refreshed in Google Sheets and great visualization capabilities in Power BI. Over to you?

P.S. Pulling larger amounts of data to Power BI?

Connecting Google Sheets to Power BI works great for smaller amounts of data. So what do you do if you have larger amounts of data to bring into Power BI?

Supermetrics for Power BI to the rescue.

Extract everything you need in a custom output format that’s compatible with Power BI’s ‘Get Data from Web’ connector.