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.
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: