cancel
Showing results for 
Search instead for 
Did you mean: 

Web connector & google sheets &credentials

Hi,

 

for the past 2 weeks, I am experiencing trouble with refreshing google spreadsheet source.

The PBI service and desktop seem to be having difficulties remembering credentials, which is interesting, as the spreadsheet is published to the web, so its accessible to everybody having the URL.

 

When I come to the service, schedule refresh and go to the data credentials tab, there are no "crosses", ie it seems, that PBI "remembers" the (anonymous...) credentials. As soon as I hit the "refresh now" button, the page also refreshes (as ctrl+r) and the wild cross appears, telling me, that PBI does not know the credentials. After selecting the anonymous option, everything repeats.

The issue happens for multiple spreadsheets and PBI reports.

 

The refresh history provides this issue description:

Show Scheduled 8/4/2018, 12:02:04 AM 8/4/2018, 12:15:14 AM Failed There was an error when processing the data in the dataset.

 

While "show details" provides this information:

Message: The credentials provided for the Web source are invalid. (Source at https://docs.google.com/spreadsheets/............./pub.) Table: LeadSources.
Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID: dcc9a58b-8858-4de9-8986-cd31f8f4bc2a
Request ID: f9419cce-cc28-4c8f-9414-d4ad7e9ddd87
Time: 2018-08-03 22:15:14Z

 

Strangely enough, sometimes (like twice a week on daily refresh), it even refreshes normally...

 

Anybody having the same issue or could provide any information whatsoever?

Status: New
Comments
Moderator

Hi @LavinaVRovine,

 

Based on my test, after we set the data source credential for this web data source as Anonymous and click Sign In, if we don't interact with the Data Source Credentials tab, the credential is keeping with Anonymous and we can set manual refresh or schedule refresh. In your scenario, when the issue occurs, please select Anonymous for data source credential then click Refresh Now, the refresh should be successful. 

 

In case of credential losing, you can also create a web data source points to this Google Sheet in Manage Gateways section, then configure the dataset to use this data gateway to refresh. I know Google Sheet doesn't require any gateway, but with gateway configured, the refresh will be smoothly as long as long gateway is running fine. 

 

Best Regards,
Qiuyun Yu 

Frequent Visitor

Hi,

 

i've setup the gateway as u've mentioned, which seems to fix the issue. 

 

Thank you

 

PS.

 

Hello,

 

it seems, that it did not help. I've setup gateway with anonymous access, Here is the error i've got (with downloadable url aswell)

 

Last refresh failed: Tue Aug 07 2018 13:48:00 GMT+0200 (Central European Summer Time)
Invalid connection credentials.Hide details
Underlying error code: -2147467259 Table: Azor.
Underlying error message: Přihlašovací údaje zadané pro zdroj Web jsou neplatné. (Zdroj v https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2...)
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind: Web
Microsoft.Data.Mashup.CredentialError.DataSourcePath: https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2...
Microsoft.Data.Mashup.CredentialError.Reason: AccessForbidden
Microsoft.Data.Mashup.MashupSecurityException.DataSources: [{"kind":"Web","path":"https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2..."}]
Microsoft.Data.Mashup.MashupSecurityException.Reason: AccessForbidden
Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID: bc28fc15-aebb-402e-b5b5-acc156070f25
Request ID: 2dd75591-2f3a-41c9-2889-a32c7972bb37
Time: 2018-08-07 11:48:00Z

Frequent Visitor

Hello,

 

it seems, that it did not. I've setup gateway with anonymous acces to gsheets. Here is the error i've got (even with the downloadable url):

 

Last refresh failed: Tue Aug 07 2018 13:48:00 GMT+0200 (Central European Summer Time) 
Invalid connection credentials.
Hide details

Underlying error code:-2147467259 Table: Azor.
Underlying error message:Přihlašovací údaje zadané pro zdroj Web jsou neplatné. (Zdroj v https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2...)
DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind:Web
Microsoft.Data.Mashup.CredentialError.DataSourcePath:https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2...
Microsoft.Data.Mashup.CredentialError.Reason:AccessForbidden
Microsoft.Data.Mashup.MashupSecurityException.DataSources:[{"kind":"Web","path":"https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2..."}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:AccessForbidden
Cluster URI:WABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID:bc28fc15-aebb-402e-b5b5-acc156070f25
Request ID:2dd75591-2f3a-41c9-2889-a32c7972bb37
Time:2018-08-07 11:48:00Z
Frequent Visitor

Hi,

 

it seems that it did not. I set up the gateway using anonymous access. Here is the error i got:

 

Last refresh failed: Tue Aug 07 2018 13:48:00 GMT+0200 (Central European Summer Time) 
Invalid connection credentials.
Hide details

Underlying error code:-2147467259 Table: Azor.
Underlying error message:Přihlašovací údaje zadané pro zdroj Web jsou neplatné. (Zdroj v https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2...)
DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind:Web
Microsoft.Data.Mashup.CredentialError.DataSourcePath:https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2...
Microsoft.Data.Mashup.CredentialError.Reason:AccessForbidden
Microsoft.Data.Mashup.MashupSecurityException.DataSources:[{"kind":"Web","path":"https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2..."}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:AccessForbidden
Cluster URI:WABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID:bc28fc15-aebb-402e-b5b5-acc156070f25
Request ID:2dd75591-2f3a-41c9-2889-a32c7972bb37
Time:2018-08-07 11:48:00Z
Frequent Visitor

Hello,

 

it seems, that it did not help. I've setup gateway with anonymous access, Here is the error i've got (with downloadable url aswell)

 

Last refresh failed: Tue Aug 07 2018 13:48:00 GMT+0200 (Central European Summer Time)
Invalid connection credentials.Hide details
Underlying error code: -2147467259 Table: Azor.
Underlying error message: Přihlašovací údaje zadané pro zdroj Web jsou neplatné. (Zdroj v https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2...)
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind: Web
Microsoft.Data.Mashup.CredentialError.DataSourcePath: https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2...
Microsoft.Data.Mashup.CredentialError.Reason: AccessForbidden
Microsoft.Data.Mashup.MashupSecurityException.DataSources: [{"kind":"Web","path":"https://docs.google.com/spreadsheets/d/e/2PACX-1vQTEw5KzKmwxKwFbqQvS35RfPsg4wLSJWZKS8fMkwK7LJSg-Fid2..."}]
Microsoft.Data.Mashup.MashupSecurityException.Reason: AccessForbidden
Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net
Activity ID: bc28fc15-aebb-402e-b5b5-acc156070f25
Request ID: 2dd75591-2f3a-41c9-2889-a32c7972bb37
Time: 2018-08-07 11:48:00Z

Idea Statuses