cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LijunChen
New Member

Google Sheets Auto refresh in Power BI Report

Dear Power BI users,

 

I am using Power BI desktop to generate data visualization for the data from Excel files and Google sheets. For reading in google sheets, I am using the Beta version, that is using the url of the Google sheets and my organizational google account for my credentials to access the Google sheets..

 

I wanted to set up data autorefresh for the report. But it is not working because my Google sheet credentials has been stopped a few hours after I set it up.  So I have to manually refresh the data and reset the credentials . (see below picture for the error we encountered). Is there any way to solve the problem and make my Goolgle sheet credentials permanent?

LijunChen_0-1643863594012.png

Thanks. 

Lijun 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @LijunChen 

 

The situation you mentioned does exist. This is a known issue, ICM is 285035360. While doing OAuth2 authentication, GoogleSheets connector was not receiving refresh token due to which once Access token expires(after one hour), there is no way for for GoogleSheets report to refresh and get new access token.

 

Also, based on my testing, you can currently try the Web connector to connect Google Sheets, please refer to https://www.youtube.com/watch?v=Z9V221eNbi0

vzhangti_0-1644216901971.png

Then use standard gateway to connect to the data source and it will not have invalid credentials as frequently as the google sheet connector.

vzhangti_1-1644216902045.png

vzhangti_2-1644216902258.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.

 

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @LijunChen 

 

The situation you mentioned does exist. This is a known issue, ICM is 285035360. While doing OAuth2 authentication, GoogleSheets connector was not receiving refresh token due to which once Access token expires(after one hour), there is no way for for GoogleSheets report to refresh and get new access token.

 

Also, based on my testing, you can currently try the Web connector to connect Google Sheets, please refer to https://www.youtube.com/watch?v=Z9V221eNbi0

vzhangti_0-1644216901971.png

Then use standard gateway to connect to the data source and it will not have invalid credentials as frequently as the google sheet connector.

vzhangti_1-1644216902045.png

vzhangti_2-1644216902258.png

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.

 

Hi, I understand from your captures that you are connecting to an open sheet available to be read by anyone with the link. Do you know if this would work with protected sheets that need a login from specific users? 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Solution Authors