Benard Kiplangat Ever wanted to connect your Google sheets data into Power BI but failed? Perhaps you’ve tried some hacks on the internet that didn’t work. Or maybe you just want to try the new Power BI Google Sheets connector. Either way, there must be some effective options to get you started. So, how can you load your data in Google Sheets into Microsoft Power BI?
The old techniques for connecting Microsoft Power BI with Google Sheets no longer work, so we need to be creative on this. And there’s also a native connector in Power BI that’s still in beta — so it isn’t stable yet.
That’s why we created three simple steps for you to connect your Google sheets data to Power BI. Let’s dive right in!
Part one: Using Google Sheets data connector in Power BI (Beta)
A few months ago, Google Sheets was missing from the list of native connectors in Power BI, which included Excel files, BigQuery, Google Analytics, and more!
Now, it is available (good news), but still under development (bad news). Nonetheless, you can try it out today.
Note: This feature is currently available only on the desktop app
Since December 2015, when Microsoft introduced the Get Data experience, it has evolved greatly into a simple, clean user interface for adding new data sources.
Now, you can search and connect with Google Sheets directly without browsing the hundreds of data sources available in Power BI right on the Get Data window.
Steps Power BI to connect Google Sheets
To use the Power BI Google Sheets connector, follow these steps:
- Click the Home ribbon on your Power BI Desktop app, and click Get Data. On the dropdown menu with the common data sources, click Other at the end of the list.
- Then, on the search bar, search for Google Sheets.
Here are the search results for Google Sheets. Select it, and click Connect!
- Once you click connect, you’ll get a new screen with a form field to feed your Google Sheets URL. So, go to Google Sheets and get your link. Copy it and paste it to the input field.
Note: You should sign in to your Google account to connect your Google Sheets!
- After pasting your link, click Sign In to continue.
- After hitting the Sign-in button, a Google dialog will appear, prompting you to continue to Power BI Desktop. Approve the connection and return to Power BI. You’ll find a screen like this one:
- Click Connect to finalize the connection.
- On the new window, find all the available data from your Google Sheets. Then, choose the tables you need to import to Power BI. If you want to structure your data, you can hit Transform data to go to Power Query or load it directly in Power BI Desktop by hitting Load.
And that’s it! Your data is now available in Power BI.
Part 2: Setting up Google Sheets for Power BI web connector
Before connecting Google Sheets through the web connector, you need to publish your Google Sheet to the web. Before we can do it, let’s first check out some tips.
Note:
- Sharing is disabled for untitled files. Be sure to name all your Sheets.
- Your edits are not real-time. Republish to make your changes to reflect instantly.
- Publishing your Google Sheets makes them accessible to anyone on the web. Don’t share sensitive info!
Steps to publish your Google Sheets files to the web
In Google Sheets, open the file you want to use in Power BI. Then click File, then Share, and finally — Publish to web.
On Publish to Web window, click the Published content and settings to view more sheet controls.
You can select the entire document to include all the sheets in your spreadsheets or select the individual sheets you want to publish.
You can check off automatic republishing if you would not like your changes to be published every time you make them.
To finish, hit Publish.
Finally, copy the link to your spreadsheets, and let’s go back to Power BI, where we will follow the steps in Part 4.
Note:
- This connector is currently not supported on Power Query Online.
- You should hit refresh in Power BI every time you make changes to your Google Sheets for the changes to reflect.
- It doesn’t support connecting with shared drives.
- Because it uses a different resource path for every Google Sheet URL, you will have to keep authenticating it for every new connection, which can be burdensome.
Part 3: The easiest — using Dataslayer API Query Manager
One of the most efficient ways to connect your Google Sheets data to Power BI is to use Dataslayer API Query. If the word API scared you, calm down. Our user interface is so intuitive, and you can connect your Google Sheets data in minutes. That should be interesting. Right? Right.
So, let’s now get down to business.
Getting started with Dataslayer’s API Query Manager
- Log in or sign up to Dataslayer and select API Query Manager on the welcome screen.
- Connect Google Sheets as your data source if you haven’t yet. It should take just seconds.
- If you had not previously used Dataslayer with Google sheets, here are a few quick steps for you to set it up:
- On Google Sheets, tap on Extensions in the menu bar and click on Get Add-ons.
- Finally, hit the search icon and search for Dataslayer.
- On the search list, select Dataslayer and click Install.
Guide to getting started:
2. After setting up your data source like Google Sheets, select Power BI on the BI tool format and click View Data.
3. Finally, copy the URL, and your marketing data will be ready to be loaded into your Power BI platform!
Now, let’s get to the final step.
Part 4: Getting your Google Sheets data in Power BI
Click the Home ribbon in your Power BI Desktop app, then Get Data. On the dropdown menu with common data sources, Select Web and click connect.
If you didn’t see it, or you opened Get Data from elsewhere, select Web from the list of All or Other data sources on the Get Data dialog box, and click Connect.
On the Web dialog box, paste your published Google Sheets URL or the URL from Dataslayer. Then select all the tables that you want to connect to Power BI on the Navigator dialog.
Once you have selected the tables if you need to clean up your data before loading, click Transform Data. Otherwise, click Load.
And there you have it! Your data is now available in Power BI and straight from your Google Sheets!
Conclusion
Connecting Google Sheets to Microsoft Power BI can be a great hassle, yet it shouldn’t be. While it may still seem daunting, we hope that we shared all the resources that will help you get your data to Power BI.
When the native connector is released officially, maybe then, it will be easier to do it without getting far from the Power BI Desktop app. However, as we’ve seen, it also comes with considerate drawbacks, and it may take a couple of years for it to pick up powerfully.
Therefore, a viable option to use is a tool like Dataslayer API Query Manager, which will help you to connect and maintain data freshness for your reports in Google Sheets. So, try today and see how much it can help you.
