Ravindra BagaleCourses & study guides

5. Web Data, APIs and Google Sheets

5.5 Google Sheets Connector

By Last updated:

Some store teams keep daily targets in Google Sheets. Power BI Desktop has a Google Sheets connector.

Steps in Power BI

  1. In Google Sheets, copy the sheet URL from the browser (for example https://docs.google.com/spreadsheets/d/<id>/edit).
  2. In Power BI: Home › Get data › More… › Online Services › Google Sheets › Connect.
  3. Paste the URL › OK › Sign in with the Google account that can open the sheet › allow access › Connect.
  4. The Navigator lists the tabs (sheets) in the file. Tick Targets › Transform Data.
  5. 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, Solapur, Nashik, Sambhaji Nagar, Kolhapur and Nagpur.

Limitations and refresh

He bagha: connector features and limitations change over time (for example, support in the Service and dataflows, shared drives, khup 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.