Introducing: the new Power Query Google Sheets connector!
Power Query currently offers over 160 connectors in Power BI Desktop, one of the richest connectivity ecosystems in the industry. We’re constantly looking to add more connectors and light up more connectivity scenarios for users, and often look to direct customer and user feedback to help prioritize investments to address the most important gaps in our platform. One such gap has been connectivity from Google Sheets to the Power Query ecosystem, specifically in Power BI Desktop.
A Google Sheets connector to Power BI Desktop is the #15th most upvoted idea, and #1st most upvoted connector-related idea on the Power BI Ideas forum. We are happy to announce that with the November release of Power BI Desktop, we are releasing a certified Google Sheets connector out-of-box in Power BI Desktop!
Use the Google Sheets connector in Power BI Desktop
To connect to Google Sheets from Power BI Desktop, you will need a Google account, and must have access to the Google Sheet that you are attempting to connect to. Shared drives are supported.
First, open Power BI Desktop and select “Google Sheets” from the “Get Data” experience.
Next, you’ll be prompted to enter a Google Sheet URL. Copy the URL to the Google Sheet you are trying to connect to from your browser address bar and paste it into the input field.
You will then be asked to “Sign In” to your Google Account. The connector supports the standard Google OAuth flow to connect to the Google Sheets API.
The Google sign-in experience opens in an external browser window. Sign in to your Google account, and authorize connecting to and allowing data to be shared with Power BI Desktop.
Once signed in, select “Connect” to continue.
If sign-in is successful, you will be brought to the Power Query Navigator experience, where you will be able to select a specific spreadsheet within the Google Sheet referenced by your provided link. You will then be able to Load the data into Power BI Desktop or Transform it within Power Query.
Limitations and Considerations
The Power Query Google Sheets connector is currently only available within Power BI Desktop experiences. Note that the connector uses a different ResourcePath for every Google Sheet URL provided. You’ll need to authenticate to new resource paths and URLs, but you might not need to sign into Google multiple times if the previous sessions remain active.
This release is the first step in enabling Google Sheets connectivity for all Power Query users. We are excited to continue to invest in this experience, and have the following improvement plans as of today.
- Availability in Power Query Online (Dataflows) experiences – by mid-2022
- Additional forms of authentication (e.g. Google Service Accounts) – in consideration, with no timeline
- An experience to “Browse” your Google Drive to select a specific Google Sheet to connect to – in consideration, with no timeline
- Various performance improvements – under investigation, with no timeline
If you would like to see any of these features released, please share your feedback through comments below or through Power BI Ideas. If there is a feature not listed above that you are interested in, please share that feedback with us as well.
We are super excited to hear your feedback after using this connector! Feel free to leave a comment below with your thoughts.