cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SirHC
Helper I
Helper I

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
SirHC
Helper I
Helper I

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:

 

2021-11-18_09-36-01.png

 

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

 

Any thoughts/improvments?

 

View solution in original post

22 REPLIES 22
ekarim2020
Super User
Super User

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.

2021-11-17_16-48-25.png

 

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):

 

2021-11-17_16-37-39.png

 

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

2021-11-17_16-20-47.png

 

2021-11-17_16-20-26.png

 

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:

2021-11-17_16-40-07.png2021-11-17_16-38-23.png2021-11-17_16-39-03.png

 

Example run-time for a "bad user":

2021-11-17_16-46-29.png

 

2021-11-17_16-58-53.png

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
Helper I
Helper I

@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

 

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:

 

2021-11-17_17-59-37.png

 

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
Helper I
Helper I

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.

 

2021-11-17_23-05-32.png

This short gif animation should help:

2021-11-17_22-37-02.gif


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
Helper I
Helper I

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:

 

2021-11-18_09-36-01.png

 

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

 

Any thoughts/improvments?

 

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)

2021-11-18_09-02-21.png

 

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

 

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
Helper I
Helper I

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.

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
Helper I
Helper I

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.

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
Helper I
Helper I

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.

 

 

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
Helper I
Helper I

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.

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

2021-11-21_21-38-26.png2021-11-21_21-39-15.png

 


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
Helper I
Helper I

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?

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?

2021-11-23_08-41-36.png

Ellis

Can't move that because of some dependencies:

 

printscreen1.png

 

1B: - Pagination set to 5000

 

printscreen2.png

 

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

 

printscreen3.png

 

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

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,098)