9

I am trying to connect google sheet with power bi using R Studio, there is a video on youtube by stephnie locke, but that is just a short video and dont help completely. any one there which can help properly and completely.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Azam Saleem
  • 129
  • 1
  • 1
  • 3

3 Answers3

16

If you're not limited to R Studio, there is another way to access Google Sheet data from Power BI:

Sample data: sample

  1. Get shareable link of the Google Sheet (Permission at least Anyone with the link can view):

shareable link

  1. Modify the shareable link and add the following parts:

    From:

    https://docs.google.com/spreadsheets/d/google-sheet-guid/edit?usp=sharing

    To:

    https://docs.google.com/spreadsheets/d/google-sheet-guid/export?format=xlsx&id=google-sheet-guid

e.g.

https://docs.google.com/spreadsheets/d/1ohAz8SD6viEGIrcor-JC-vCt2yRbgY1c-7Uflv-_hrA/edit?usp=sharing -> https://docs.google.com/spreadsheets/d/1ohAz8SD6viEGIrcor-JC-vCt2yRbgY1c-7Uflv-_hrA/export?format=xlsx&id=1ohAz8SD6viEGIrcor-JC-vCt2yRbgY1c-7Uflv-_hrA

  1. Create a blank query in Power BI and get the source as below:

    let Source = Excel.Workbook(Web.Contents("the-link-in-step-2"), null, true) in Source

code

table-1

  1. If you drill into the Table cell you should be able to see the data and work from there.

table-2

Foxan Ng
  • 6,883
  • 4
  • 34
  • 41
  • @AzamSaleem Glad to help :) If this help solving the issue, you can [accept this answer](https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work/5235#5235) to positively close the question. – Foxan Ng Aug 30 '17 at 11:53
  • 1
    This doesn't seem to be working now. Can you please confirm? – Gangula Sep 03 '19 at 08:03
8

An easier way: Go to Get data, search Web. Select Web Select Web

on the URL.

-> Modify the URL.

From:

https://docs.google.com/spreadsheets/d/emphasized textgoogle-sheet-guid/edit?usp=sharing

To:

https://docs.google.com/spreadsheets/d/google-sheet-guid/export?format=xlsx&id=google-sheet-guid

Paste.

Paste link

And there is your sheet.

Select your sheet

Joseph M.
  • 81
  • 1
  • 2
  • 1
    I am able to see my sheets but I get this error: Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [Expression.Error] The column 'P O/U' of the table wasn't found.. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. The current operation was cancelled because another operation in the transaction failed. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. OLE DB or ODBC error: Exception from HRESULT: 0x80040E4E. '. – Conner Feb 11 '20 at 21:34
2

To build upon @Joseph A's perfect answer, if you would like to make it refreshable after publishing your report to the Power BI service from Desktop:

  1. Navigate to the workspace where the report is published (i.e. My Workspace)
  2. Click Datasets
  3. Under Actions, click the ellipses and choose Settings
  4. Expand Data source credentials and click Edit Credentials
  5. Change Authentication method to Basic
  6. Enter User Name and Password of Google Docs account where the file resides and click Sign In

Your report can now be refreshed and updated (manually or scheduled) to reflect changes in your Google Sheets doc!

ronpadz
  • 21
  • 1