Power Automate: Export To Excel (SharePoint)

Hello Friends,

Welcome back with another post on Power Automate. Last year, we have learned about Export To Excel Using Power Automate where we have used OneDrive to store the excel template. Recently, someone asked me provide the same feature without using OneDrive. In short, their requirement was- whenever an item (request) is created in SharePoint list, the information is to be emailed in the form of Excel file to the user and the same excel file needs to be stored in a document library for future reference. Let's begin-

1. Let's create a SharePoint list "TestList". It is having "Title" column by default.
2. 
3. Below is the design of Excel template. It is having a table "Table1" with 2 columns "ID" & "Title".
4. 
5. Below is the structure of document library "ExcelLibrary" where we will save the template and the files.
6. The root library is "ExcelLibrary". It is having 2 nested folders -
1. Template - It will hold the Excel template.
2. DataFiles - It will be used to save the final excel files for each request.
7. 
8. Upload the Excel template in "ExcelLibrary" >> "Template" folder as shown in above screenshot.
9. Now, we will write the Power Automate Flow. Open the Power Automate Portal and click on "+ Create".
10. Select "Automated cloud flow". Give flow a suitable name and choose the flow trigger as "When an item is created (SharePoint)".
11. 
12. Select the Site Address and List Name. Here list name will be "TestList".
13. 
14. Now, we will copy the excel template from Template folder to DataFiles folder. For this, we will choose "Copy file (SharePoint)" action.
15. 
16. Select the Current Site Address, File to Copy (the excel template file), Destination Site Address (it will remain same as we are copying to same site), Destination Folder (ExcelLibrary >> DataFiles).
17. Now the important point here is to select the course of action if another file is already there with same name. Here we will choose to Copy the file with new name. Basically, the flow will append an incremental number ahead to the file name and save. It will save us from replacing the existing file of another request.
18. 
19. Save the flow and keep saving the flow at regular interval in order to save your precious efforts.
20. Now, add another action to add row in excel file. For this choose the action name "Add a row into a table (Excel Online (Business))".
21. 
22. Select the Location, Document Library.
23. For File, select the "Id" property of "Copy file" action from dynamic expression.
24. 
25. For Table, it will not show any table name because, you are providing the file dynamically. Therefore, choose "Enter custom value" and input "Table1". If you remember, you had added a table in Excel Template, if you check the same of this table, you will find it Table1. Otherwise, whatever be the table name, you have to put in here.
26. Now, there will be another filed get displayed called "Row".
27. As, all the values are dynamic in nature, therefore in design time, power automate is unable to fetch the schema of the table as well. Therefore, it allows us to provide the data to be inserted in JSON format.
28. 

29. { 
      "ID": @{triggerOutputs()?['body/ID']},
      "Title": "@{triggerOutputs()?['body/Title']}"
    }
    

30. The next step is to get the content of this file and send it through Email. Before that, we will add a delay of around 1 minute so that the writing of data gets completed in excel file.
31. 
32. Now, add the Get file content (SharePoint).
33. 
34. Select the Site Address. File Identifier will again picked from Copy file "Id" property.
35. 
36. Now, send an email using "Send an email (V2) (Office 365 Outlook)" action.  
37. Here, we can give the file name as per our wish. I had created a combination of "Created By Display Name" (replaced spaces by Underscore), suffixed by ItemID.

38. concat(replace(triggerOutputs()?['body/Author/DisplayName'],' ','_'),'_',triggerOutputs()?['body/ID'],'.xlsx')
    

39. 
40. Now, the attachment content will be mapped with the File Content property of Get file content action.
41. 
42. Now the next step is to rename the copied file in DataFiles folder with same name we used in attachment. But that is not so easy because, the file copied and written content in it gets locked as we were editing (adding items in that file) the file. Therefore, the file will not get renamed.
43. For this, we have to wait to get it unlocked. Here we will use "Do until" action. Let add the steps for it-
44. Add "Initialize variable" action -
1. Name: blnIsFileLocked
2. Type: Boolean
3. Value: true
45. 
46. Add "Do until" action -
47. Change the limits as shown below.
48. 
49. Add "Delay" action and set count to 1 and unit to Minute.
50. 
51. Now, we will try to rename the file. If the file is unlocked, it will get renamed otherwise, it will throw error. We have to handle the error carefully. For this, you may use the property "Configure run after".
52. Add "Send an HTTP request to SharePoint" action.
53. 
54. Fill up the required details. Here, we are using "ItemId" attribute to fetch the desired file.
55. For Body section, again it will take JSON and the FileLeafRef property is set to the file name we had used earlier (without .xlsx).

56. {'__metadata':{'type':'SP.Data.ExcelLibraryItem'},'FileLeafRef':'@{concat(replace(triggerOutputs()?['body/Author/DisplayName'],' ','_'),'_',triggerOutputs()?['body/ID'])}'}
    

57. 
58. Add "Set variable" action and set the variable "blnIsFileLocked" to false.
59. 
60. Now, we will set the "Configure run after" property for this action. Click on ellipses (marked in above screenshot) and choose "Configure run after".
61. 
62. It will show the settings as above screenshot. By default, "is successful" is selected. This is what, we require. This action should only execute if the file get renamed means the HTTP request get executed successfully. Click on "Done".
63. 
64. Now, add "Get file properties (SharePoint)" action to get the properties of the file. You may skip this step. I am adding this step to send another mail with the link of the excel file for future reference. For each step you have to set the "Configure run after" property (for "is successful")
65. 
66. 
67. 
68. The last step is to add action "Send an email (V2)".
69. 
70. The Item Link is set as-

71. concat('<a href="',split(outputs('Get_file_properties')?['body/{Link}'],'?')[0],'">Item Link</a>')
    

72. Set "Configure run after" for "is successful".
73. At last, you may add "Terminate" action with Status as "Succeeded".
74. 
75. Let's start testing.
76. Add an item in TestList.
77. 
78. As checked, file has been created in DataFiles folder with content as below.
79. 
80. 
81. Now, we are waiting to get file renamed.
82. Finally, the workflow got completed successfully. Let's see, if the file name has been changed. Yes, it's done.
83. 
84. And the mails, I received are-
85. 
86. 
87. This way, you can achieve the functionality.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

Power Automate: TimeOut in Approval Workflow

Hello Friends,

Welcome back with another post on Power Automate. Sometimes, it happens in real world that we trigger an approval process and sometimes it gets halted due to non-availability of any approver or due to some other reasons. In such scenarios, the workflows instance keeps running. In such cases, what the requirement is that these instances should get terminated after a certain interval. By default, there is a timeout of 30 days for trial licenses. But for paid licenses, it keeps running. Therefore, we need to apply some workaround during the workflow creation to set a timeout of the instance and if the workflow instance reaches to that timeout before getting completed, it should get terminated. So, let's start - 

1. Suppose we have a SharePoint list named "ZTimeOut".
2. 
3. We have to apply the timeout feature upon the item approval process. For this, we have to create a Power Automate flow upon this list.
4. So, login to Power Automate portal and click on "+ Create". Choose ""Automated cloud flow".
5. Give flow a suitable name and choose the trigger option as "When an item is created". click on "Create".
6. 
7. It will create a new workflow. Select the Site Address and the List Name.
8. 
9. Now, in order to apply the timeout, we will use the "Delay until" action. It needs a timestamp till when it has to wait.
10. So, for that, we will define a variable "strTimeOut" of type string and set it's value as UTCNOW + 5 minutes. You may pick the time out duration (i.e. 5 minutes) dynamically as well from a master list and it could be Seconds / Minutes / Days / Months. You need to define one dimension either Seconds or Minutes or Days or Months. Then convert it accordingly in workflow.
11. Now, initialize the variable "strTimeOut" and set the Value as below-
12. 
13. Now save the workflow so that the workflow should get saved on cloud environment.
14. Now, we will define another variable "blnIsProcessCompleted" with default value as false; which will be used to set as true once our approval process gets completed. In case, our approval process doesn't get completed before timeout, then based upon it's value, our code will terminate the workflow instance.
15. 
16. Now, add "Start and wait for approval" action.
17. 
18. At this stage we will add another action "Delay until" in parallel which will act as Timout feature for our flow.
19. For this, click on + icon and choose "Add a parallel branch".
20. 
21. Now search for "Delay until" action
22. 
23. Give the variable "strTimeOut" as Timestamp input.
24. 
25. Basically, we had created 2 branches. One branch will perform our approval process activity as usual. While other parallel branch "Dealy until" will wait for the stipullated time provided by variable.
26. This action will wait for the datetime provided by strTimeOut in parallel.
27. Now add a Condition action to check if the variable blnIsProcessCompleted is true or false.
28. 
29. If false that means the workflow is still running so we will terminate the workflow. Before terminating, if you wish to inform user then you may add "Send an email (V2)" action. If you wish to update the item then you may use "Update an item" or "Send an HTTP request to SharePoint" action. Lastly, we will use Terminate action to terminate the workflow.
30. 
31. 
32. This completes our TimeOut portion. Now come back to Approval Process. We had added one level of approval. Based upon outcome, you may write your own logic. I am adding one more level to show how the timeout works.
33. Add one more "Start and wait for approval" action in the approval branch.
34. 
35. Again, you may write your own logic based upon the outcome of this approval. Once all approval gets completed, you need to set "blnIsProcessCompleted" to true and then add the Terminate action.
36. 
37. Save the workflow and now it's time to test it. For this, add one item in list.
38. I had added one item and the workflow gets trigger. It sent me the first level approval notification.
39. 
40. I had approved it.
41. It triggered the level 2 approval notification
42. 
43. I had approved it also.
44. Now, if we check the workflow, it shows that it has been executed successfully and the approval process get completed with last Termination action execution as well.
45. 
46. However, if we see that the "Delay until" is still showing in running mode. Let's wait for 5 minutes (out TimeOut). It's almost 7 minutes passed. Let's check the status.
47. 
48. As we can see, the Delay until got cancelled and further steps also didn't executed. Means, it is working fine if the approval process goes smoothly. Now, what if any level doesn't take any action. Let see. I will initiate the process again. Approver 1 will approve the request. Approver 2 will not take any action until timeout.
49. As we can see from below screenshot, Approver 1 has completed its request. Approver 2 haven't taken any action upon the request. So, we are waiting to get the request timeout.
50. 
51. As we can see that the workflow reached to the timeout and it has been terminated by the parallel branch.
52. 
53. The auto cancel mail is also received.
54. 
55. The complete flow is-
56. 
57. Important- You have noticed that in case of timeout, the approval instance for Level 2 still showing as waiting for response. This is because, as of now, there is no feature available by Microsoft to cancel the approval instance. However, it will not make any impact because, if the workflow instance has itself terminated and if the level 2 approver takes any action upon the request, it will not be entertained by the workflow as the instance has already been terminated.
58. This way, we can implement the TimeOut feature in Power Automate.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !