Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
christianjon17
New Member

How to connect google sheet to Power BI

Good day,

 

I would like to ask if how can I import google sheet to power BI?

 

For your assistance.

 

Thank you and God bless,

Christian

1 ACCEPTED SOLUTION
mike_honey
Memorable Member
Memorable Member

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.

View solution in original post

72 REPLIES 72

 @gaillardb - It sounds like you are starting from app.powerbi.com ?  I didnt make it clear, but actually my solution uses Power BI Desktop.  That has much more "Get Data" functionality.

 

BTW these forums arent great - if you want someone to be notified of your reply you need to mention them eg @xyz

Hello Mike,

 

Could you help me locate the basic Get data / From web in PowerBI. I am registered as a free user.

 

Thanks

Thanks for this! It works on my Google Spreadsheets.

It only returns the first sheet? how do we get the other sheets?

Arif

Mike,

 

I am affraid your option doesn't really work anymore. I haven't been able to do it.

 

Are you using the "shareable link" from google sheet, or the actual sheet URL ?

 

Thanks !

 

 

 

 

From Google Sheets, go to File / Publish to Web.  Then change the selection from Web Page to Microsoft Excel.  Copy the generated link.

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)

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors