Power Automate: Export To Excel With Dynamic Table & Columns Creation

Hello Friends,

Welcome back with another post on Power Automate. Today, we will learn how create a table in excel dynamically along with the columns where number of columns are not predefined. The export data to excel. The outcome of this post will be-

1. 
2. 

Let's start-

1. First of all, we need a SharePoint list. Below is the list, I had created. It has 8 columns.
2. 
3. Now, the objective is that I wish to export any combination of columns-
1. ID, Title, First Name, Last Name 
2. ID, First Name, Date Of Joining, Salary, Designation
3. ID, Full Name (First Name + Last Name), Designation
4. As we can see in above examples, the number of column as well as the columns itself are not fixed. Till now, we were creating a defined template which is having a table with columns and using it to export the data. This approach will not work here.
5. Microsoft had given solution for this problem as well. Let's begin-
6. Create a document library in Site Assets or the Documents library. Let say "ExcelFiles".
1. 
7. Now create a blank excel file and save it with name "EmployeesData.xlsx".
1. 
8. Upload this file in "ExcelFiles" folder.
1. 
9. Now, open the Power Automate maker portal and create a new Instant cloud flow with flow name as "POC-ExportExcelDynamic" and trigger condition "PowerApps".
1. 
10. Add a variable that will interact with PowerApps to get the column names.
1. 
11. Save the flow and keep saving the flow at regular interval during the flow writing.
12.  Now, to create the table in excel dynamically, we should know the number of columns that are going to be created and based upon the count, the Excel cell address. As we know, that excel works upon cell address. Columns are defined as A, B, C, .... while rows are defined as 1, 2, 3, ...
13. So, for the first row the first cell address is A1, second cell address is B1 and so on.
14. Similarly, for the second row the first cell address is A2, second cell address is B2 and so on.
15. Similarly, table is also dependent upon cells. For a 3 column table, the header address will be A1 -> C1. For a 4 column table, it will be A1 -> D1.
16. However, there is no direct method to get the respective address of cell using the count. Here, we have to apply some trick. We will use an excel file to get the series of the address based upon count. Excel has provided "ADDRESS" function. Here is the excel.
1. 
17. We have to create a JSON and will use the same. Here is the JSON.

1. [
     {
       "ColumnNo": 1,
       "ColumnAddress": "A1"
     },
     {
       "ColumnNo": 2,
       "ColumnAddress": "B1"
     },
     {
       "ColumnNo": 3,
       "ColumnAddress": "C1"
     },
     {
       "ColumnNo": 4,
       "ColumnAddress": "D1"
     },
     {
       "ColumnNo": 5,
       "ColumnAddress": "E1"
     },
     {
       "ColumnNo": 6,
       "ColumnAddress": "F1"
     },
     {
       "ColumnNo": 7,
       "ColumnAddress": "G1"
     },
     {
       "ColumnNo": 8,
       "ColumnAddress": "H1"
     },
     {
       "ColumnNo": 9,
       "ColumnAddress": "I1"
     },
     {
       "ColumnNo": 10,
       "ColumnAddress": "J1"
     },
     {
       "ColumnNo": 11,
       "ColumnAddress": "K1"
     },
     {
       "ColumnNo": 12,
       "ColumnAddress": "L1"
     },
     {
       "ColumnNo": 13,
       "ColumnAddress": "M1"
     },
     {
       "ColumnNo": 14,
       "ColumnAddress": "N1"
     },
     {
       "ColumnNo": 15,
       "ColumnAddress": "O1"
     },
     {
       "ColumnNo": 16,
       "ColumnAddress": "P1"
     },
     {
       "ColumnNo": 17,
       "ColumnAddress": "Q1"
     },
     {
       "ColumnNo": 18,
       "ColumnAddress": "R1"
     },
     {
       "ColumnNo": 19,
       "ColumnAddress": "S1"
     },
     {
       "ColumnNo": 20,
       "ColumnAddress": "T1"
     },
     {
       "ColumnNo": 21,
       "ColumnAddress": "U1"
     },
     {
       "ColumnNo": 22,
       "ColumnAddress": "V1"
     },
     {
       "ColumnNo": 23,
       "ColumnAddress": "W1"
     },
     {
       "ColumnNo": 24,
       "ColumnAddress": "X1"
     },
     {
       "ColumnNo": 25,
       "ColumnAddress": "Y1"
     },
     {
       "ColumnNo": 26,
       "ColumnAddress": "Z1"
     },
     {
       "ColumnNo": 27,
       "ColumnAddress": "AA1"
     },
     {
       "ColumnNo": 28,
       "ColumnAddress": "AB1"
     }
   ]
   

18. You may create as many as column numbers you want. Ideally, keep it "Total Number of Columns in list + 10" so that in case, if one or 2 columns got added later on in the list, it will not impact your flow.
19. Now, add an action called Parse JSON in flow and paste this JSON in Content as well as use this JSN to create schema "Generate from sample".
1. 
2. 
20. Now, we have the columns list that were passed through PowerApps and we have the mapping structure of Column Number vs Cell Address.
21. Add another action called "Filter array".
22. Here we will filter the JSON to find the corresponding cell address row (Column Address property in JSON) using the count of columns passed through PowerApps.
1. 
23. It will give us the item which is having ColumnNo property equals to the total number of columns received from PowerApps.
24. Now, we will fetch the ColumnAddress property from this output. So, add another called "Compose".
1. 
25. The first part is completed. Now, add an action called "Copy file". We will create a copy of the excel template.
1. 
26. Remember to select "Copy with a new name" option in dropdown which is asking the further course of action "If another file is already there".
1. 
27. Now, here the climax part of the flow. Microsoft have given a unique feature to create table on the fly. Add an action "Create table".
1. 
28. Provide the input as shown below. The starting range is fixed to A1, followed by a colon ":". The table range can be "Absolute", "Partial Relative" or "Relative". Make sure, whatever you are choosing, both cell address must be in same format. Here, I had chosen, "Relative"
29. You may give the table name as per your wish. I had given "SearchResults". You may define a variable for the Table Name as well in the beginning because, this table name is going to be used at two places, therefore, to avoid any misspell, you may use variable.
1. 
30. This was the second part.
31. The last part is adding items to table.
32. Add an action called "Get Items".
1. 
33. Next, we will use the "Select" to transform the data as per our requirement. For an example, if we wish to add another column "Full Name", we can do here by using "concat" function. Remember, the name of column at left side in mapping must be exactly the same as we are expecting to get receive from PowerApps.
34. So, add "Select" action.
1. 
35. I had used "concat" function to join First Name & Last Name. Also, I used "formatDateTime" function to apply formating upon Date Of Joining. 
36. Now, use "Apply to each" action to add this data to excel table.
37. Add an action "Apply to each".
1. 
38. Now, add "Add row into a table" action inside this "Apply to each" action.
1. 
39. It's all done.
40. Now, If you wish to send this excel as attachment or send the link of the file, you can add further actions that will fulfil the requirements. These all, we have already discussed in our previous posts.
41. Save the flow and test it.
42. Let's check for our first set of columns mentioned in the beginning of the post. Remember, column names should be separated either by comma or semicolon and should not have leading or trailing spaces.
1. ID,Title,First Name,Last Name [Correct Format]
2. ID;Title;First Name;Last Name [Correct Format]
3. ID, Title,First Name , Last Name [In-Correct Format]
43. Click on "Test" >> "Manually" >> "Test". It will show the Run window and asking for Column names.
44. Give the input as "ID,Title,First Name,Last Name" and click on Run.
1. 
45. Wait for the flow to complete it's execution. It got succeeded.
1. 
46. Check the SharePoint library. The file got created with name as "EmployeesData1.xlsx".
1. 
47. Here we go. The is there.
1. 
48. Now let's try with custom column 
1. ID,Full Name,Designation,Date Of Joining,Salary,Experience
49. Here we go. This also tested successfully.
1. 
50. This is how, you can create Excel Tables dynamically with any number of columns. However, you can create the file on the runtime but for that, you need to include the OneDrive connection. Ultimately, you are creating a file then copying it. Means one more step. Therefore, we avoided that. Keeping a blank file is far better than to include one more connection and adding one more step.

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 !