Configuring the SharePoint List

Figure 22-1 shows the SharePoint list where we store our data.

A screenshot of the reservations page. It has 8 columns with relevant data underneath.

Importantly, we include the time component in our AppointmentDate column since the user will be picking from available dates and times. We include Modified By in our view since we are going to allow either the person who created the appointment¹ OR the person for whom it was scheduled edit the appointment. We include a Subject and Comments for the user to give some additional detail on what they want to discuss in the reservation. Status is a choice column with the default value of Available. Our Power App will change it to Booked when the appointment is scheduled. If the user cancels, it will put it back to Available and clear the Subject and Comments. We store the Participant (Person column) separately so a user can schedule for someone else as mentioned before². We default AppointmentLength to 20 (minutes) but preserve the ability to change this value if desired. Finally, we have a ScheduledTeamsMeeting Yes/No column. We will use that later with our scheduled Power Automate flow. We will query for all the Booked reservations where ScheduledTeamsMeeting is No. The flow will then schedule the meeting and set this value to Yes.

Next on the docket is Power Apps.

Creating our Application in Power Apps

We will use varUserEmail later in determining if there are existing reservations the current user can edit. varParticipant is useful in updating the participant as well as showing or hiding our error label. We initialize it to be Blank().

Figure 22-2 shows the Home screen of our application.

A screenshot of the appointments page. It has available appointments on the left, and input boxes to add a new appointment on the right.

This is less complicated than some of our other examples as we don’t need to create any new records. Instead, we use the LookUp() method to find the correct SharePoint list item. We then set the Subject and Comments to the values entered in their respective controls. Setting the Status is non-trivial, though, since it is a Choice column in SharePoint. We normally just match it to a drop-down box, but in this case we always want to always set it to Booked. I originally tried to create a variable from a hidden drop-down box, but that was strangely unreliable. Luckily, a quick search found the proper {Value: “Booked”} syntax. We’ve covered how to update a SharePoint Person column, so we won’t cover it again here other than to note how we took advantage of our varParticipant variable. The ScheduledTeamsMeeting column should already be false, but we set it that way to be on the safe side. We don’t set AppointmentLength since that is part of the appointment and not set in our design by the person scheduling. We Reset() our controls so they are empty if the user selects another appointment or returns after editing an appointment. We also set our varParticipant to Blank() to be sure that it doesn’t persist now that we have cleared the combo box where we set it.

This is a bit different than our normal syntax, but my original formulation⁴ was subject to delegation issues. Instead, we use the With() function to evaluate our filter and treat it as a single record. As we described in the last section, we want for either the participant or the person who made the reservation to be able to edit it. Since both Participant and Modified By are Person columns, we can get their Email property and compare it to the current user’s email. We also wanted our Status to be Booked and for the appointment to be in the future.

Figure 22-3 shows the resulting screen.

A screenshot of the appointments page. It has existing appointments on the left, with input boxes to update an appointment on the right.

This will display the existing values that the user can then edit if desired. This is helpful as we will write the contents of these boxes back to the appointment (even if they haven’t changed).

We want to Patch() the selected appointment by clearing its Subject and Comments, setting its Status back to Available, and setting ScheduledTeamsMeeting to false. We’d like to clear the Participant as well, but that is not currently possible. But since we will write a new Participant when the appointment is rescheduled, we should be fine.⁶ We then navigate back to the Home screen.

We update both the Subject and the Comments and then return to the Home screen.

We are now ready to go to Power Automate to schedule a Teams meeting for each booked appointment.

Adding a Scheduled Flow in Power Automate

While we could have launched Power Automate from within our application, in this case, it makes more sense to have a scheduled flow, as shown in Figure 22-4, perhaps every day at 5 AM.

A cropped screenshot of the power automate page has a panel on the left with my flows highlighted. An arrow points to the scheduled cloud flow option from the menu on the right.

That will minimize the rework if the user cancels or changes the appointment the same day. While we will schedule a Teams meeting for each future appointment, we could add additional logic to only schedule meetings one week or some other time frame before the appointment. Figure 22-5 shows how we set the frequency and the time for the flow to run.

A screenshot of the build a scheduled cloud flow window. It has input boxes for flow name, run this flow, and repeat every, option on the right.

After setting up this recurrence, we go to the SharePoint connector and configure the Get items action, as shown in Figure 22-6.

A screenshot of the get items window has drop-down menus for site address, list name, and include nested items. It also has a select a folder option.

We configure the Site Address and List Name to match our connection in Power Apps. We then need to click Show advanced options in order to choose which items we want. We do that by entering an Open Data Protocol (OData) filter query, as shown in Figure 22-7.

A screenshot of the get items query. An arrow points to the data u t c now in filter query from the expression tab on the right.

ne means “not equals,” ge means “greater than or equals,” and eq means “equals.” Notice also how a 1 represents true and a 0 represents false. We use the Expression window in Figure 22-7 to insert the utcNow() method to dynamically insert the current time in Universal Time Coordinated (UTC) format (previously known as Greenwich Mean Time). Since our AppointmentDate value from SharePoint includes a time zone component, Power Automate gets the math right on whether the time is in the future. We could include an OData Order By value, but we are going to process all the list items we get and thus the order does not matter.

We next need to loop through all the list items that are returned by the query. As shown in Figure 22-8, we do this with the Apply to each action and use the values attribute, which is the list of the SharePoint items.

A cropped screenshot of the apply to each task window. An arrow points to value in select an output, from the dynamic content tab.

But using this value directly when we schedule the Teams meeting causes an error. Instead, we use the Compose action, as shown in Figure 22-9, and then use the formatDateTime() expression.

A screenshot of the format start date time task under apply to each window. An arrow points to format date time input, from the expression tab on the right.

So what we needed to get rid of was the seconds and the Z (which means the time zone is UTC).

We similarly need the ending date and time of our meeting. We again use the Compose action, as shown in Figure 22-10.

A screenshot of the format end date time task under apply to each window. An arrow points to format date time input through add minutes method.

We are now finally ready to schedule our meeting. We select the Create a Teams meeting action and choose our primary Calendar from the list of available calendars. Figure 22-11 shows how we use the Participant Display Name value from our SharePoint list item in the Subject of the meeting.

A screenshot of the create a teams meeting task window. An arrow points to the participant display name data under the subject input box, from the dynamic content tab.

We use a similar technique (Figure 22-12) to add the Subject, Comments, Participant, and Scheduled By to the Message of the meeting.

A screenshot of the create a teams meeting task window with data in message input box. An arrow points to the outputs data under the start time input box.

Notice how we use the HTML Line Break (<br />) to get hard returns in the message. We use the Outputs from our Compose action (Figure 22-9) for the Start time and do the same for the End time (see Figure 22-13).

A screenshot of the create a teams meeting task window. An arrow points to the participant email data under the required attendees input box.

Our last scheduling task is to add our Participant Email as a Required Attendee, as shown in Figure 22-13.

Now that we have scheduled the meeting, we need to make sure the next time the flow runs that it doesn’t try to schedule another one. To do that, we need to use the SharePoint Update item action to set the ScheduledTeamsMeeting column to Yes, as shown in Figure 22-14.

A screenshot of the update item task window. Data from I d, status value, and scheduled teams meeting are highlighted as I D, booked, and yes.

Notice how use the ID from our Get items action (within our loop) in order to make sure we are editing the right item. We set the Status to Booked just to be on the safe side and then set ScheduledTeamsMeeting to Yes.

We did it! Figure 22-15 shows an example of our scheduled Teams meeting.

A screenshot of the Microsoft teams meeting reservation. It has the details of a reservation in the scheduled team meeting for Jeffrey Rhodes.

This solution doesn’t cancel the meeting if the user cancels the reservation in our Power App, but that would be a good enhancement. We could also easily connect Power BI to our SharePoint list to make a dashboard showing appointments, what percentage of available appointments were booked, and much more.

Summary

In this chapter, we connected Power Apps to SharePoint to keep track of appointment reservations. We incorporated some specialized logic to allow either the submitter or the person with the registration to edit or cancel their reservations. We then used the ability of Power Automate to do scheduled flows to automatically create Teams Meetings for the appointments. To pull this off, we learned how to do OData filter queries so that we could limit to just those appointments without a Teams Meeting as well as some gyrations around date and time formats. We then updated our SharePoint list to reflect that the meetings had been scheduled so we could avoid duplicates. In the next chapter, we will dig into Power Apps Forms control for more sophisticated adding and editing of data.