Power Automate: Play With Dates

1. If The Current Date is 16-April-2021T12:17:10.1808189Z then
1. 
   
   
   
   
2. 
   
   
   
   
   
   
2. Add month to a date (Use addToTime expression)
1. addToTime(utcNow(),1,'month')
2. If utcNow() is 16-April-2021T12:17:10.2433164Z then above expression will return 16-May-2021T12:17:10.2433164Z
3. 
   
   
   
   
4. 
   
   
   
   
3. Jump to 1st day of next month (Use startOfMonth expression)
1. startOfMonth(addToTime(utcNow(),1,'month'))
2. If utcNow() is 21-April-2021T12:17:10.2433164Z then above expression will return 01-May-2021T00:00:00.0000000Z
3. 
   
   
   
   
4. 
   
   
   
   
4. Jump to 1st day of current month (Use startOfMonth expression)
1. startOfMonth(utcNow())
2. If utcNow() is 21-April-2021T12:17:10.2433164Z then above expression will return 01-April-2021T00:00:00.0000000Z
3. 
   
   
   
   
4. 
   
   
   
   
5. Get last date of current month (Use subtractFromTime expression)
1. subtractFromTime(startOfMonth(addToTime(utcNow(),1,'month')),1,'day')
2. If utcNow() is 21-April-2021T12:17:10.2433164Z then above expression will return 30-April-2021T00:00:00.0000000Z
3. 
   
   
   
   
4. 
   
   
   
   
6. Get last date of previous month (Use subtractFromTime expression)
1. subtractFromTime(startOfMonth(utcNow()),1,'day')
2. If utcNow() is 21-April-2021T12:17:10.2433164Z then above expression will return 31-March-2021T00:00:00.0000000Z
3. 
   
   
   
   
4. 
   
   
7. Get start of today (Use startofDay expression)
1. startOfDay(utcNow())
2. If utcNow() is 16-April-2021T18:05:03.1234567Z then above expression will return 16-April-2021T00:00:00.000000Z
3. 
   
   
   
   
4. 
   
   
   
   
8. Date:- 17-Apr-2021
9. Get Current Year (Use formatDateTime expression)
1. formatDateTime(utcNow(),'yyyy')
2. 
   
   
   
   
3. 

   
   
   
10. Get Current Month (Full Name) (Use formatDateTime expression)
1. formatDateTime(utcNow(),'MMMM')
2. 
   
   
   
   
3. 
   
   
   
   
11. Get Current Month (Abbreviation) (Use formatDateTime expression)
1. formatDateTime(utcNow(),'MMM')
2. 
   
   
   
   
3. 
   
   
   
   
12. Get Current Month (Number) (Use formatDateTime expression)
1. formatDateTime(utcNow(),'MM')
2. 
   
   
   
   
3. 
   
   
   
   
13. Get Current Day Of Month (Use formatDateTime expression)
1. formatDateTime(utcNow(),'dd')
2. 
   
   
3. 
   
   
   
   
   
   
14. Get Current Day Of Year (Use dayOfYear expression)
1. dayOfYear(utcNow())
2. It returns the day of year starting from 1 Jan
3. 
   
   
   
   
4. 
   
   
   
   
15. Get Day Of Month (Use dayOfMonth expression)
1. dayOfMonth(utcNow())
2. 
   
   
   
   
3. 
   
   
   
   
16. Get Day Of Week (Use dayOfWeek expression)
1. dayOfWeek(utcNow())
2. It takes Sunday as 0, Monday as 1, Tuesday as 2, Wednesday as 3, Thursday as 4, Friday as 5, Saturday as 6
3. 
   
   
   
   
4. 
   
   
   
   
17. Get Day Of Week (Name) (Use formatDateTime expression)
1. formatDateTime(utcNow(),'dddd')
2. 
   
   
   
   
3. 
   
   
   
   
18. Get First Day Of Week (Complete Date) (Use subtractFromTime with dayOfWeek expression)
1. subtractFromTime(utcNow(), dayOfWeek(utcNow()), 'Day')
2. 
   
   
   
   
3. 
   
   
   
   
19. Get First Day Of Week (Name Of Day) (Use formatDateTime with subtractFromTime with dayOfWeek expression)
1. formatDateTime(subtractFromTime(utcNow(), dayOfWeek(utcNow()), 'Day'), 'dddd')
2. 
   
   
   
   
3. 
   
   
   
   
4. If you want Monday as 1st day of week, the modify the above expression as any of the below-
1. formatDateTime(subtractFromTime(addDays(utcNow(), 1), dayOfWeek(utcNow()), 'Day'), 'dddd')
2. formatDateTime(subtractFromTime(utcNow(), sub(dayOfWeek(utcNow()), 1), 'Day'), 'dddd')
20. Will add more soon...

Export To Excel Using Power Automate

Hello Friends,

Welcome back with another post on Power Automate. Today, we will try to export SharePoint Online list data to Excel. Let's start-

1. First of all we need a SharePoint list with records that needs to be exported. I had created one list "ExportToExcelList" and added 3 records.
2. 
   
   
   
   
3. Now create an excel template with same columns and convert it into table and upload it to OneDrive. The access to OneDrive is mentioned in my previous post from Access OneDrive, steps 78-81. I created folder PowerApps >> Templates.
4. 
   
    
5. 
   
   
   
6. Now there are 3 ways to create a flow-
1. When an item is created
2. When an item is created or modified
3. Scheduled flow
7. I am creating a flow upon item create. Here, I will
1. Get the template
2. Check if template has rows
3. If yes, delete those rows
4. Write new rows
5. Send it my email
8. Let's do step by step-
9. Create a flow on "When an item is created". You may see how to create the flow on my post Create PDF Using Power Automate, steps 10 - 24.
10. Here we will use Flow name as "ExportToExcelOnItemCreate" and the list is "ExportToExcelList".
11. 
    
    
    
    
12. Now before moving ahead, there are 2 things-
1. You need to remove the entire content and rewrite
2. You need to append the new row 
13. In case you need to remove the entire content, follow the steps given below from 11 - 
14. Click on "+ New step" and choose "List rows present in table (Excel Online (Business))"
15. Set the Location, Document Library, File, Table.
16. 
    
    
    
    
17. Now check if rows are present in table. For this add a Condition and check on length.
18. 
    
    
    
    
19. If there are rows, delete them. For this, add and action in "If yes" condition and choose "Delete a row Excel Online (Business)".
20. Set Location, Document Library, File, Table, Key Column (mention column "Name" from the Excel Table), Key Value (choose column value "Name" from the Excel Table).
21. 
    
    
    
    
22. "Apply to each" action will get auto added as soon as you will choose Key Value.
23. Once all rows get deleted, move to next step. Now, the important point here is that Deletion/Insertion of rows in OneDrive Excel file is an Asynchronous process. Therefore, it is better to put a delay of sometime before moving to next step so that all async process gets completed.
24. Add a Delay action. for this click on "+ New step" and choose "Delay (Schedule)".
25. 
    
    
    
    
26. Now we will get items from our SharePoint list. I just added a basic filter query upon Name column that get only those items where it is not blank.
27. 
    
    
    
    
28. Add a condition to check if SharePoint list has items-
29. 
    
    
    
    
30. If items are available start writing into excel. For this, choose action "Add a row into a table (Excel Online (Business))" and fill in the information as below-
31. 
    
    
    
    
32. If you wish to add another delay for a minute, you can add in the Apply to each section as shown below (however it's purely optional. I added a delay of 30 seconds in below example)-
33. 
    
    
    
    
34. Once all items get written in excel, you can add another delay to get all async write process complete-
35. 
    
    
    
    
36. Now we will get the content of this excel file and send it as an attachment over email. to get the content choose action "Get file content (OneDrive for Business)"-
37. 
    
    
    
    
38. Select the Excel template file in "File"path box-
39. 
    
    
    
    
40. It's almost done. Now send this file over email as below-
41. 
    
    
    
    
42. It's all done. Now save the flow and put a new entry in SharePoint list.
43. 
    
    
    
    
44. Let's wait for the flow to run.
45. Wow! I got the mail with attachment-
46. 
    
    
    
47. 
    
    
    
    
48. Let's try with adding one more item.
49. 
    
    
    
    
50. The final list of items is-
51. 
    
    
    
    
52. Got mail for this transaction also-
53. 
    
    
    
    
54. As expected, the flow has replaced all the items with fresh list-
55. 
    
    
    
    
56. This way, we can export our SharePoint list items to excel and can send over email.
57. Additional feature that you can add is that you can save this excel in your SharePoint library also.
58. Add an action "Create file (Sharepoint)". Provide details, save it and Test the flow-
59. 
    
    
    
    
60. 
    
    
    
    
61. Let's wait for the result. In case, if the flow throws error of "File Content is null", just add action "Get File Content" (steps 36 - 39) just before the Create File action (step 58)and use this File content in creating file in SharePoint.
62. Meanwhile, I was writing the functionality, I got the mail of file getting generated. When I checked the SharePoint library, the file got created there also. Below are the screenshots-
63. 
    
    
    
    
64. 
    
    
    
    
65. 
    
    
    
    
66. So, this way, you can achieve the discussed functionality. Hope you enjoyed it.

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !