Case Study: Connecting Google Sheets to Power BI

Context

The client’s team was working on Google Sheets for budgets, trackers, and project logs due to its real-time collaboration and cloud-based nature.

Unlike traditional files, their data was already centralized and continuously updated by multiple users — making it an ideal source for live reporting in Power BI.

Why Google Sheets Over Excel

• Cloud-based (No dependency on local systems): Data is stored online — no need for any machine to stay ON.

• No Gateway Required: Direct connection to Power BI Service works seamlessly.

• Real-time Collaboration: Multiple users can update simultaneously without version conflicts.

• Always Up-to-Date Data Source: Changes reflect instantly in dashboards after refresh.

Why Excel Created Challenges

When using Microsoft Excel as a source:

• Requires Gateway setup for automatic refresh

• The system hosting the file must remain ON

• Higher chances of file version issues

• More maintenance overhead

The Smart Fix: Web Connector + Published Link

We used Google Sheets’ Publish to Web option to generate a stable CSV link, which Power BI could read directly.

Step-by-Step Process to Connect to Google Sheets

Step 1 – Prepare the Google Sheet

• Ensure the sheet is cleaned and well-structured (headers in first row, no merged cells).

• Go to File → Share → Publish to Web.
  

  

• Publish as CSV for the specific sheet/tab.

Step 2 – Get the CSV Link

• Google generates a link ending with &output=csv.

• Copy this link.
  

  

Step 3 – Connect in Power BI

• In Power BI → Get Data → Web.
  

  

• Paste the CSV link.
  

  

• Anonymous →Connect
  

  

Step 4 – Transform Data

• Power BI loads the sheet as a table.
  

  

  
  

• Save as a query like Z Source
  

  

• Reference this Z Source to build multiple desired queries.
  

  

• Apply transformations as needed.
  

  

• Once you have the desired queries click - Close & Apply.
  

  

Step 5 – Refresh Behaviour

• Since it’s a published CSV link, Power BI refreshes whenever the Google Sheet is updated.

• No more manual downloads.

  
  

Takeaway

Google Sheets and Power BI work perfectly together when set up the right way:

• Publish the sheet as a CSV (you can also choose Excel).

• Use the Web connector in Power BI.

• Organize with reference queries.

This gives your team a collaborative, always-live dashboard — without messy downloads or copy-paste.