5. Web Data, APIs and Google Sheets
5.5 Google Sheets Connector
Start with a problem
Rani's team types daily order targets in a Google Sheet, with dates across the columns. The report must compare actual orders with those targets.
Explain simply
The Google Sheets connector signs in with a Google account and lists the tabs. Then clean as usual: promote headers, Unpivot the date columns, set types with the Indian locale.
Show the data
Targets tab (same layout as 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: 3 rows × 3 columns. This table uses columns: City, Date, Target Orders. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: web_daily_targets_sheet.csv, visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
- Copy the sheet URL (
https://docs.google.com/spreadsheets/d/<id>/edit). - Get data › More… › Online Services › Google Sheets › paste › sign in › Connect.
-
Tick Targets › Transform Data › promote headers.
Your result should look like this:
3 rows × 3 columns -
Select City › Transform › Unpivot Other Columns › rename Attribute, then Date, Value, then Target Orders.
Your result should look like this:
City Date Target Orders Pune 04-05-2026 1 Pune 05-05-2026 2 Nashik 04-05-2026 1 Nashik 05-05-2026 1 Kolhapur 04-05-2026 2 Kolhapur 05-05-2026 1 -
Date › Change Type › Using Locale › Date, English (India). Target Orders › Whole Number.
- Compare with delivered orders from the practice orders file on 04-05 and 05-05.
Show the result
Your result should look like this (fictional sample):
| City | Target orders | Delivered orders | Achievement |
|---|---|---|---|
| Pune | 3 | 1 (306) | 33.3% |
| Nashik | 2 | 2 (304, 307) | 100.0% |
| Kolhapur | 3 | 1 (305; 308 cancelled) | 33.3% |
| Total | 8 | 4 | 50.0% |
Explain what happened
Unpivot turned 2 date columns into rows, so targets can join to orders by City and Date. Delivered orders on those two days: 304, 305, 306, 307.
Explore further
- Connector features change; check current Microsoft documentation for Service refresh support.
Detailed reference for this lesson:
Some store teams keep daily targets in Google Sheets. Power BI Desktop has a Google Sheets connector.
Steps in Power BI
- In Google Sheets, copy the sheet URL from the browser (for example
https://docs.google.com/spreadsheets/d/<id>/edit). - In Power BI: Home › Get data › More… › Online Services › Google Sheets › Connect.
- Paste the URL › OK › Sign in with the Google account that can open the sheet › allow access › Connect.
- The Navigator lists the tabs (sheets) in the file. Tick Targets › Transform Data.
- Clean as usual: promote headers, unpivot month columns (Module 7.17), set types.
Example sheet Daily Targets (fictional): columns Date, City, Platform, Target Orders, typed by Rani's team for Pune, Mumbai, Nashik, Sambhaji Nagar, Kolhapur and Nagpur.
Limitations and refresh
Connector features and limitations change over time (for example, support in the Service and dataflows, shared drives, very large sheets). Check the current Microsoft documentation before relying on it for production. Also check that the Google account used for refresh keeps access to the file.
Ravindra Bagale's Tip
Friends, remember: a common problem is that the Google Sheets connector asks for sign-in again after publishing, and refresh fails. Test Service refresh early. If the connector's limitations don't suit you, consider the published CSV method or moving the data to SharePoint or Excel on OneDrive. Remember this rule.
Ravindra Bagale's Tip – मराठी
मित्रांनो, लक्षात ठेवा: एक common problem म्हणजे publish केल्यानंतर Google Sheets connector पुन्हा sign-in मागतो आणि refresh fail होतो. Service refresh लवकरच test करा. Connector च्या मर्यादा तुम्हाला जमत नसतील तर published CSV पद्धत किंवा data SharePoint किंवा OneDrive वरच्या Excel मध्ये हलवण्याचा विचार करा. हा नियम लक्षात ठेवा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, याद रखो: एक common problem यह है कि publish करने के बाद Google Sheets connector फिर से sign-in माँगता है और refresh fail हो जाता है. Service refresh जल्दी test करो. Connector की सीमाएँ तुम्हारे काम की न हों तो published CSV वाला तरीका या data को SharePoint या OneDrive वाले Excel में ले जाने पर विचार करो. यह नियम याद रखो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Dates read as 5 April | Default US locale on dd-mm-yyyy text | Change Type › Using Locale › English (India) |
| Each new day is a new column | Wide layout loaded as is | Unpivot Other Columns |
| Refresh asks to sign in again after publishing | Google credentials not set in the Service | Set credentials; test Service refresh early |
Practise independently
Practice task
What were the target and delivered orders for 05-05-2026 alone?
Hints
- Targets: 2 + 1 + 1. Delivered: 306, 307.
Solution
Target 4; delivered 2 (306 Pune, 307 Nashik; 308 was cancelled); achievement 50.0%.
Think like a professional
Ask the sheet owners to keep one stable tab and header row; your query depends on that layout.
Revision card