Google Sheets authentication error in Report Server

[fdanielsouza]

Hi,

For some time I was able to refresh my Google Sheets data sources using the pattern https://docs.google.com/spreadsheets/u/0/d/{GOOGLE-SHEET-ID}/export?format=xlsx&id={GOOGLE-SHEET-ID} and it always worked well. However, today I tried to publish a new report with Google Sheets data source with anonymous credentials but I'm getting the following error 

I already tested my url and it works anonymously. Also I'm using the May-2021 version of Report Server. I saw that there's a new bet connector for Google Sheets since december, but in the update notes of the Report Server in January I didn't see any news about this connector, so I also don't know if I can solve my problem by updating it. 

[v-henryk-mstf · Accepted solution]

Hi @fdanielsouza ,

According to your description, for this kind of problem is not a problem caused by the version. Rather, after switching to anonymous credentials, it does not support refreshing the xlsx form of the url. You need to publish the url format to web page type and then change web.browsercontent to web.content in the power query, the refresh will work fine.
I have done the test in the same rs version and the reference is as follows.

if not change Web.BrowserContent to Web.Content

if change Web.BrowserContent to Web.Content, refresh successfullty

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

Best Regards,
Henry

[v-henryk-mstf]

Hi @fdanielsouza ,

According to your description, you get the data of the url dynamically through Web.Contents. The report server seems to have limitations on this type of data source and is unable to create scheduled refreshes.

Related document link.Power BI report scheduled refresh in Power BI Report Server - Power BI | Microsoft Docs

If the problem is still not resolved, please point it out. Looking forward to your reply.

Best Regards,
Henry

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

[v-henryk-mstf]

Hi @fdanielsouza ,

Although news about this connector was not mentioned in the latest January update, it seems that this is not a major issue in your case.

I suggest deleting the previous credential information in the data source setting and reconnecting to the Google Sheets data source with anonymous credentials. Alternatively, you can upgrade to the latest version and try again.

Similar solutions.

Solved: Connecting to Google Sheets - Anonymous credential... - Microsoft Power BI Community

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[fdanielsouza]

Hi @v-henryk-msft, it didn't work. I don't have problems when in Power BI Desktop, the error I'm getting is in the Report Server browser, when I try to set anonymous credentials it tells me Log on failed. Already tried to clean permissions, delete and re-publish the report, but nothing worked.

This is when I go with the Web.Contents path. The new Google Sheets connector doesn't even give me the option to set the credentials and I can't create the refresh schedules.