Power Automate

Multiple Validations in SharePoint List & Excel

(2) Share

Report

Posted on by GeorgeSopra

Hi all,

I'm stuck on building a flow. I essentially need it to do the follow:

Form gets submitted
Puts data in SharePoint List
Validates the existence of submitted email address against email address in excel file
If YES - then it validates the Signature Authoirty level of that email address (this is in a separate column)
If SA equals the right level, an approval is sent

I'm stuck with the validation part of this process. Any help would be amazing. Thanks!

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Joseph_Fadero 670 Power Up Champ - 2025 on at

Like (0)

Report

Hi George,

Good day,

1. Trigger: When a Form is Submitted
Action: Use the "When a new response is submitted" trigger from the Microsoft Forms connector.
Action: Add a "Get response details" action to retrieve the form responses.
2. Add Data to SharePoint List
Action: Use the "Create item" action to add the form data to your SharePoint list.
3. Validate the Email Address Against Excel
Action: Use the "List rows present in a table" action from the Excel Online (Business) connector to get all rows from the Excel file where your email addresses are stored. Under the ODATA Filter you can filter the Email address column eq the email dynamic content coming from the form data OR you can, Add a "Filter array" action to filter the Excel rows where the email address matches the submitted email address.
In the Filter array condition, compare the email address from the form submission with the email addresses from the Excel table.
4. Validate the Signature Authority Level
Action: Add a "Condition" action to check if the email address exists in the filtered Excel rows.
Condition: If the email address exists, then extract the Signature Authority (SA) level from the relevant column.
Action: Add another "Condition" action within the "If Yes" branch of the previous condition to check if the SA level matches the required level.
5. Send an Approval Request
Action: If the SA level condition is true, use the "Start and wait for an approval" action to send an approval request to the necessary parties.
Action: If the condition is false, you might want to send an email or take another action to notify someone that the validation failed.

I have also included a video by Reza:Youtube Which can provide further insights on how to get that done.

Kindly feel free to like my response and mark as a solution if it fits your requirement.

Was this reply helpful? Yes No
GeorgeSopra 19 on at

Like (0)

Report

Thanks for the heads up Joseph.

So I've been implementing the suggested flow and seem to come across issues with the condition which I can't seem to find a workaround for. Any screenshots or suggestions on the code would be of great use to me. Thank you for your support.

Was this reply helpful? Yes No
Suggested answer

WillPage 2,307 Super User 2025 Season 2 on at

Like (0)

Report

Step 1: I have an excel file with a table inside that contains the email addresses:

Step 2: Here is a compose action that contains an email address. This could be dynamic content from wherever, e.g. form response field etc - it doesn't matter, this is just an example.

Step 3: Power Automate action "List rows present in a table". Select the file and table containing your email addresses. Add a Filter query like Email eq '<dynamic content>'. In my example the Excel table column header is Email and my dynamic content is the compose. Yours may be different, adjust as appropriate. Place the dynamic content in single quotes.

Step 4: Another compose with length(outputs('List_rows_present_in_a_table')?['body/value']) as the expression. You don't have to type all this out (you can though), just type length() in the expression editor, place the cursor between the brackets then switch to dynamic content tab of the expression editor and find 'value' in there, then click it.

When we run it, the result is 1 because the email is found.

Change to one that doesn't exist in the list:

Now the result is 0

You can use this in a condition where it's greater than 0, and put different actions in the Yes and No branch (greater than 0 then put approval in the No branch, or you can go equals 0 and put the approval in the Yes branch)

Was this reply helpful? Yes No
Suggested answer

Joseph_Fadero 670 Power Up Champ - 2025 on at

Like (0)

Report

Hi George,

Good day,

Thanks for your response,

Kindly find below a sample,

Once you set the ODATA filter for the excel to filter the email based on the email dynamic content gotten from the form, For the condition you can use Is Not equal to blank and that should solve your issue.

Kindly like my response and mark as a solution if it meets your requirement

Was this reply helpful? Yes No