Power BI Dataset Refresh Error with Google Sheet

[Luchiuda]

I have several Power BI dashboards that use Google Sheets as a data source, that are configured to automatically refresh every day. 
After 2 days of publishing the report, there is always an error while refreshing the dataset due to a lack of credentials in the data source, and the automatic refresh is disabled.

To fix this, I go to the dataset configuration, reload the credentials in the Data Source Credentials section and the report updates without problems.

How can I avoid having to constantly load the credentials?

[Luchiuda]

Hi @v-zhangti ,

Thanks for your reply. I do not have a on-premises gateway that works 24/7, and either way, my dataset only contains cloud data sources. This is why I established a scheduled refresh of the dataset without using an on-prem.
That solution was working correctly with all my reports. However, lately I have been having credentials issues when refreshing the dataset. The thing that comes to my attention is that each day I have issue with a different report, I can’t find a pattern. Some days I have to renew the credentials of 2 google sheets of a report, the next day 1 sheet of another report, and so on.
Do you have another solution for my problem?
Thanks!

[v-zhangti · Accepted solution]

Hi, @Luchiuda 

Please refer to the following links in the hopes of helping you.

Solved: Credentials for Google Sheets connector - Microsoft Fabric Community

Solved: Cannot add Google Sheet to a gateway (automatic re... - Microsoft Fabric Community

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

[v-zhangti]

Hi, @Luchiuda 

To avoid having to constantly reload the credentials, you can configure the On-premises data gateway. This gateway is free and allows you to refresh your data sources automatically without having to manually reload the credentials.

Solved: refresh data automatically google sheets - Microsoft Fabric Community

If your report dataset only contains cloud data sources like Google Sheets, you can manually refresh the dataset by using the Refresh now option. Power BI automatically connects to the workbook on OneDrive, or SharePoint Online, about every hour to check for updates. If the workbook has changed, Power BI will refresh the dataset and reports in the Power BI service.

Data refresh in Power BI - Power BI | Microsoft Learn

If your dataset contains both cloud and on-premise data sources, then it needs to use the gateway, and then you can configure schedule refresh. Note that you also need to select Allow user’s cloud data sources to refresh through this gateway cluster option under Gateway Cluster Settings.

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