Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

LavinaVRovine

Web connector & google sheets &credentials

Submitted by on ‎08-06-2018 10:24 AM

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
See more ideas labeled with:
5 Comments (5 New)
Comments
v-qiuyu-msft
Community Support
Community Support
‎08-06-2018 11:46 PM

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 

LavinaVRovine
Frequent Visitor
‎08-07-2018 02:05 AM

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

 
LavinaVRovine
Frequent Visitor
‎08-07-2018 04:55 AM

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
LavinaVRovine
Frequent Visitor
‎08-07-2018 05:07 AM

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
LavinaVRovine
Frequent Visitor
‎08-07-2018 05:18 AM

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