Handling an expected "Access to the resource is forbidden." error

[dapkiene]

Hi everyone,

I am connecting to UiPath Automation Hub data through API connection and using the CBA endpoint. It is specific for each process (API call takes process ID) but I am trying to have a workaround to have the CBA data for all process IDs.

(I connected using the process in here: https://automation-hub.uipath.com/api/v1/api-doc/#/)

For now, I Have a query that takes in a parameter and displays its respective CBA table. Then I have a function that calls the query with the different parameter (input of the function).

Finally, the plan is to have a table with all process IDs and call the function for each row to return their respective table. However, some Process IDs do not have CBA and thus results in "Expression.Error: Access to the resource is forbidden." I would prefer having an error for each row that gets this error and the table if the row contains a valid process ID. But it gives me the error on the whole and it leaves me in a dead-end.

I even tried a lot of different try / otherwise cases but still keep getting that error.

Any ideas how or where to use the "try" or "if" for this to work? Or any other solution would be greatly appreciated.

Thanks!

[mahoneypat]

Just to clarify, if you filter your query before you invoke the function to IDs that get results, everything works correctly?  I suspect you will need to handle the error within the function.  Can you share that M code?  If a table is returned, you can add a step to count the rows of it and not do the subsequent step if there are none, for example.

Pat

[dapkiene]

Hi @mahoneypat,

See the code below. I am filtering by process_task_stability_id to get the working rows but I am not sure if this is the best way to do it because I just found that it worked. My invoked function returns a table for the automation ID one passes as a parameter.

let
Source = Table.SelectColumns(Automations,{"process_id", "process_task_stability_id"}),
#"Filtered Rows" = Table.SelectRows(Source, each ([process_task_stability_id] <> null)),
#"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "IDFunction", each try IDFunction([process_id]) ),
#"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"process_task_stability_id"}),
#"Expanded IDFunction" = Table.ExpandRecordColumn(#"Removed Columns", "IDFunction", {"HasError", "Value"}, {"IDFunction.HasError", "IDFunction.Value"}),
#"Filtered Rows1" = Table.SelectRows(#"Expanded IDFunction", each [#"IDFunction.HasError"] = false),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"IDFunction.HasError"}),
#"Expanded IDFunction.Value" = Table.ExpandTableColumn(#"Removed Columns1", "IDFunction.Value", {"title", "title.1", "field_name", "value", "input_options", "input_selected", "start_date.value", "end_date.value", "baseline_cycle_time.value", "actual_cycle_time.value", "baseline_actual_delta.value"}, {"title", "title.1", "field_name", "value", "input_options", "input_selected", "start_date.value", "end_date.value", "baseline_cycle_time.value", "actual_cycle_time.value", "baseline_actual_delta.value"})
in
#"Expanded IDFunction.Value"

It might be because other projects are in a too early stage and they don't have a process_task_stability_id yet so that's why it might be working.

However, my initial question is about handling a situation like this, when I want to get the error by row (so that later I could discard those rows) but it gives me the overall error, so I cannot even know which rows are working and which are not. 

Thanks!

P.S. For some reason I replied to this three times and still cannot see that I replied. Hopefully, now it'll work.

[mahoneypat]

At your Invoked Custom Function step, do you see "error" for some of the rows?  If so, you can select that column, right click and choose "Remove Errors".

Pat

[dapkiene]

Hi @mahoneypat ,

If I comment out my M query line which does the filtering before applying the function I get the following:

let
    Source = Table.SelectColumns(Automations,{"process_id", "process_task_stability_id"}),
    // #"Filtered Rows" = Table.SelectRows(Source, each ([process_task_stability_id] <> null)),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "IDFunction", each try IDFunction([process_id]) ),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"process_task_stability_id"}),
    #"Expanded IDFunction" = Table.ExpandRecordColumn(#"Removed Columns", "IDFunction", {"HasError", "Value"}, {"IDFunction.HasError", "IDFunction.Value"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded IDFunction", each [#"IDFunction.HasError"] = false),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"IDFunction.HasError"}),
    #"Expanded IDFunction.Value" = Table.ExpandTableColumn(#"Removed Columns1", "IDFunction.Value", {"title", "title.1", "field_name", "value", "input_options", "input_selected", "start_date.value", "end_date.value", "baseline_cycle_time.value", "actual_cycle_time.value", "baseline_actual_delta.value"}, {"title", "title.1", "field_name", "value", "input_options", "input_selected", "start_date.value", "end_date.value", "baseline_cycle_time.value", "actual_cycle_time.value", "baseline_actual_delta.value"})
in
    #"Expanded IDFunction.Value"

Then on the invoke custom function step I get the error on the whole query but not for each line:

[dapkiene]

Sorry, above screeshot is having another error. But here it is after fixing:

[mahoneypat]

I suspect it is due to your function.  Can you share the M code for the IDFunction formula?

Pat

[dapkiene]

Sure,

here it is after removing sensitive info. It currently has Manual status handling errors commented out but it didn't work with them uncommented as well:

= (AutomationID as any) => let
    Source = try 
Web.Contents("https://automation-hub.uipath.com/api/v1/openapi/CBA?id="&Number.ToText(AutomationID), [Headers=[#"Content-Type"="application/json", #"x-ah-openapi-auth"="openapi-token", #"x-ah-openapi-app-key"="***", Authorization="Bearer ***"]//, ManualStatusHandling = {403, 401, 404, 200}
]) otherwise "Error!"
,
    Json_step = Json.Document(Source),
    data = Json_step[data],
    sections = data[sections],
    #"Converted to Table" = Table.FromList(sections, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"title", "sections"}, {"title", "sections"}),
    #"Expanded sections" = Table.ExpandListColumn(#"Expanded Column1", "sections"),
    #"Expanded sections1" = Table.ExpandRecordColumn(#"Expanded sections", "sections", {"title", "input_fields"}, {"title.1", "input_fields"}),
    #"Expanded input_fields" = Table.ExpandListColumn(#"Expanded sections1", "input_fields"),
    #"Expanded input_fields1" = Table.ExpandRecordColumn(#"Expanded input_fields", "input_fields", {"field_name", "value", "input_options", "input_selected", "start_date", "end_date", "baseline_cycle_time", "actual_cycle_time", "baseline_actual_delta"}, {"field_name", "value", "input_options", "input_selected", "start_date", "end_date", "baseline_cycle_time", "actual_cycle_time", "baseline_actual_delta"}),
    #"Expanded input_options" = Table.ExpandListColumn(#"Expanded input_fields1", "input_options"),
    #"Expanded start_date" = Table.ExpandRecordColumn(#"Expanded input_options", "start_date", {"value"}, {"start_date.value"}),
    #"Expanded end_date" = Table.ExpandRecordColumn(#"Expanded start_date", "end_date", {"value"}, {"end_date.value"}),
    #"Expanded baseline_cycle_time" = Table.ExpandRecordColumn(#"Expanded end_date", "baseline_cycle_time", {"value"}, {"baseline_cycle_time.value"}),
    #"Expanded actual_cycle_time" = Table.ExpandRecordColumn(#"Expanded baseline_cycle_time", "actual_cycle_time", {"value"}, {"actual_cycle_time.value"}),
    #"Expanded baseline_actual_delta" = Table.ExpandRecordColumn(#"Expanded actual_cycle_time", "baseline_actual_delta", {"value"}, {"baseline_actual_delta.value"})
in
    #"Expanded baseline_actual_delta"

[mahoneypat]

Thanks for providing the function.  I don't see any obvious reason why the function would cause that.  If you make a single web call with one of the IDs that you suspect won't return data, do you get the error?

Pat

[dapkiene · Accepted solution]

Yes, it returns the same error if I make a single web call on the single id which doesn't have the specified data.

I guess it could happen because some of the projects are in too early state so maybe filtering by project stage could help. I was trying to find the reason why doesn't it give me error at a row level because if it did, I could simply filter out rows that give me error.

Anyway, thanks for trying to help. I'll mark this as a solution as it kind of is a work-around.

Thanks,

Ruta

[Abdel_Work]

Hello Ruta, 

Few years after, facing the exact same issue.. Not with UI Path but same problem, trying to make an API call on an ID for which I may or may not have permission to access. Thing is I cannot flag ahead which ones I'm allowed or not. 
The annoying part as you mentioned is not being able to get an error at row level, it crashes the whole thing. I tried all try options, at every possible level, encapsulating the call in a function and so on. I also tried the ManualStatusHandling option of the Web.Contents function and nothing successful. When running the same query via Postman, I get the 403 status code that I'm not able to retrieve on Power Query. While a 404 status error can be retrieved. I think authorization error is the one that cannot be catched. It's a shame as so many options were made available and none can actually manage this specific error...

So almost 4 years after you, same error and really not a single perspective of solving

Too bad..

Abdel

[RJ251997]

Hi @dapkiene , 

Reset the data connection to re-enter credentials. than

 click Options and settings on the menu than

Select Data Source Settings to open the window shown directly below.

and go to that link its help you 

https://www.spguides.com/access-to-the-resource-is-forbidden-power-bi/#:~:text=In%20the%20Power%20BI...

Thank & Regards

Rohit Jaiswal

[dapkiene]

Hi Rohit Jaiswal,

thanks for the answer, but it does not apply for this case.  The access and credentials are fine because when I change the parameter I can see it works well. My question is about handling this error when it comes and ignoring the rows for which it is an error. Right now I get this as error as a whole and not by row and I am searching for a way to get the result in a new column where the values are either a table or this error.

For now, I found a temorary solution but it would be interesting to know how to handle this error.

Thanks!