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.
"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 :-)
Seems like only way is to keep the web key (link to csv) secured.