cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

69 REPLIES 69
Highlighted

Should this be working as well with Power BI Report Server?

 

Best Regards

Highlighted

hey thanks, can we connect


@ImkeF wrote:

 I've used this custom connector recently and it worked just fine:

https://www.thebiccountant.com/2017/09/24/custom-connector-import-google-sheets-oauth2-powerbi/

 



google sheets linked to company id with power bi?

Highlighted

Hi @asmarezgui ,

you can publish the report also on the report server, but you cannot schedule a refresh unfortunately, due to current limitations with OAuth: https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh  

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted

Hi @PowerBI_AB ,

I don't understand what you mean with "linked to company id " (I'm not very familiar with GoogleSheets actually)

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Highlighted

Thank You!

 

It's working.

Highlighted
Advocate IV
Advocate IV
Highlighted

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)

Highlighted
New Member

Hello, 

is there any possibility to connect google sheet if i do not have access to Google API (company limitation).

and i can not setup document availible to anybody (as well company limitation).

 

in fact when i insert the link, it gives me an erreur and i can see in Web View that Google indentification required.

 

 

Highlighted

Hi!

 

I had the same issue - what I did was that I created the project from my private Google Account. You can create a project, add the API:s and download the credentials. Then you just have to share the Sheet with the email in the credentials you downloaded! 🙂

Highlighted

hi, i have created a workbook on google sheets and i want to link it to power bi. This works for me, but as soon as I publish the report to power bi online and share it with someone, the data is not automatically updated. Is it possible to publish the report online and at the same time to update the data from the google workbook? Thanks

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors