Connect a Google Sheet spreadsheet to Power BI as a data source

Update 9/29/21: A new Google Sheets connector is coming soon for Power BI. Keep an eye out for updates.

I recently connected Power BI directly to a spreadsheet in Google’s Excel alternative, Google Sheets. It’s important to connect to data without moving it when we can. The less we have to export/download, save, rename, move, massage, etc. the better.

Here’s how we’ll connect to a Google Sheet:

1. Get “no sign-in required”/anonymous share URL from Google Docs
2. Adjust URL for Power BI
3. Connect to “Web” data source in Power BI, using adjusted URL

Get anonymous share link from Google Docs

To get started, open your Google doc/sheet and click “Share.” If you don’t see “anyone (no sign-in required)” or something similar, click “More.” Then select “On – Anyone with the Link.” Copy the resulting URL.

Adjust URL for Power BI

1. Paste the URL into Notepad, or a similar, simple text editor.
2. Remove /edit?usp=sharing from the URL and add /export?format=xlsx&id= in its place
3. Copy the ID between /d/ and /export… (see red box below)
4. Paste the ID at the end of the URL (see green box below)

Connect to Google Sheet in Power BI

1. Get Data > Web

2. Paste your adjusted URL and click OK.

As long as you used the correct Google Sheet share link (anyone with the link can view), you’ll see your data ready to load or transform as usual in the Navigator screen. If you get an error, double-check the share link type.

Acknowledgment

Thanks to GGetty at this thread for pointing me in the right direction. Note one of the comments from 2017 warning you may need to be owner of a file (having edit rights may not be sufficient) doesn’t seem to be true anymore. I successfully connected to a file I only had edit rights to.