Forum Discussion
How to connect google sheet to Power BI
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.
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.
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:
Something went wrongCannot connect to the mashup data source. See error details for more information.
Please try again later or contact support. If you contact support, please provide these details.Underlying error messageThe Web.Page function requires Active Scripting to be enabled in Internet Explorer options. See https://go.microsoft.com/fwlink/?LinkId=506565 for details on how to enable Active Scripting.Has anyone experienced this?
Many thanks,Viktor
