cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
christianjon17
New Member

How to connect google sheet to Power BI

Good day,

 

I would like to ask if how can I import google sheet to power BI?

 

For your assistance.

 

Thank you and God bless,

Christian

1 ACCEPTED SOLUTION

Accepted Solutions
mike_honey Power Participant
Power Participant

Re: How to connect google sheet to Power BI

The easiest way is to Get Data / From Web, then enter the URL to your google sheet, with "&output=xls" on the end, e.g.

 

http://spreadsheets.google.com/pub?key=r1hlZB_n1rpXTij11Kw7lTQ&output=xls

 

PBI then analyses the resulting Excel file, showing the tabs as tables , which you can edit and manipulate.

View solution in original post

68 REPLIES 68
mike_honey Power Participant
Power Participant

Re: How to connect google sheet to Power BI

The easiest way is to Get Data / From Web, then enter the URL to your google sheet, with "&output=xls" on the end, e.g.

 

http://spreadsheets.google.com/pub?key=r1hlZB_n1rpXTij11Kw7lTQ&output=xls

 

PBI then analyses the resulting Excel file, showing the tabs as tables , which you can edit and manipulate.

View solution in original post

Julieng
New Member

Re: How to connect google sheet to Power BI

Mike,

 

I am affraid your option doesn't really work anymore. I haven't been able to do it.

 

Are you using the "shareable link" from google sheet, or the actual sheet URL ?

 

Thanks !

 

 

 

 

arifuddin
Frequent Visitor

Re: How to connect google sheet to Power BI

It only returns the first sheet? how do we get the other sheets?

Arif
mike_honey Power Participant
Power Participant

Re: How to connect google sheet to Power BI

From Google Sheets, go to File / Publish to Web.  Then change the selection from Web Page to Microsoft Excel.  Copy the generated link.

LsasS
Frequent Visitor

Re: How to connect google sheet to Power BI

What if I need protected source? In case of Publishing to the web any link owner can veiw data source. It seems not secured enough.

mike_honey Power Participant
Power Participant

Re: How to connect google sheet to Power BI

"Secured enough" is obviously subjective and depends on your scenario.

 

Google do have an oAuth2 API which apparently provides secure access to protected Google Sheets. The challenges will be:

 

1) getting Power BI to work with Google's oAuth2 API - this might be possible but at the least it will be quite difficult.  There are no obvious doco or blog posts to follow.

2) getting Power BI to read the result - it doesnt appear (from my quick scan of Google's oAuth2 API doco) that it provides the same easy method to return an Excel file as the result.

 

I'd be really happy to be proved wrong, if someone has the time and persistence.  Right now I have neither 🙂

LsasS
Frequent Visitor

Re: How to connect google sheet to Power BI

Seems like only way is to keep the web key (link to csv) secured.

miguelflores Advocate I
Advocate I

Re: How to connect google sheet to Power BI

Thanks for this! It works on my Google Spreadsheets.

gaillardb
New Member

Re: How to connect google sheet to Power BI

Hello Mike,

 

Could you help me locate the basic Get data / From web in PowerBI. I am registered as a free user.

 

Thanks

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors