Brand New Connector! Connect Power BI & Google Sheets
Contents
Microsoft recently (16th November 2021) announced that they have beta released a Google sheets connector for Power BI and Power Query.
This means that you can now connect to and import your business data from Google Sheets into Power BI for further analysis with the new Google Sheets connector.
This connector will make importing Google Sheets data into Power BI as easy as it is to import it into Google Data Studio (see here our comparison of Power BI and Google Data Studio) or SQL Server.
We cover connectors during our physical and online Power BI courses.
What Is A Connector In Power BI?
A Power BI connector allows you to automatically import data from a specific data source once you have set it up.
A connector also allows you to automatically update that data according to a set schedule.
It is possible to connect Power BI desktop to multiple connectors at the same time.
Power BI currently has hundreds of connectors, and the list is growing steadily. For the current list of connectors, see this Microsoft page.
Why Use A Connector?
Connectors save huge amounts of time if you are repeatedly exporting data from a data source into Power BI.
Other benefits of using a connector include:
- Changes are simple to make.
- Simplifies data governance.
- They are very useful when working with sensitive data.
- Makes data availability simpler and more self-contained, particularly if your source data updates regularly.
Connecting Power BI Desktop to Google Sheets
NOTE: To use the new Google Sheets connection, you must update to the November 2021 update for Power BI.
Log in to your Google Sheets account.
Open the Google Sheet for the file you would like to import into Power BI and copy the URL from the address bar.
Next, open the Power BI Desktop app.
On the Home tab, first, click Get Data.
Select More > Search for Google Sheets > Connect.
Paste the Google Sheets URL you copied from your browser.
Click the OK button.
Assuming this is the first time you have connected Power BI to Google sheets, you will be asked to sign in.
In your default browser, complete the sign-in procedure.
You should be able to connect after a successful sign.
After you have successfully connected, the Navigator dialogue box will open when you click Connect.
You should see all the worksheets in the Google Sheets file you are connected to available.
The next step is almost identical to importing data from Excel or SQL Server into Power BI.
Select the worksheets (in my example, Sheet1) from which you wish to export data and click Load.
Note if your data needs cleaning then it is best to first click Transform Data.
This opens the Power Query Editor in Power BI where you can easily carry out operations like renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, and so on.
Once you have completed that, you can then click Load.
Power BI Service & Data Scheduling
Once you’ve built your data model, you can publish and save it in Power BI service and schedule it to refresh automatically.
This means you do not need to manually refresh your data each time you log in.
Login to Power BI Service > My Workspace
Locate the Dataset and Click on the Schedule refresh icon.
Ignore the Gateway connection option as the dataset is on the cloud. You do not require the method to schedule refresh.
Expand Data source credentials, select Edit credentials to login into the Google account of the source data.
Next, Expand the Schedule refresh button and schedule it to refresh at your desired time and frequency.
Setting up a data refresh schedule means that the new Google Sheets connector works in Power BI Desktop and in Power BI service once the dataset has been published. This is a great feature for frequently used dashboards and reports.
Current Limitations
As a brand-new connector, this currently has a few limitations, although Microsoft is likely to resolve these in the coming releases of Power BI.
- Multiple connections aren’t handled centrally.
If you need to connect to numerous Google Sheets, you must sign in to each URL separately.
- Power Query online:
This connector isn’t currently accessible in the dataflows and Power Query online experiences.
- Google Drive folder:
You can’t obtain all the Sheets from a Google Drive folder path. You must use individual URL options.
Conclusion
This new connector has been one of the most requested improvements for Power BI.
If you use Microsoft Power BI and Google Sheets being able to connect it will directly save you a considerable amount of time. So give it a try.
An understanding of connectors will often be the basis of Power BI interview questions for entry-level and intermediate positions.
