5. Web Data, APIs and Google Sheets
5.6 Alternative: Publish a Google Sheet as CSV and Use the Web Connector
Steps
- In Google Sheets: File › Share › Publish to web.
- Choose the tab (Targets) and the format Comma-separated values (.csv) › Publish › copy the link.
- In Power BI: Get data › Web › paste the link › OK › Anonymous › Connect. Power Query reads it as a CSV file.
- Promote headers and set types (use Using Locale English (India) for dd-mm-yyyy dates).
Publish to web makes the data public
Anyone who has the link can read the sheet. Use this method fakt for non-sensitive data such as practice targets. Never use it for customer names, phone numbers or financial data.
| Method | Sign-in needed | Privacy | Refresh in Service |
|---|---|---|---|
| Google Sheets connector | Google account (OAuth) | Private to accounts with access | Supported per current docs (credentials required) |
| Published CSV + Web connector | None (Anonymous) | Public link | Works like any public URL; no gateway needed |
Ravindra Bagale's Tip
Mitrano, khup students forget that a published CSV is read with the default locale, so dates and numbers can come in wrongly (udaharan mhanje dd/mm read as mm/dd). Set types with Using Locale after loading, check that the header row appears only once, and remember that published data can take a few minutes to update after you edit the sheet. Punha ekda karun bagha, mag pudhe jaa.