Connecting Power BI to Google Sheets – The New Connector

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.

 

copy the URL from the address bar

 

Next, open the Power BI Desktop app.

On the Home tab, first, click Get Data.

 

On the Home tab, first, click Get Data

 

Select More > Search for Google Sheets > Connect.

 

Select More Search for Google Sheets Connect

 

Paste the Google Sheets URL you copied from your browser. 

Click the OK button.

 

Paste the Google Sheets URL you copied from your browser

 

Assuming this is the first time you have connected Power BI to Google sheets, you will be asked to sign in.

 

you will be asked to sign in

 

In your default browser, complete the sign-in procedure.

 

complete the sign-in procedure

 

You should be able to connect after a successful sign. 

 

ble 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 Navigator dialogue box will open when you click Connect

 

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

 

Login to Power BI Service My Workspace

 

Locate the Dataset and Click on the Schedule refresh icon.

 

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.

 

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.

 

Articles On Power BI Visuals

Bullet Charts In Power BI

Scatter Charts In Power BI

Working With Maps In Power BI

Using Word Clouds In Power BI

Gantt Charts In Power BI

The Decomposition Tree Visual In Power BI

The Smart Narrative Visual In Power BI

About Ben Richardson

Ben is a director of Acuity Training. He writes about SQL, Power BI and Excel on a number of industry sites including SQLCentral, SQLshack and codingsight.