Recurring Calendar Events in Power Automate, using SOAP

It’s strange how using something called SOAP can make you feel dirty & unclean. But in the modern SharePoint development era, accessing your content via the new clean & shiny REST API can do just that.

I’m going to make the case that there is at least still one good reason to use the older SOAP web service, and that is: recurring calendar events.

In the age of the modern interface in SharePoint online, the new modern Calendar (which is now just a new view type on top of a custom list) lacks the ability to create or display recurring events. This lack of support for recurring events, while acknowledged by some as “nobody really uses those anymore”, really seems to stem from issues with how this feature was implemented in classic calendars in the first place. (As expressed by most of the other developers I’ve talked to about this.)

Even so, I’ve had to support solutions with the hard requirement of recurring events from several of my clients.

Ultimately, I say all of this because it does feel a little strange telling you how to call a SOAP web service from Power Automate. But here goes …

The Setup

At first glance, just using the Get Item(s) or Send an HTTP request to SharePoint actions would seem like the logical choice. But there are a couple of problems with trying to use these actions:

1. The SharePoint actions generally expect the body content to be JSON, whereas calls to the SOAP web services expect XML.
2. The default authentication token/cookie/whatever inherently passed to the SharePoint actions results in a 401/403 access denied error when using the SOAP endpoint as the destination.
3. Get Item(s) doesn’t seem to allow us to select a classic calendar “Events” list.

So unfortunately, we can’t use these actions to get the calendar events directly and can’t use them to connect to the SOAP web services.

So, how do we do it?

This is where the (premium) HTTP action comes in.

Yep, this process is premium and there doesn’t appear to be a way around that. If you’re ok with that, let’s continue.

We’ll tackle the authentication hurdle first.

You can authenticate successfully to the SOAP endpoint by passing it the SPOIDCRL cookie.

There are 2 steps needed to obtain this cookie.

1) Authenticate to the Secure Token Service (STS) using an HTTP action to connect to the https://login.microsoftonline.com/rst2.srf endpoint, passing your username and password and the address of the site (URL).

The body of the STS request requires the following SOAP envelope, containing the credentials and the destination URL (address):

<?xml version="1.0" encoding="UTF-8"?>
<S:Envelope xmlns:S="http://www.w3.org/2003/05/soap-envelope" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsp="http://schemas.xmlsoap.org/ws/2004/09/policy" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" xmlns:wsa="http://www.w3.org/2005/08/addressing" xmlns:wst="http://schemas.xmlsoap.org/ws/2005/02/trust">
  <S:Header>
    <wsa:Action S:mustUnderstand="1">http://schemas.xmlsoap.org/ws/2005/02/trust/RST/Issue</wsa:Action>
    <wsa:To S:mustUnderstand="1">https://login.microsoftonline.com/rst2.srf</wsa:To>
    <ps:AuthInfo xmlns:ps="http://schemas.microsoft.com/LiveID/SoapServices/v1" Id="PPAuthInfo">
      <ps:BinaryVersion>5</ps:BinaryVersion>
      <ps:HostingApp>Managed IDCRL</ps:HostingApp>
    </ps:AuthInfo>
    <wsse:Security>
        <wsse:UsernameToken wsu:Id="user">
            <wsse:Username>username</wsse:Username>
            <wsse:Password>password</wsse:Password>
        </wsse:UsernameToken>                
    </wsse:Security>
  </S:Header>
  <S:Body>
    <wst:RequestSecurityToken xmlns:wst="http://schemas.xmlsoap.org/ws/2005/02/trust" Id="RST0">
      <wst:RequestType>
        http://schemas.xmlsoap.org/ws/2005/02/trust/Issue
      </wst:RequestType>
      <wsp:AppliesTo>
        <wsa:EndpointReference>
          <wsa:Address>yourtenant.sharepoint.com</wsa:Address>
        </wsa:EndpointReference>
      </wsp:AppliesTo>
      <wsp:PolicyReference URI="MBI"></wsp:PolicyReference>
    </wst:RequestSecurityToken>
  </S:Body>
</S:Envelope>

2) Next, we grab the BinarySecurityToken from the response of the HTTP action above, sending it as the Authorization header in our 2nd HTTP action. This action uses the https://yourtenant.sharepoint.com/_vti_bin/idcrl.svc endpoint.

If the site you are accessing is a subsite or a different site collection you’ll need to use that URL instead, i.e. https://yourtenant.sharepoint.com/sites/myothersitecoll/_vti_bin/idcrl.svc .

The header (with BinarySecurityToken) is set using the JSON + expression below:

{ 
  "Authorization": "BPOSIDCRL @{json(xml(body('Get_Login_Token')))?['S:Envelope']['S:Body']['wst:RequestSecurityTokenResponse']['wst:RequestedSecurityToken']['wsse:BinarySecurityToken']['#text']}" 
}

Here’s what that the HTTP action will look like:

And now … from the output of this 2nd HTTP request, we’ll receive the SPOIDCRL cookie.

This is the required cookie we need to call our SOAP web service. Alright!

Finally, we’re getting somewhere!

Now that we’ve tackled our authentication issue, the last step is to query our calendar via the SOAP web service call to /lists.asmx.

So we’ll use a 3rd HTTP action to do the query, like so:

We start by using the following SOAP endpoint and supplied headers: https://yourtenant.sharepoint.com/_vti_bin/lists.asmx

{
  "Content-Type": "text/xml; charset=utf-8",
  "SOAPAction": "\"http://schemas.microsoft.com/sharepoint/soap/GetListItems\""
}

If the site you are accessing is a subsite or a different site collection you’ll need to use that URL instead, i.e. https://yourtenant.sharepoint.com/sites/myothersitecoll/_vti_bin/lists.asmx .

Next, the body of the HTTP action is the SOAP Envelope below. It contains the input parameters of the GetListItems method. (I am only listing the parameters that I use, please see the link for all parameters.)

listName – The list’s id (GUID) or its title.

query – The SharePoint CAML query used to determine which list items to return.

viewFields – Specifies which fields of the list item SHOULD be returned.

queryOptions – Specifies various options for modifying the query.

The SOAP envelope containing our CAML query will look something like this:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/sharepoint/soap/">
  <soap:Header/>
  <soap:Body>
    <m:GetListItems>
      <m:listName>Your Calendar List Id or Title</m:listName>
      <m:viewName/>
      <m:query>
        <Query>
          <Where>
            <And>
              <DateRangesOverlap>
                <FieldRef Name="EventDate" />
                <FieldRef Name="EndDate" />
                <FieldRef Name="RecurrenceID" />
                <Value Type="DateTime"><Now /></Value>
              </DateRangesOverlap>
              <Or>
                <Eq>
                  <FieldRef Name="ID" />
                  <Value Type="Integer">ItemId</Value>
                </Eq>
                <Eq>
                  <FieldRef Name="MasterSeriesItemID" />
                  <Value Type="Integer">ItemId</Value>
                </Eq>
              </Or>
            </And>
          </Where>
          <OrderBy>
            <FieldRef Name="EventDate" />
          </OrderBy>
        </Query>
      </m:query>
      <m:viewFields>
        <ViewFields>
          <FieldRef Name="EventDate" />
          <FieldRef Name="EndDate" />
          <FieldRef Name="fRecurrence" />
          <FieldRef Name="RecurrenceData" />
          <FieldRef Name="fAllDayEvent" />
          <FieldRef Name="MasterSeriesItemID" />
          <FieldRef Name="GUID" />
        </ViewFields>
      </m:viewFields>
      <m:rowLimit></m:rowLimit>
      <m:queryOptions>
        <QueryOptions>
          <CalendarDate>Specific Date</CalendarDate>
          <ExpandRecurrence>TRUE</ExpandRecurrence>
          <RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion>
        </QueryOptions>
      </m:queryOptions>
      <m:webID/>
    </m:GetListItems>
  </soap:Body>
</soap:Envelope>

CAML Query, Let’s Break It Down

Here’s a great article that explains the results of several recurring date query tests.

<DateRangesOverlap /> – Compare the dates in a recurring event with a specified DateTime value to determine whether they overlap.

• <Now /> – Use to retrieve all instances of a recurring event that overlap the current date right now. This generally gives you all recurring events from now into the future, based upon the recurrence pattern (Daily, Weekly, Monthly, Yearly).
• <Month /> – Used to retrieve all instances of a recurring event that occur within a month. I’ve found this is most useful when the recurrence pattern is Monthly or Yearly.

<FieldRef Name="MasterSeriesItemID" /> – If you want to pull the recurring dates for a specific recurring event, pass its list item ID as the value here.

<QueryOptions /> – These options contain the magic for expanding the recurring events:

<ExpandRecurrence>TRUE</ExpandRecurrence>
<RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion>

The optional queryoption <CalendarDate/> allows us to pass a specific date to be used as the starting date of our query (although it seems to be ignored when using <Now/>):

<CalendarDate>2021-01-01</CalendarDate>

Lastly, two important notes about how many dates SharePoint will return:

1. No more than 30 expanded recurring dates are returned
2. Max returned dates are no more than two years in the future.

Now, put it all together

Armed with these pieces, here’s my example query:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:m="http://schemas.microsoft.com/sharepoint/soap/">
  <soap:Header/>
  <soap:Body>
    <m:GetListItems>
      <m:listName>Compliance Calendar</m:listName>
      <m:viewName/>
      <m:query>
        <Query>
          <Where>
            <And>
              <DateRangesOverlap>
                <FieldRef Name="EventDate" />
                <FieldRef Name="EndDate" />
                <FieldRef Name="RecurrenceID" />
                <Value Type="DateTime"><Now /></Value>
              </DateRangesOverlap>
              <Or>
                <Eq>
                  <FieldRef Name="ID" />
                  <Value Type="Integer">155</Value>
                </Eq>
                <Eq>
                  <FieldRef Name="MasterSeriesItemID" />
                  <Value Type="Integer">155</Value>
                </Eq>
              </Or>
            </And>
          </Where>
          <OrderBy>
            <FieldRef Name="EventDate" />
          </OrderBy>
        </Query>
      </m:query>
      <m:viewFields>
        <ViewFields>
          <FieldRef Name="EventDate" />
          <FieldRef Name="EndDate" />
          <FieldRef Name="fRecurrence" />
          <FieldRef Name="RecurrenceData" />
          <FieldRef Name="fAllDayEvent" />
          <FieldRef Name="MasterSeriesItemID" />
          <FieldRef Name="GUID" />
        </ViewFields>
      </m:viewFields>
      <m:rowLimit></m:rowLimit>
      <m:queryOptions>
        <QueryOptions>
          <CalendarDate></CalendarDate>
          <ExpandRecurrence>TRUE</ExpandRecurrence>
          <RecurrencePatternXMLVersion>v3</RecurrencePatternXMLVersion>
        </QueryOptions>
      </m:queryOptions>
      <m:webID/>
    </m:GetListItems>
  </soap:Body>
</soap:Envelope>

Here’s the output of the query:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <soap:Body>
        <GetListItemsResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/">
            <GetListItemsResult>
                <listitems xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
                    xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
                    xmlns:rs='urn:schemas-microsoft-com:rowset'
                    xmlns:z='#RowsetSchema'>
                    <rs:data ItemCount="2" ListItemCollectionPositionNext="Paged=Next&amp;p_StartTimeUTC=20221231T000001Z">
                        <z:row ows_EventDate='2021-12-31 00:00:00' ows_EndDate='2021-12-31 23:59:00' ows_fRecurrence='1' ows_fAllDayEvent='1' ows_GUID='{971C4640-015A-4E3E-91A3-7FD975606E92}' ows__ModerationStatus='0' ows__Level='1' ows_Title='Ensure Training conducted' ows_ID='155.0.2021-12-31T00:00:00Z' ows_UniqueId='155;#{4049F043-9F62-451E-ABC5-62E0550481A6}' ows_owshiddenversion='130' ows_FSObjType='155;#0' ows_Created='2016-11-30 19:48:04' ows_PermMask='0x7fffffffffffffff' ows_Modified='2021-11-04 12:12:03' ows_ScopeId='155;#{357BCCAA-6671-48A6-92F0-FC3C20EF2975}' ows_FileRef='155;#Lists/ComplianceCalendar/155_.000' ows_ComplianceRecurrence='1 - Daily, Weekly, Monthly, or Yearly' ows_MetaInfo='155;#' />
                        <z:row ows_EventDate='2022-12-31 00:00:00' ows_EndDate='2022-12-31 23:59:00' ows_fRecurrence='1' ows_fAllDayEvent='1' ows_GUID='{971C4640-015A-4E3E-91A3-7FD975606E92}' ows__ModerationStatus='0' ows__Level='1' ows_Title='Ensure Training conducted' ows_ID='155.0.2022-12-31T00:00:00Z' ows_UniqueId='155;#{4049F043-9F62-451E-ABC5-62E0550481A6}' ows_owshiddenversion='130' ows_FSObjType='155;#0' ows_Created='2016-11-30 19:48:04' ows_PermMask='0x7fffffffffffffff' ows_Modified='2021-11-04 12:12:03' ows_ScopeId='155;#{357BCCAA-6671-48A6-92F0-FC3C20EF2975}' ows_FileRef='155;#Lists/ComplianceCalendar/155_.000' ows_ComplianceRecurrence='1 - Daily, Weekly, Monthly, or Yearly' ows_MetaInfo='155;#' />
                    </rs:data>
                </listitems>
            </GetListItemsResult>
        </GetListItemsResponse>
    </soap:Body>
</soap:Envelope>

Pretty cool.

Hopefully, you’ll be able to tame those pesky recurring events as I have and maybe even grow to appreciate them too.

Happy coding!

Follow My Blog

Get new content delivered directly to your inbox.

Author: Joey

I develop modern solutions leveraging the Microsoft cloud technologies: Power Platform and SharePoint Online, with a focus on process automation. I got my start writing code professionally beginning with Microsoft VB6, but really fell in love with code (and the art of it) with Microsoft .NET Framework version 1.0. I'm also a fan of Superman, so don't be surprised if references creep in. Up, Up, and Away! View all posts by Joey