Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
2023 update - the method below is no longer required, the native connector to Google Sheets is generally available. This supports Google authentication, so the data no longer needs to be public.
https://learn.microsoft.com/en-us/power-query/connectors/google-sheets
**** BELOW IS NOW OUT-OF-DATE ****
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.
@GGetty Does using the "export?format=xlsx&id=" publish the sheet to wthe web for access?
Just want to make sure, before I use it, that this solution doesn't cause our sheet to be published or available to anyone other than who it has been shared with.
If this maintians the security of the sheet this is a real slick solution.
Look forward to hearing back from you!
@NWBI I think you did have to publish if you wanted to use the "export?format...." solution. You should definitely test first with a spreadsheet that doesn't have sensitive data. Caveat emptor. This thread is nearly two year old. Other folks in the thread talked about setting up a Google Developer ID for more secure access - you might want to try one of those solutions.
This worked just fine on an unpublished sheet.
Interesting.
Good solution, thanks!
@NWBI glad to hear it. I imagine it all worked smoothly as you as the author were logged in to Power BI and Google with correct credentials. It would be interesting to hear what happens if other users attempt to view the Power BI visualizations on PowerBI.com or by opening the Power BI .PBIX file and clicking refresh.
Glad you got what you needed. Cheers.
Hello,
I have succesfully connected a published Google Sheet to Power BI Desktop, published the report to app.powerbi.com and set up Gateway for it. When refreshing data I get:
@NWBI glad to hear it. I imagine it all worked smoothly as you as the author were logged in to Power BI and Google with correct credentials. It would be interesting to hear what happens if other users attempt to view the Power BI visualizations on PowerBI.com or by opening the Power BI .PBIX file and clicking refresh.
Glad you got what you needed. Cheers.
Hi GGetty, thanks a ton for this post. It was immensely helpful and worked like a wonder. Cheers!
This is amazing! It actually works, unlike the others
I just want to make a slight correction to GGetty's solution, which is AWESOME!
At step 4, DO NOT remove the slash "/" before the edit?usp=sharing
Keep it, and then do step 5 so it should look like:
""https://docs.google.com/spreadsheets/d/1nWV8adkjfadkfHWDIAa3ad/export?format=xlsx&id=1nWV8adkjfadkfHWDIAa3ad"
GGetty, it works fine for me! Thanks!
Hi,
I have copied doc link, but couldnt understand in Power Bi where do I need to past it. I mean which data connection I have to use to past this link ?
Hi @RasikaOgale
I wasn't clear in my original post on this, but I meant to start the data import from Power BI Desktop, not app.powerbi.com.
Also note my post from 10 minutes earlier on getting the right URL.
Not the publish again =_= I don't want people to see what I put on my Google Spreadsheet =_=
are there any other way ?
In case anyone needs this, here's a secure way to access Google Sheets.
https://tumbleroad.com/2016/07/07/free-marquee-google-sheets-template/
Hey @trebgatte I can follow all these steps but the only thing that loads is your 100 columns - none of my Google Sheets data. Am I doing something wrong.
Did you get your own developer id?
I got asked by another client to look into a secure/API connection direct to Power BI again.
My conclusion is that the only Google API Authorization option now is the dreaded oAuth2, which is beyond the capabilities of Power BI.
I did have a look at Treb's blog post, but that seems to get tripped up by the same change in the Google API.
FWIW my proposed workaround is to use Microsoft Flow to read from Google Sheets and write the rows to an Excel file stored in OneDrive, running on a schedule. I got a crude prototype running quickly so it seems viable.
Hope this saves others some time.
Obviously the best solution would be for Microsoft to add Google Sheets connectivity with integrated authorization - if you agree please add your votes and comments here - maybe it will be delivered before it's 3rd birthday ...
Deleted the thread because the "solution" actually didn't work.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
delete
Hi @dosansil and @Anonymous:
I made a stupid mistake here, so the solution doesn't work unfortunately: I didn't recognize that the sheet I was referrig to was still published to web. So despite me restricting the access in the "share"-area, there was still the published web-option which accepted my credentials. But the problem here is, that I actually didn't need credentials, as the anonymus connection would have worked as well.
I'm very sorry to have wasted your time here!! (Will edit/delete my orignal post so that others will not get fooled as well)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello, ImkeF. Don't worry. You are always helpful and gently. I'm grateful for your try and good assistance. If you discovery a solution, let me know. Thanks!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
67 | |
61 | |
23 | |
17 | |
12 |