Power Automate: Flows Timeout Issue Through PowerApps

Hello Friends,

Welcome back with another post on Power Automate. Today, we will discuss about the TimeOut issue when calling a flow from PowerApps. Sometimes, you have observed that the flow you had written for some processing, takes longer time to get it complete. When, such workflows are called from PowerApps, we get a timeout issue on PowerApps after 2 minutes. However, if you had observed, that the flow is still running. The reason is the service which acts as a communication bridge between PowerApps & Power Automate for Request/Response drops the connection after 2 minutes. So, how to handle this situation?

As of now, there is no solution provided by Microsoft to handle this situation. However, we can do a workaround for that. Let's start.

1. Create a "Instant cloud flow" in Power Automate. Give name as "POC-FlowTimeOutIssue" and the trigger type as "PowerApps".
2. 
3. Click on Create to create the flow. Add to input parameters (Initialize variable) named-
1. DisplayName
2. Email
4. The data type of these parameters will be string and the Value will be "Ask in PowerApps".
5. 
6. Now add a delay action and set the delay for 3 minutes.
7. 
8. Now add an action "Respond to a PowerApps or flow".
9. 
10. Add the output as below-
11. 
12. Save the flow and test it. It will ask you to provide the values for input parameters. Provide the Display Name & Email ID and click on Run flow. Follow the steps to the flow executed.
13. 
14. You will find that the flow execution got failed. It will throw one error "The execution of template action 'Respond_to_a_PowerApp_or_flow' is failed: the client application timed out waiting for a response from service. This means that workflow took longer to respond than the alloted timeout value. The connection maintained between the client application and service will be closed and client application will get an HTTP status code 504 Gateway Timeout.". But no worries because it was a Test, not executed from PowerApps.
15. 
16. Now, create a PowerApps and add a button.
17. 
18. Now from left navigation menu, click on Power Automate Icon >> + Add flow >> Search flow >> POC-FlowTimeOutIssue
19. 
20. Now, write below code on "OnSelect" property of the button.
21. 
22. Save the app and play it. click on the button to execute the flow.
23. 
24. You will see the 5 dots scrolling at the top as well as the button got disabled. After 2 minutes, you will find that the suddenly got enabled and the scrolling of dots stopped. This is because, the flow got timed out. We haven't got any visible error on screen because, we are saving the output in a variable. Let's stop playing it. and check the app. We will find an error in app.
25. 
26. If we see the flow running status, we found it again failed. The Error Details are-
27. 
28. It is saying the connection between client application and service got closed.
29. Now, what is the solution?
30. Here, we have 2 solutions-
1. If there is only data processing and no intimation to user.
2. If there is processing the data and intimate to user.
31. For both solutions, you have to follow below process-
32. In PowerApps, add one condition on "OnSelect" function of button just after the existing code.

1. If(IsBlankOrError(vrOutPut),Notify("Your request has been timed out, however, it is already processing at the backend.",NotificationType.Error),vrOutPut);
   

33. 
34. In case, if user needs to be intimated, then add below code

1. If(IsBlankOrError(vrOutPut),Notify("Your request has been timed out, however, it is already processing at the backend. You will receive an email.",NotificationType.Error),vrOutPut);
   

35. Now come back to flow. In case of intimation to user, add "Send an email (V2)" action at the end of flow.
36. 
37. Now, click on the 3 dots of this action and choose "Configure run after".
38. 
39. Select the options as shown below and click on Done
40. 
41. Save the flow, click on test and enjoy.
42. 
43. With this, what will happen is that whatever output you want to give to user, it will be sent through mail.
44. 
45. For example, user has clicked on button to export data to excel. Now, if the data is large, it will surely get timeout. then with this method, you can provide the Excel link back to user of the file itself as an attachment to user over email.
46. The best part is we had configured it to not run if the Response action got executed successfully. It means it will trigger if the Response action got failed / timed out / skipped.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !

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 !