Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Credentials for web conection are invalid (custom conector for sharepoint)

So, yeah, another one about sharepoint and credentials. I searched the forum and the web in the last two days and couldnt find anything that could help me.

 

I have a report that reads data from a SharePoint list, and the client asked me to add two pieces of information that I couldnt find using the connectors available in Power Query: the comments and versions of the items. So, I made two custom connectors using Web.Contents() and Relative Patch to get this information dynamically from this list.

 

The code is:

 

 

 

let
    CustomFunction = 
    ( List_SP as text, Item_ID as number ) => 
    let
        Source = 
        Xml.Tables(
            Web.Contents(
                SP,
                [RelativePath =  "/_api/web/Lists/getbytitle('" & List_SP & "')/items(" & Text.From(Item_ID) & ")/Comments()"]
                )
            ),
        Navigation = Source{0}[entry],
        Content = Table.SelectColumns(Navigation,{"content"}),
        ExpandContent = 
        Table.ExpandTableColumn(Content, "content", {"http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"}, {"content"}),
        ExpandProprierties = Table.ExpandTableColumn(ExpandeContent, "content", {"properties"}, {"properties"}),
        ExpandSchema = Table.ExpandTableColumn(ExpandeProprierties, "properties", {"http://schemas.microsoft.com/ado/2007/08/dataservices"}, {"properties"})
    in
        ExpandSchema
in
    CustomFunction

 

 

NOTES:
- The SP in the web.contents first argument is a parameter that has the website address  (https://client.sharepoint.com/sites/SiteName).

- The other tables use the same parameter as source, but with the standard sharepoint connectors.
- The second function is the same, it only changes the last argument of the relative patch from Comments() to versions.

 

On the desktop everything works and updates, but in services, the credentials for the web connection are invalid, and I tested all possible combinations, whether or not I ignore the test connection, and nothing works, I receive code 400 with the message


and when i try to update the credentials, i receive the message

 

Ive already tried deleting and publish again the semantic model, changing the privacy levels in the desktop file (combine or ignore), and nothing works.

 

So, what am I doing wrong or not seeing?

 

Thanks for the support

  • Anonymous's avatar
    Anonymous
    2 years ago

    I managed to make it work!


    I changed the parameter

    from https://client.sharepoint.com/sites/SiteName

    to https://client.sharepoint.com/sites/SiteName/_api.

     

    I deleted the published semantic model, configured on the desktop the credential and privacy as organizational on all connections (web and sharepoint), then republished it, put the web and sharepoint credentials as OAuth2 and privacy as oragnizational and skipped the test connection on web connector, and it worked!!

     

    In other words, all because of a "/_api".

9 Replies