Auto-update Leave Request Response on Excel (Online) | Power Automate

Hello People!

In this article, I'll cover how you can automate the leave approval process through Power Automate. The technologies that seemed impossible years ago are a reality today. Power Automate uses these technologies to increase efficiency and improve the bottom line. This has made it the fastest-growing segment of the enterprise software market in the past few years.

With this simple flow, you can learn how you can release your force from repetitive boring tasks to higher-value activities and start focusing on designing numerous possibilities of automating.

Follow the steps mentioned below:

Step 1: Create an Excel (Online) Sheet & Microsoft Forms

Excel (Online)

Create a table and save the excel file.

Microsoft Forms

Create a Microsoft form and save it.

Create flow in Power Automate

Step 1: Add an action "When a new response is submitted"

1. Select the form name which you saved earlier from the drop-down menu.

Step 2: Add an action "Get response details"

1. Select the Form Id.
2. Every Response received has a unique Id. In the Response Id field, select "Response Id" from the Dynamic content. The Dynamic content bar will appear once you click on the Response Id field.

Step 3: Add an action "Add a new row into a table"

Once a Microsoft form response is received, a row into a table will be auto-updated.

1. Select the location of the file from the drop-down menu.
2. Select the document library from the drop-down menu.
3. Select the excel (online) file saved earlier (Step 1).
4. Select the table of the excel file (i.e Table 1) from the drop-down menu.
5. Map each field of the form as highlighted above in the screenshot. This is the mapping of the Microsoft form fields in the Excel table.
6. In the Leave status field, type "Hold". When a response is submitted the leave status field will be on hold, after receiving the approval response the leave status field will be auto-updated (Approve/Reject).

Step 4: Add an action "Start and wait for an approval"

This step will trigger the approval request to the assigned user.

1. Select "Approve/Reject - First to respond" from the drop-down menu in the Approval Type.
2. In the "Title" field, design the subject of using Dynamic contents.
3. In the "Assigned to" field, select the relevant user (approver) or mention the email address.
4. In the "Details" field, design the body from the Dynamic contents.
5. In the "Item link" field, select "Response Id" from the Dynamic contents.

Note: Email notification will be received by the user (approver) if the enable notifications is set to "Yes' in the "Show advanced options" tab.

Step 5: Add a "Condition" Step

1. Select the "Outcome" from the Dynamic content in the value field.
2. Type "Approve" as highlighted above. The purpose of hardcoding it is that if the outcome is Reject, the flow will be directed to "If no".

Step 6: Add the same actions in "If yes" and If no"

1. Add an action "Update a Row"

This action will update the outcome (approve/reject) in the leave status column in the excel file.

1. Select the location of the file from the drop-down menu.
2. Select the document library from the drop-down menu.
3. Select the excel (online) file saved earlier (Step 1).
4. Select the table of the excel file (i.e Table 1) from the drop-down menu.
5. In the key column select "Employee ID" since that will be unique. This action will search for the unique employee Id and update the leave status column based on the outcome (Approve/Reject).
6. Select key-value as "Employee ID".
7. Leave the other columns empty and add "Outcome" in the leave status column.

2. Add an action "Send an Email"

This step will send an email to the form submitter and update whether the leave request has been approved/rejected.

1. In the "To' field, mention the "Email ID" from the Dynamic content. This is the email Id of the submitter.
2. Design the subject and body of the email by using Dynamic content or you can also design it in HTML.
3. Now save and test the flow.

Test the Flow

1. Submit the form

2. Once the form is submitted, the excel file will be auto-updated with the relevant details, and the "Hold" text will be auto-filled in the Leave Status column. An email notification will also be sent to the approver and a response will be received through Power Automate.

3. Once the approver has responded to the request, the response will be auto-filled in the Leave Status column and an email notification will be sent to the submitter. The approver has an option to respond to the leave request through the desktop/mobile application of Power Automate.

Leave Status column auto-updated.

Email notification sent to the submitter.

Hope you learned from this article. Feel free to share any feedback.

Muhammad Ahmad Khan

ERP Consultant

[email protected]