How to Connect Google Sheets to Power BI

Power BI connects to Google Sheets in two ways: the native Google Sheets connector in Power BI Desktop (Google sign-in, private data) or the Web connector pointed at a sheet published as CSV (no account needed, but the data becomes public). The first one covers the vast majority of cases; the second is a fallback for read-only sharing. This guide walks through both, then covers scheduled refresh in the Power BI service, which is where most connections break.

Available in Power BI Desktop since 2022. It reads a private workbook with your Google account, without publishing anything.

1. Copy the full workbook URL from your browser, in the form https://docs.google.com/spreadsheets/d/…. The connector needs the complete URL, not a shortened share link.
2. In Power BI Desktop: Home → Get data → More… → Google Sheets.
3. Paste the URL, confirm, then sign in with the Google account that has access to the document (an authorization window opens; grant read access).
4. In the Navigator, tick the sheets you want to load. The connector exposes every tab in the workbook.
5. Click Transform Data rather than Load: in Power Query, check the header row (Use First Row as Headers) and the column types. Dates and decimal separators are the classic traps of a hand-maintained sheet.
6. Close & Apply: your data is in the model, ready for visuals.

Three things to watch

• The connector reads the whole workbook: no named ranges, no server-side filtering. Trimming happens in Power Query.
• The account you use must have access to the document at the time of every refresh, not just when you first connect.
• On large workbooks (tens of thousands of rows), loading is noticeably slower than from an Excel file: Google Sheets is not a database. See the last section.

Useful when the report must refresh without Google credentials, or to feed a read-only sheet to a third-party tool. The trade-off: published data is accessible to anyone who has the URL. Keep it for non-sensitive data.

1. In Google Sheets: File → Share → Publish to web.
2. Pick the tab to publish and the “Comma-separated values (.csv)” format, publish, and copy the generated URL.
3. In Power BI Desktop: Get data → Web, paste the URL.
4. When prompted for authentication, keep Anonymous.
5. Power Query receives a clean CSV: promote the headers, set the column types, load.

Good to know: Google regenerates the published CSV with a small delay (a few minutes after an edit), and publishing covers one tab per URL.

After publishing the report to app.powerbi.com, scheduled refresh fails until the service has its own credentials: the ones from Power BI Desktop do not travel with the file.

1. In the service: Semantic model settings (formerly “dataset”) → Data source credentials.
2. On the Google Sheets source: Edit credentials → OAuth2 → Sign in with the same Google account.
3. Then configure scheduled refresh: the source lives in the cloud, so no gateway is required.

With method 2 (anonymous Web connector), there are no credentials to enter: refresh works right away.

Common errors and their causes

Symptom	Usual cause
“Couldn’t authenticate” on refresh	OAuth2 credentials never re-entered in the service, or the account lost access to the document.
“Invalid reference” or source not found	Incomplete URL (shortened share link) or the document was moved/deleted on the Google side.
Columns shifted after a refresh	Someone inserted or renamed a column in the sheet: Power Query references header names. Lock the structure or handle renames in the query.
Numbers loaded as text	Decimal separator or regional format of the sheet: force the type in Power Query using the appropriate locale.

	Google Sheets connector	Web connector (published CSV)
Privacy	Private document, account-based access	Public to anyone with the URL
Service authentication	OAuth2, set up once	None
Scope	All tabs of the workbook	One tab per published URL
Best for	The standard: internal data	Already-public data, prototypes

If your volumes grow or the spreadsheet becomes the bottleneck of your reporting, the natural next step on the Google side is BigQuery (or an equivalent warehouse) fed properly, with Power BI or Looker Studio on top. And once the data is connected, our Power Query & DAX reference covers the essential functions (in French).