Requirement: Export events in calendar into Excel Online using Power Automate.
Create a MS flow that will export all the events from the calendar in outlook to Excel Online. In the organization, there are a lot of events like Birthdays, Vacation, meetings, sick leave, etc., which are added to the timesheet, then export all these events to Excel. Once it is exported user can send it to the manager.
1.Create Excel Sheet in SharePoint Online
Create an Excel sheet to store the Event details. Then add the below columns to the Excel sheet of Events:
- Event
- Start Time
- End Time
- Body
- Hour
Once your Excel sheet is ready you can save it in SharePoint Online or Onedrive.
2: Create a Flow
To Create a flow, log in to Power Automate, and then click on Create -> Instant cloud Flow.
Now provide the Flow name, and choose ‘Manually triggered Flow‘. Then click on Create.
3: Get the calendar Events for a Particular month
Now get all the events for a particular month, so click on the Next step and then select ‘Get calendar view of events(V3)‘ action.
Next, provide the Calendar id, start time, and end time, from which is to fetch the events created in the calendar.
4: Initialize variable start time and end time
In this step, initialize two variables for start time and end time, later use these two variables to get the total hour of events. So, click on the Next step, and then select Initialize variable action, then provide the variable name and type as integer like below:
5: Apply to each event
Now click on the Next step and then select Apply to each action, then in output field provide the value- Get calendar view of events(V3) from dynamic content.
6: Convert the start time and end time to integer
Now to convert the start time and end time into integers in Power Automate.
So, click on Add an action inside apply to each, and then select Set variable action, then select the name from the drop-down is Start Time, and in value write the below expression:
ticks(items('Apply_to_each')?['start'])Similarly set the variable for the end time and then in value write the below expression:
ticks(items('Apply_to_each')?['end'])7: Get the Hours Event will run
To calculate the total hour the event will run, click on the Next step, and then select Compose action inside Apply to each. In Input write the below expression:
div(div(mul(sub(variables('End Time'),variables('Start Time')),100),1000000000),3600)
8: Export events to excel table
In this step, export the events to an excel table, so click on the add an action inside Apply to each select ‘Add a row into a table‘-Excel online action.
Now provide the location, Document library, File, and Table of excel file. then map the value with the excel column. In Start time and End time write the below expression respectively.
Start time -
formatDateTime(item()['start'],'d.M.yy hh:mm tt')
End time -
formatDateTime(item()['end'],'d.M.yy hh:mm tt')
In Hour Field, select the output of the compose action from dynamic content.
9: Run the Flow
To run the flow manually, click on Save and then test the Flow. The Flow ran successfully.
In calendar, there are two events, One is a Vacation and TEST INVITE . So these two events are exported to an excel table.
