In this Power Automate tutorial, we will see how to automatically get events into Excel Online using Power Automate. And also we will discuss Power Automate create calendar events from excel.
Power Automate get events into excel
Here we will see how to automatically export events into Excel Online using Power Automate.
We will create a Microsoft Flow that will export all the events from the calendar in outlook to Excel Online. For example in the organization, there are a lot of events like meetings, sick leave, etc., which are added to the timesheet, then we will export all these events to Excel. Once it is exported we can send it to the manager.
Steps For Power Automate get events into excel
Step 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.
Now Excel sheet is ready, we will move towards creating the Flow.
Step 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.
Step 3: Get the calendar Events for a Particular month
Now we will 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 we will fetch the events created in the calendar.
Step 4: Initialize variable start time and end time
In this step, we will initiable two variables for start time and end time, later we will 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:
Step 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.
Step 6: Convert the start time and end time to integer
Now we will see how 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 StartTime, 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'])
Step 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('StartTime')),100),1000000000),3600)
Also, read, Power Automate copy files
Step 8: Export events to excel table
In this step, we will 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.
Once your Flow is ready, you can run the Flow manually.
Step 9: Run the Flow
To run the flow manually, click on Save and then test the Flow. You can see your Flow ran successfully.
In My calendar, there are two events, One is a daily meeting and Christmas. So these two events are exported to an excel table.
This is an example of Power Automate get events into Excel.
Read Power Automate Rename File
Power Automate create calendar events from excel
Here we will see how to create calendar events from excel in Power Automate.
I have an Excel called Events, where events are stored, so we will create an event on Calendar in Outlook from Excel.
Steps for power automate create calendar event from excel
Here we will see the steps for Power Automate to create a calendar event from excel.
Step 1: Create an Excel
I have created an Excel sheet called Events, with the column listed below
- Events
- Start Time
- End Time
- Body
- Hour
- Start Date time
- End Date Time
Once you have a list of events in an excel sheet, let’s create a flow in Power Automate.
Step 2: Create a Flow
To create a Flow, log in to Power Automate, then click on Create and select ‘Instant Cloud Flow‘.
Next, provide the Flow name, then select ‘Manually triggered Flow‘. Then click on Create.
Step 2: Get all events from the Excel
To get all events in Excel, click on the next step and then select the ‘List rows present in a table‘ action. then provide the Location, Document library, File, and table of the Excel file.
Step 3: Create an event in the Calendar
Now we will create an event in the calendar, so, click on the Next step and then select ‘Create Event (V4)‘ action. Then set the calendar id and map the subject, start time, and end time with the excel value.
Then choose the time zone, which must be similar to the outlook calendar time zone. When you map the subject, automatically Apply to each action will create.
Once your Flow is ready, you can run the Flow manually.
Step 4: Run the Flow
Now to run the Flow click on Save and run the Flow manually, you can see your Flow ran successfully.
Now you can check the calendar, that event is created like below.
This is how Power Automate creates calendar events from excel.
You may like the following Power Automate tutorials:
- Microsoft flow send email based on create date
- Power Automate or Microsoft Flow check day of week
- Power Automate SharePoint Get Items Filter Query + 12 Examples
- How to get days of month in Power Automate
- Power Automate send email to SharePoint group
- Power Automate remove characters from a string
- Power Automate Array Variable + 15 Examples
- How to create a word document from a template in Power Automate
In this Power Automate tutorial, we saw an example of power automates get events into excel. And also we learned how Power Automate create calendar event from excel.
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com
What happens when you want to have your excel table updated?
When an event in the calendar is deleted, modified or a new event is created, will the table update?
Hi Hector could you please advise if you get how to update the excel online sheet I’m stuck in this for many days.
Thanks
Hi Bhawana,
thanks for this flow. Is there a way to do it for shared calendars too?
Right now in “Calendar-ID” I see ony my own calendar and my co-worker’s calendar that I have full access to.
Hi Bhawana,
Thank you for this article. It is very helpful.
I would like to trigger the workflow upon arrival of a new calendar event.
Is there a way to achieve this?
Hi, thanks for the instructions on setting this up. It is useful. Just a note that the Excel date mapped to Outlook Start and End Time fields begin at 0:00hr. How can I set the Time Start to have 9:00hr and Time End at 17:00hr for each of the dates?
Thanks
Hi Bhawana,
Thank you for this flow.
How to calculate minuits between two times?
I couldn’t figure out div formula in compose process.
hi ,
Did you get the solution for this by any chance ?
I am getting an error with String was not recognized as a valid datetime
In excel i am using [$-en-US]d/m/jj u:mm am/pm;@ (to get the same layout as in the example) can someone tell me how to set the format of the cells?
It is creating multiple entries of the same event (at least 7 entries) in outlook. How to avoid that?