Update Sharepoint List from Excel - Validate source people field before insert/update

[SirHC]

Dear all

I have a flow which is updating a Sharepoint List from an Excel file. Unfortunately the Owner field in the source is not maintained properly. having an user like firstname.name@company.com will be imported just fine in to the People field.

But if there is

• a misspelling, wrong email
  a free text (just a name)
  a correct email from an employee who left the company (no longer in directory)

the value can obviously not be resolved and it ends in this error:

{
  "status": 400,
  "message": "The specified user FREE TEXT NAME could not be found.\r\nclientRequestId: d071e7d1-fdfc-489e-92b3-ce713ea6d2fa\r\nserviceRequestId: d071e7d1-fdfc-489e-92b3-ce713ea6d2fa"
}

I can handle the empty fields as follows:

if(equals(items('Apply_to_each')?['Owner Contact'],''),null,items('Apply_to_each')?['Owner Contact'])

How can I do a check if the user will be resolved?

If it can not be resolved I'd like to have a specific user instead or at least null (an empty field)

Currently the 'wrong' data results in the error and none of the record will be saved.

Thank you very much for any hints/ideas.

[ekarim2020]

If I have understood the question correctly, you could use the Get user Profile (V2) action to get the user's profile information from Azure AD.

You can use parallel branches to handle valid and invalid users. There will be one branch to handle a valid verified user and another branch for the "bad user" (user not found in AAD):

Configure the Bad User branch to run when the Get user Profile (V2) action fails:

Then set the following settings when both branches need to join up again: set this action to run if the above actions succeed or fail:

Example run-time for a "bad user":

Hope this helps

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[SirHC]

@ekarim2020 thank you very much. I guess that is what I need. Just not sure how to achieve it. I'm a bloody newbe.

Could you pleas elaborate the step: "Configure the Bad User branch to run when the Get user Profile (V2) action fails:"
What is the "actions(..)" need to be in the Inputs field please?

Thank you very much

[ekarim2020]

The Compose action was there to help illustrate an example. You can enter any valid action for the "bad user action" such as sending an email to the user informing them that a bad email address was entered in the Excel file:

Hope this help.

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[SirHC]

ok thanks. I did that.

how do I join this parallel branches again to the "Final Operation"?

currently it takes about one second per entry to verify and add or update. I guess it will get even slower with this extra step. for now my flow looks as follows. Between Get user profile (v2) and Condition there will be the parallel branch in between.

[ekarim2020]

This short gif animation should help:

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[SirHC · Accepted solution]

I see your approach and I'm thankful for all your help Ellis, but I did not get it to run 😞

After some further tests I figured out another way. I guess both ways are talking double the time because of the additional validation. I think there is still room for improvement, but it is running now for me as follows:

if(equals(outputs('CheckUser')?['body/mail'],null),null,outputs('CheckUser')?['body/mail'])

Any thoughts/improvments?

[ekarim2020]

I will give this some more thought. For now you can try:

(1) Use filter array action on the results (value) returns by (1) above to to filter out bad rows (eg. Owner contact)

Your IF expression wont then need to check for null email addresses.

Inside the Apply to each loop:

(3) Get items action - can you add any filter queries to select only the items you need in order to reduce the data set?

Let me know how you get go.

Ellis

[SirHC]

The problem with the source data and the owner contact field is that this is a free text field and contains wrong or old email addresses, and sometimes just Text. Cleanup in the source is ongoing but far from done.

Therefore we have to validate every Owner Contact value.

[SirHC]

Since the source file has about 3000+ records and probably only about 300 changed per day I'll see if I can do a comparison from the last two files and only take the changed (300) to process against sharepoint.

just not sure yet if this would be possible (or make sense) in Power Automate or with some other scripts (powershell) before running this flow.

the new job takes probably 3 instead of 1.5 hours. and since 90% is unchanged there is no need to process the whole file.

[ekarim2020]

Can you add any data validation to the Excel file to reduce the amount of work needed to filter out bad addresses?

For example:

Email Address Format Validation in Excel & Google Sheets

https://www.automateexcel.com/how-to/email-validation/

Ellis

[SirHC]

not feasible. I can get rid of the junk, but when an email address looks like it should "firstname.name@company.com" I still don't know if this is an active user, meaning whether it can be resolved or not. at a certain point in time I need to validate against a directory.

[ekarim2020]

You could run a scheduled flow, say at midnight every night that reads the Excel files, searches for the email address in the AAD , then updates a new column in Excel (called ValidEmail) with valid or invalid.

Then your main flow could filter the Excel rows for ValidEmail = valid - and not bother searching for the email address in AAD. This would make the flow run much faster.

Ellis

[SirHC]

hmmm, having them separated in two jobs would not change the total amount of time, so I do not see the benefit.

I'm checking if we can get from the source system an additional field "Modified" so we can easily just take all items modified within the last 24 hours since the last run. this would bring the job down to a few minutes.

[ekarim2020]

The idea was to see if it is possible to run some of the time consuming tasks or preparation tasks out of hours. Then during office your flow will have a smaller dataset to process.

It's an excellent approach to get better data from the source!

Ellis

[SirHC]

ahhh. That job can run every night at midnight for example. sot it should not be an issue if it takes 1 or 3 hours. 

but when we have a modified date in the source we have the benefit to run this job ad hoc during the day if needed - just in a few minutes.

there is just one other issue I have. Records can be deleted in the source. for now my job is only creating and updating items in Sharepoint from Excel. I'm currently struggling to remove Sharepoint items which are no longer in the excel file.

[ekarim2020]

Can you enable Concurrency on the Apply to each loop and see of that improves performance:

Ellis
____________________________________
If I have answered your question, please mark the post as Solved.
If you like my response, please give it a Thumbs Up.

[SirHC]

I tried that. Now it takes even longer. But I'm not sure if this is the cause for the time increase. I might have changed other things as well. Changing from manual trigger to scheduled, should not be the issue.

Is there a way to analyse what step takes how long and why? I mean I can see that reading the 3500 lines from excel takes about a minute and that the apply each step takes now 8 instead of 2 hours. but how can I get more detailed information about the process?

[ekarim2020]

It was worth a try. Can you post the current flow you are using. There may be some simple step we can take to speed up the flow. The Get Items and CheckUser actions are going to be the bottlenecks for the flow.

Based on some older screenshots of your flow, is it possible to move the Get items action to the Yes branch of the condition? What is the filter query being used in the Get items?

Ellis

[SirHC]

Can't move that because of some dependencies:

1B: - Pagination set to 5000

2B: Settings currently set to "Concurrency Controll" ON, Degree of Parallelism 20.

But this seams not to work, even get worse?

2C: Filter Query: CI_Name Equals "items('Apply_to_each')?['CI_Name']"

and Top Count = 1

2E: FinalUserName Inputs: "if(equals(outputs('CheckUser')?['body/mail'],null),null,outputs('CheckUser')?['body/mail'])"

2F: Condition:  "length(outputs('Get_items')?['body/value'])" is not equal to 0

Hope you can see the mistake. Something is not correct.