5. Web Data, APIs and Google Sheets
5.6 Alternative: Publish a Google Sheet as CSV and Use the Web Connector
Start with a problem
The Google Sheets connector keeps asking Zoya to sign in. The targets are practice data, not sensitive, so she tries a published CSV link.
Explain simply
File › Share › Publish to web as CSV gives a public link that Get data › Web reads anonymously. Anyone with the link can read it, so use it only for non-sensitive data.
Show the data
Published CSV (web_daily_targets_sheet.csv):
| City | 04-05-2026 | 05-05-2026 |
|---|---|---|
| Pune | 1 | 2 |
| Nashik | 1 | 1 |
| Kolhapur | 2 | 1 |
About this data: This table uses columns: City, 04-05-2026, 05-05-2026. This table uses columns: Preview. This table uses columns: Date. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: web_daily_targets_sheet.csv (source: src/practice/).
Demonstrate
- Google Sheets › File › Share › Publish to web › tab Targets › Comma-separated values (.csv) › Publish › copy the link. (For practice, use the course-site link of web_daily_targets_sheet.csv.)
-
Desktop › Get data › Web › paste › Anonymous.
Your result should look like this:
Preview 3 rows; columns City, 04-05-2026, 05-05-2026 -
Promote headers › Unpivot Other Columns (as in 5.5).
-
Wrong way first: Change Type › Date (default locale).
Your result should look like this:
Date 04-05-2026 → 5 April 2026 (wrong in an en-US locale) -
Right way: delete that step › Using Locale › English (India).
Your result should look like this:
Date 04-05-2026 → 4 May 2026
Show the result
Your result should look like this (fictional sample):
| Method | Sign-in | Privacy | Total target orders |
|---|---|---|---|
| Google Sheets connector | Google account | Private | 8 |
| Published CSV + Web | None (Anonymous) | Public link | 8 |
Explain what happened
The Web connector read the published file like any CSV. The locale step decides whether 04-05-2026 means 4 May or 5 April.
Explore further
- No gateway is needed for a public URL.
Detailed reference for this lesson:
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 only 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
Friends, many students forget that a published CSV is read with the default locale, so dates and numbers can come in wrongly (for example 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. Try it once more, then move on.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students विसरतात की published CSV default locale ने वाचली जाते, त्यामुळे dates आणि numbers चुकीचे येऊ शकतात (उदाहरणार्थ dd/mm हे mm/dd म्हणून वाचलं जातं). Load केल्यावर Using Locale ने types set करा, header row फक्त एकदाच येते ना ते चेक करा, आणि लक्षात ठेवा की sheet edit केल्यानंतर published data update व्हायला काही मिनिटं लागू शकतात. पुन्हा एकदा करून बघा, मग पुढे जा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students भूल जाते हैं कि published CSV default locale से पढ़ी जाती है, तो dates और numbers गलत आ सकते हैं (जैसे dd/mm को mm/dd पढ़ लेना). Load करने के बाद Using Locale से types set करो, check करो कि header row सिर्फ़ एक बार आए, और याद रखो कि sheet edit करने के बाद published data update होने में कुछ मिनट लग सकते हैं. एक बार फिर करके देखो, फिर आगे बढ़ो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Targets land on the wrong dates | Default locale reads mm-dd | Using Locale English (India) |
| Customer data exposed | Publish to web used for sensitive data | Never publish sensitive data; use the connector |
| Link shows an HTML page | Copied the normal sheet URL | Copy the published CSV link |
Practise independently
Practice task
If the dates were read in a US locale, which target dates would the report show?
Hints
- mm-dd-yyyy.
Solution
04-05-2026 would become 5 April 2026, and 05-05-2026 stays 5 May 2026 (same digits either way). The 04-May targets (Pune 1, Nashik 1, Kolhapur 2 = 4) would sit on 5 April, a day with no orders, so Achievement for 04-May would show no target.
Think like a professional
Every time you parse dates from text, name the locale explicitly. It is a one-click habit that prevents silent errors.
Revision card
Interview card
Comparison card