How to Save Outlook Calendar event's data into SharePoint List

[hkmamun]

I need to create SharePoint list item from Outlooks event from PowerApps. The form has a Date Picker & a Button named "Sync Data". User will select date and click the "Sync Data" button then below functionality will be done

1. Get all Events from Outlook Calendar for Selected date .

2. For all events check the Event ID is already in the SharePoint list or not.

3. If Event ID is not in SharePoint list then create an item in SharePoint list with necessary data.

5. If events have no "Required Attendees" then create a list item for event Organizer.

6. If events have "Required Attendees" then create separate list item for Organizer and also separate list item for each attendee. ( one attendee one row).

My SharePoint list name is DailyActivities and column structure is given below

Column Name	Column Type	Notes	Column's name from Event
Title	Single line of text		subject
StartDate	DateTime		start
EndDate	DateTime		end
Description	Multi line of text		body (which is coming HTML)
Location	Choice		location
Participants	Person (allow multiple)		requiredAttendees
Organizer	Person (single)		organizer
ActivityFor	Person (single)	here the value will be Organizer and when there will be attendees then a each attendee

I have used below code to to collect events from Outlook Calendar but can not put date filtering

ClearCollect(CalendarEventsForSync,Sort(Office365Outlook.V4CalendarGetItems("Calendar").value,start,Descending));

Then remove the items from collection if Event ID is already in SharePoint List (DailyActivities)

RemoveIf(CalendarEventsForSync,id in DailyActivities.EventID);

I am stuck filtering data from Outlook Calendar by date selected, and save data in SharePoint list with upper conditions.

I appreciate your  urgent help. Its very urgent for me.

Thanks!

[v-bofeng-msft · Accepted solution]

Hi @hkmamun :

Do you want to add records from collection CalendarEventsForSync to DailyActivities?

Because the fields in the collection are stored as strings, the key is to convert the data type.

I'v made a test for your reference:

Add a button and set it's OnSelect property to:

ForAll(
    CalendarEventsForSync,
    Patch(
        DailyActivities,
        Defaults(DailyActivities),
        {
            Title: subject,
            StartDate: DateTimeValue(start),
            EndDate: DateTimeValue(end),
            Description: body,
            Location: {Value: location},
            Organizer: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & organizer,
                DisplayName: Office365Users.UserProfile(organizer).DisplayName,
                Email: organizer,
                JobTitle: "",
                Picture: ""
            }
        }
    )
)

Because of the robustness problem, once Office365Users.UserProfile(Result) cannot find the corresponding user, the program will report an error. I suggest you change the field to text type.If you insist, you can try the following code (unstable).

ForAll(
    CalendarEventsForSync,
    Patch(
        DailyActivities,
        Defaults(DailyActivities),
        {
            Title: subject,
            StartDate: DateTimeValue(start),
            EndDate: DateTimeValue(end),
            Description: body,
            Location: {Value: location},
            Organizer: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & organizer,
                DisplayName: Office365Users.UserProfile(organizer).DisplayName,
                Email: organizer,
                JobTitle: "",
                Picture: ""
            },Participants:ForAll(Split(requiredAttendees,";"),If(!(IsEmpty(Office365Users.UserProfile(Result))),{
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & Result,
                DisplayName: Office365Users.UserProfile(Result).DisplayName,
                Email: Result,
                JobTitle: "",
                Picture: ""
            }))
        }
    )
)

Best Regards,

Bof

[hkmamun]

@v-bofeng-msft  thanks for your support, here is my feedback

1. I can not change the Participants type field from Person to text, since from App side user also creating Outlook events by selecting Attendees/Participants from a People picker. Is there any way to by pass or hide the error, though error is showing but value is saving. 

2. Body is saving as HTML, so need to convert it as text, means I need to save the text part inside the <body></body> tag, is there any way?

3. Cancelled event is also coming , so how can I ignore or discard them?

NOTE: If possible can you please help me in this problem also: https://powerusers.microsoft.com/t5/Building-Power-Apps/Add-30-minutes-to-End-Date-Time-when-Start-D...

Thanks!

[v-bofeng-msft]

Hi @hkmamun :

About Q1 and Q2

My method is to detect the problematic record and replace the value of requiredAttendees with Blank(). In addition, I can't think of a better method than this.

Please try this code:

ClearCollect(CalendarEventsForSync,Sort(Office365Outlook.V4CalendarGetItems("Calendar").value,start,Descending));RemoveIf(CalendarEventsForSync,id in DailyActivities.EventID);ClearCollect('the unrecognized users',{TheValue:Blank()});Collect('the unrecognized users',Ungroup(ForAll(CalendarEventsForSync,ForAll(Split(requiredAttendees,";"),If(!(Result in Office365Users.SearchUserV2().value.Mail),{TheValue:Result}))),"Value"));ForAll('the unrecognized users',UpdateIf(CalendarEventsForSync,TheValue in Split(requiredAttendees,";"),{requiredAttendees:Blank()}));ForAll(
    CalendarEventsForSync,
    Patch(
        DailyActivities,
        Defaults(DailyActivities),
        {
            Title: subject,
            StartDate: DateTimeValue(start),
            EndDate: DateTimeValue(end),
            Description: Match(body,"(?<=<body>).*(?=</body>)").FullMatch,
            Location: {Value: location},
            Organizer: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & organizer,
                DisplayName: Office365Users.UserProfile(organizer).DisplayName,
                Email: organizer,
                JobTitle: "",
                Picture: ""
            }
        ,Participants:If(!(IsBlank(requiredAttendees)),ForAll(Split(requiredAttendees,";"),{
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & Result,
                DisplayName: Office365Users.UserProfile(Result).DisplayName,
                Email: Result,
                JobTitle: "",
                Picture: ""
            }))}
    )
)

About Q3

I did a test, and when I canceled an event, I couldn't get it anymore.

Best Regards,

Bof

[hkmamun]

@v-bofeng-msft  thanks for your tremendous effort, since all the users are coming from AD, so why they will be unrecognized? To get text from body I have used PlainText(body) function and getting text value, is it okay?

Thanks!

Have a nice day!

[v-bofeng-msft]

Hi @hkmamun :

I’m not quite sure whether the users in the requiredAttendees field can be found in your organization(If not found, the program will report an error and the code will not run), so I have done the above measures to ensure that the code can run normally. If you are sure that there are no problems, you can directly use the code provided in my first reply.

In addtion,I think using PlainText(body) is also feasible, which is better than the solution I provided.

Best Regards,

Bof

[hkmamun]

@v-bofeng-msft If there is no "requiredAttendees" means events has only the organizer no attendees/participants then this code is giving error.

ForAll(
    CalendarEventsForSync,
    Patch(
        DailyActivities,
        Defaults(DailyActivities),
        {
            Title: subject,
            StartDate: DateTimeValue(start),
            EndDate: DateTimeValue(end),
            Description: body,
            Location: {Value: location},
            Organizer: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & organizer,
                DisplayName: Office365Users.UserProfile(organizer).DisplayName,
                Email: organizer,
                JobTitle: "",
                Picture: ""
            },Participants:ForAll(Split(requiredAttendees,";"),If(!(IsEmpty(Office365Users.UserProfile(Result))),{
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & Result,
                DisplayName: Office365Users.UserProfile(Result).DisplayName,
                Email: Result,
                JobTitle: "",
                Picture: ""
            }))
        }
    )
)

Thanks!

[v-bofeng-msft]

Hi @hkmamun :

Please try this code:

ForAll(
    CalendarEventsForSync,
    Patch(
        DailyActivities,
        Defaults(DailyActivities),
        {
            Title: subject,
            StartDate: DateTimeValue(start),
            EndDate: DateTimeValue(end),
            Description: body,
            Location: {Value: location},
            Organizer: {
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & organizer,
                DisplayName: Office365Users.UserProfile(organizer).DisplayName,
                Email: organizer,
                JobTitle: "",
                Picture: ""
            },Participants:If(IsBlank(requiredAttendees),Blank(),ForAll(Split(requiredAttendees,";"),{
                '@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
                Department: "",
                Claims: "i:0#.f|membership|" & Result,
                DisplayName: Office365Users.UserProfile(Result).DisplayName,
                Email: Result,
                JobTitle: "",
                Picture: ""
            }))
        }
    )
)

I have got the correct result.

Best Regards,

Bof

[hkmamun · Accepted solution]

@v-bofeng-msft  thanks for your support. I have found the problem and that is the trailing ";" in the Split() function. If the input is "a@xyz.com;b@xyz.com;" then Split() function creating 3 Result and the last one is empty. 

So what did, I just take the whole text except last ;(semicolon) from the input. 

The code is here

Split(Left(input,Len(input)-1,";")

Thanks!

Mamun