Fixing Google Sheets and Power BI Dataflows: The Transition to the Native Connector
Recently, I encountered a frustrating issue with Power BI Dataflows that I know others must have experienced too. Until early January 2025, I was importing data from Google Sheets into Power BI by publishing the sheet to the web and selecting the Excel option. It worked like a charm — until it didn’t. Suddenly, this method stopped working for all my dataflows, leaving me scrambling for a solution.
It started with this error:
When i looked into the dataflow, this was the error that as displayed:
After some trial and error, I found a that the current method that I was using (publish to the Web) was not working anymore for some reason and whatever I tried, I could not get it working.
Luckily I found a better, more secure way to connect Google Sheets to Power BI using the native Google Sheets connector. If you’ve run into the same problem, here’s how I solved it and how you can too.
The Old Way: Publish to the Web (Excel Option)
Previously, I used the “Publish to the web” feature in Google Sheets, generating a URL with an Excel output option. Here’s an example of what the query looked like in the advanced editor:
Source = Excel.Workbook(Web.Contents("https://docs.google.com/spreadsheets/d/e/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/pub?output=xlsx"), null, true)
This worked well for years, but when it broke, I needed to act fast to restore my dataflows. Publishing to the web is also not the most secure method, so perhaps it was time to move on anyway.
The Solution: Using the Native Google Sheets Connector
Power BI’s native Google Sheets connector turned out to be the best solution. It’s more secure, doesn’t rely on public links, and integrates directly using Google account credentials. Here’s how I transitioned my dataflows to use this connector:
Step 1: Add the Google Sheets Connector
- Open your Power BI Dataflow or Power BI Desktop.
- Choose Get Data and search for “Google Sheets.”
- Paste the URL of your Google Sheet (e.g.,
https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit?gid=0
).
- Sign in with your Google account when prompted.
- Select the desired worksheet and load the data.
This creates a new query with syntax like this in the advanced editor:
Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/xxxxxxxxxx/edit?gid=0#gid=0"),
#"Navigation" = Source{[name = "Sheet1", ItemKind = "Table"]}[Data]
Step 2: Replace the Old Source in Existing Dataflows
If you’re like me and want to avoid re-creating all your transformations, here’s the trick:
- Open the advanced editor for the new query created by the Google Sheets connector.
- Copy the lines defining the new source: (don’t forget the comma behind)
Source = GoogleSheets.Contents("<Your Google Sheet URL>"), #"Navigation" = Source{[name = "Sheet1", ItemKind = "Table"]}[Data],
- Open the advanced editor for your original query.
- Replace the old
Source
line (usingExcel.Workbook
) with the newGoogleSheets.Contents
source. - Click OK, and your original transformations should work seamlessly.
Example:
Old Source:
Updated:
Step 3: Verify and Refresh
After updating the query, verify that the data loads correctly and refresh your dataflow to ensure everything works as expected.
Why This Works Better
- Security: The native connector uses OAuth for authentication, making it far more secure than public web links.
- Reliability: The connector doesn’t rely on “Publish to the web,” which may break without warning (as we’ve seen).
- Ease of Maintenance: If the Google Sheet changes, the connector handles it smoothly without requiring manual adjustments to the query.
Final Thoughts
While the old method served its purpose, the native Google Sheets connector is a more robust and secure way to integrate Google Sheets with Power BI. Transitioning to this method wasn’t just a fix — it was an upgrade.
If you’ve been affected by the same issue, I hope this guide helps you restore your dataflows quickly. As always, feel free to share your experiences or ask questions in the comments. Let’s keep learning and solving problems together!
Who are we?
AccessOrange is a leading Microsoft Partner that specializes in Microsoft 365, Dynamics 365 and Azure to help our customers work smarter.
If you need help with building PowerBI reports, Microsoft Fabric Datalakes or related, leave your contact information below, and we will contact you as soon as possible.
"*" indicates required fields
Leave a Reply
Want to join the discussion?Feel free to contribute!