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.
Hi guys,
to connect fully securely, you can use my Power BI - Google Spreadsheets custom connector:
Hi,
I successfully connected to my Google Sheets data using this connector yesterday. However, when I'm hitting refresh I get the following error messages:
No columns have changed in my source data, except that new rows got added. How can I resolve this issue?
Is the sheet connected to a form?
Is the column renamed in the source file?
Did anything happen to the source file that would change title, position or structure of column?
Also how many rows in the google sheet?
Open the "Advanced Editor" in Query Editor and double check all of your syntax and see if any changes are being applied to that date column in the query.
Last piece of advice which usually yields results for me: walk backwards step-by-step of all your data prep steps and see if you can deduce a pattern or where the error ocurred. Usually the errors are so miniscule us humans miss them.
Has anyone managed to get this working with scheduled refresh and personal gateway?
I'm getting the following error:
Anonymous See earlier in the thread where mike_honey talks about using anonymous web access in powerbi service once you first build and create in power bi desktop ...
@mike_honey Thanks for the follow-up. This is great news (anonymous continuous updates of data from Google Sheets directly from app.powerbi.com without having to go through Power BI desktop and republish / overwrite). I never even considered that app.powerbi.com could access a data source configured / specified in Power BI Desktop. I keep thinking of app.powerbi.com as merely the online presence of a Power BI Desktop file and that is just not true. app.powerbi.com can act on its own once established and published via Power BI Desktop. It's just (still?) counterintuitive to me to realize there is ongoing functionalityin app.powerbi.com (e.g., connect to web sources) even though the original functionality can only be built via Power BI desktop.
