Forum Discussion
Authentication (permission) error override with Sharepoint online when looping sites w function
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?
6 Replies
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.
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 SourceThen 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.
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 SourceThank 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 SourceWhen 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.
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 TableIn 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"))
