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.
What if I need protected source? In case of Publishing to the web any link owner can veiw data source. It seems not secured enough.
Another way is to make use of PowerBI python script feature. (Note : Better Privacy, IMHO)
1. Set up python script to connect to Google Sheets (https://towardsdatascience.com/accessing-google-spreadsheet-data-using-python-90a5bc214fd2) (Note: You need to enable Both Google Drive and Google Sheet)
2. Use that python script in PowerBI as connector. ( https://docs.microsoft.com/en-us/power-bi/desktop-python-scripts ) (Need a bit modification such as pandas) Note: Make sure you install required python library such as "pip install pandas" etc
Below is sample of my script
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
from pandas.io.json import json_normalize
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('C:\\Users\\USER\\Downloads\\YourServiceAccountJSONFile.json', scope)
client = gspread.authorize(creds)
sheet = client.open('GoogleSheetFileName').sheet1
data= sheet.get_all_records()
df = json_normalize(data)
print(df)