Authentication (permission) error override with Sharepoint online when looping sites w function

[Dzeipii2]

Hi,

Ive been struggling with getting dataload working using the Sharepoint online plugin (SharePoint.Tables). I am looping through a lot of sites to get the metadata for document sets and files. Most of the sites are ok, but there are sites that I dont have permissions to. Some deliberately, some not. There is no data I could use to filter the list/table that I use as an input to the function that retrieves the data by site. And actually Id like to have a list of the ones Im missing permissions.

What happens with the ones that Im missing a permission is "We couldn't authenticate with the credentials provided. Please try again". When I try it, the same login screen and error appers. And when i leave the login form, the load is canceled.

I tried to overcome this with "Try otherwise" both in the function query and the list calling the custom function. This does no seem to work with the permission issue. Cant figure out other means to hande this kind of error handling.

Anyone else, who has had the same problem and managed to bypass it?

[Daryl-Lynch-Bzy]

Hi @Dzeipii2  - lets try the following change:

let
    Source = (SharepointSiteURLID as text) => let
    Lähde =
    try (SharePoint.Tables("https://COMPANYX.sharepoint.com/sites/"&SharepointSiteURLID, [Implementation=null, ApiVersion=15]))
    otherwise #table( type table[Table=text] , {{"No Premission"}}),
    #"Suodatetut rivit" = Table.SelectRows(Lähde, each ([Title] = "Account Documents" or [Title] = "Approval Documents" or [Title] = "Sales Documents"))
in
    #"Suodatetut rivit"
in
    Source

[Dzeipii2]

Thank you Daryl,

That seems to help to some extent. A row with the sharepoint site that I do not have permission now gets handled and full list of sharepoint sites is returned without PBI prompting for credentials continuously. However, now both the sites with and without permissions give a new error:

Expression.Error: 1 arguments were passed to a function which expects 0.

I tried to simply the query also as per below with the same error:

let
    Source = (SharepointSiteURLID as text) => let
    Lähde =
    try (SharePoint.Tables("https://COMPANYX.sharepoint.com/sites/"&SharepointSiteURLID, [Implementation=null, ApiVersion=15]))
    otherwise #table( type table[Table=text] , {{"No permission"}})
in
    #"Lähde"
in
    Source

When filtered to one row the one with permission is ok and the one with no permissions fails.

Also tried to evaluate by checking if the resulting table is empty:

(SharepointSiteURLID as text) => let
    Source =
if
    Table.IsEmpty(try (SharePoint.Tables("https://COMPANYX.sharepoint.com/sites/"&SharepointSiteURLID, [Implementation=null, ApiVersion=15]))
    otherwise Table.FromRecords({})) then "No permission"&SharepointSiteURLID else SharepointSiteURLID
in
    #"Source"

Cant figure out, what is the function that does not require argument.

[Daryl-Lynch-Bzy]

Hi @Dzeipii2 - I tried to replicate using the following function.  I used the Implementation="2.0", because it was faster.  I managed to get this work, but only when the sharepoint site didn't exist.  When is was a permissions issue, it kept asking for more credentials.

(SharepointSiteURLID as text) as table => 
    let
        Table = 
            try 
                SharePoint.Tables("https://beazley.sharepoint.com/sites/" & SharepointSiteURLID, [Implementation="2.0"])
            otherwise 
                #table( type table[Title=text] , {{"No permission"}})
    in
        Table

In this code, I have updated the Column Heading from "Table" to "Title".  The addition of these steps before #"Suodatetut rivit" step will remove the sites with "No permission".  So, I think this filter needs to be moved out of the function into the next step.  Something like this:

#"Kutsuttu mukautettu funktio" = Table.AddColumn(
        #"Kept First Rows", 
        "GetAllSharepointSitedata", 
        each GetAllSharepointSitedata([absoluteurl_siteIDpart])
    ),
#"Permission Test" = Table.AddColumn(#"Kutsuttu mukautettu funktio" , "Permission Test", each  _[GetAllSharepointSitedata][Title]{0} <> "No Permission", type logical ),
#"Exclude No Permission" = Table.SelectRows( #"Permission Test" , each [Permission Test]),
#"Expand Table Columns" = Table.ExpandTableColumn(#"Exclude No Permission", "GetAllSharepointSitedata", {"Title"}, {"Title"}),
 #"Suodatetut rivit" = Table.SelectRows(#"Expand Table Columns", each ([Title] = "Account Documents" or [Title] = "Approval Documents" or [Title] = "Sales Documents"))

[Dzeipii2]

Makes sense how to "No permission" sites could be handled. Unfortunately I cant get there without the endless loop of sing-in request. I managed to find some more information on the sharepoint connections. In one blog post I found out that Web.Contents should be more performant and it has ManualStatusHandling option. https://docs.microsoft.com/en-us/power-query/handlingstatuscodes

Unfortunately authentication related expections cannot be handled with standard connector:

PQ tries to connect again and again as a result. Extension could be an option, but I think I give for now and ask the Sharepoint Admin to share a list of sites that have errons in permission setup. One of the sub-objectices I had was to identify missing permission caused by errors in related Power Automate flows. For references, I started to build the following query with the same end result:

= let
    Source = 
    Xml.Tables(Web.Contents("https://COMPANYX.sharepoint.com/sites/crm-p-XXX/_api/web/lists/GetByTitle('Sales%20Documents')/items", [ManualStatusHandling = {400..500}])),
    GetMetadata = Value.Metadata(Source),
    GetResponseStatus = GetMetadata[Response.Status],
    Output = if GetResponseStatus=404 then "Error!" else Source
in
    Source

[Daryl-Lynch-Bzy]

Hi @Dzeipii2 - you please share the M code that you are using as I think it will merely the timing for the try otherwise or the inclusion of an if statement at the right moment to resolve the problem.  Please remove any specific or private information.

[Dzeipii2]

Yeah, should have shared it in the first place:

The custom function group has the original query, parameter and the actual function:

GetallSharepointSitedata:

let
    Source = (SharepointSiteURLID as text) => let
    Lähde =
    try (SharePoint.Tables("https://COMPANYX.sharepoint.com/sites/"&SharepointSiteURLID, [Implementation=null, ApiVersion=15]))
    otherwise "No permission",
    #"Suodatetut rivit" = Table.SelectRows(Lähde, each ([Title] = "Account Documents" or [Title] = "Approval Documents" or [Title] = "Sales Documents"))
in
    #"Suodatetut rivit"
in
    Source

Then the query that uses the above custom function:

let
    Lähde = CommonDataService.Database("prod.crm4.dynamics.com"),
    #"Suodatetut rivit" = Table.SelectRows(Lähde, each ([Name] = "sharepointsite")),
    dbo_sharepointsite = #"Suodatetut rivit"{[Schema="dbo",Item="sharepointsite"]}[Data],
    #"Poistettu muut sarakkeet" = Table.SelectColumns(dbo_sharepointsite,{"sharepointsiteid", "name", "statecode", "statecodename", "description", "absoluteurl"}),
    #"Jaa sarake osiin erottimen mukaan" = Table.SplitColumn(#"Poistettu muut sarakkeet", "absoluteurl", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, true), {"absoluteurl.1", "absoluteurl_siteIDpart"}),
    #"Muutettu tyyppi" = Table.TransformColumnTypes(#"Jaa sarake osiin erottimen mukaan",{{"absoluteurl.1", type text}, {"absoluteurl_siteIDpart", type text}}),
    #"Kept First Rows" = Table.FirstN(#"Muutettu tyyppi",10),
    #"Kutsuttu mukautettu funktio" = 
    Table.AddColumn(#"Kept First Rows", "GetAllSharepointSitedata", each GetAllSharepointSitedata([absoluteurl_siteIDpart]))
in
    #"Kutsuttu mukautettu funktio"

There are some extra steps that I remove, if I get the logic working. In particular the step with "Kept first rows" is there to check that the query runs ok for the first site. If I remove that then the resulting data set starts to include sites that I dont have permission for. Sorry for having some Finnish in there.