Google Sheets Auto refresh in Power BI Report

[LijunChen]

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?

Thanks. 

Lijun 

[v-zhangti · Accepted solution]

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. 

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

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.

[sebaschur79]

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?