Tag: Code

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.

As I play around with leveraging code in custom connectors more and more, I just love the possibilities it offers.

One of the original posts I saw was from Alex Shlega where he gives an example sorting an array. Wow this is great! But what if my array is not an array of just string values? What if it has numbers, or complex values, or nulls, etc.?

So I extended his example a bit using custom sorting logic, and added the ability to choose your desired sort order.

Let’s see how that works.

(Please see my other posts where I discuss a few more details on using the custom connector UI and where to apply the YAML and C# script code when building the Regular Expression custom action and add some enhancements.)

Action Definition

Here’s what our custom action can do:

swagger: '2.0'
info: {title: SteelCut Bytes Actions, description: SteelCut Bytes Power Automate Actions,
  version: '1.0'}
host: flow.steelcutbytes.com
basePath: /
schemes: [https]
consumes: []
produces: []
paths:
  /SortArray:
    post:
      responses:
        default:
          description: default
          schema:
            type: array
            items: {}
            description: Sorted array
            title: Sorted Array
      summary: Sort array
      operationId: SortArray
      description: Sorts the array values using both String and Numeric comparison.
      parameters:
      - name: value
        in: body
        required: true
        schema:
          type: object
          properties:
            items:
              type: array
              items: {}
              description: Unsorted chaotic array of values
              title: Chaotic Array
            sort:
              title: Sort Order
              type: string
              enum: [Ascending, Descending, Reverse]
              default: Ascending
          required: [items, sort]
definitions: {}
parameters: {}
responses: {}
securityDefinitions: {}
security: []
tags: []

Normally, you want to be as specific as possible in the types used in the YAML definition. But, since we are allowing an array of multiple data types (string, numbers, null, objects, etc.) it pays to be vague in this case.

Secret sauce:

Declaring both our input and output array items without a type (items: {}) allows both our action and code to receive our array of varying data types.

Code

Here’s the C# code for the sorting logic:

public class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        switch (this.Context.OperationId)
        {
            case "SortArray":
                return await this.HandleSortArray().ConfigureAwait(false);
        }

        var response = new HttpResponseMessage(HttpStatusCode.BadRequest);
        response.Content = CreateJsonContent($"Unknown operation ID '{this.Context.OperationId}'");
        return response;
    }

    private async Task<HttpResponseMessage> HandleSortArray()
    {
        var contentAsString = await this.Context.Request.Content.ReadAsStringAsync().ConfigureAwait(false);
        var contentAsJson = JObject.Parse(contentAsString);

        var stringJArray = (JArray)contentAsJson["items"];
        var sortOrder = (string)contentAsJson["sort"];

        var objectList = stringJArray.ToObject<List<Object>>();

        switch (sortOrder)
        {
            case "Ascending":
            case "Descending":
                objectList.Sort((x, y) =>
                {
                    var strX = (x == null ? null : x.ToString());
                    var strY = (y == null ? null : y.ToString());

                    decimal decX, decY;
                    if (decimal.TryParse(strX, out decX) && decimal.TryParse(strY, out decY))
                        return (decX > decY) ? 1 : (decX < decY) ? -1 : 0;
                    else
                        return StringComparer.Ordinal.Compare(strX, strY);
                });
                break;
        }

        if (sortOrder != "Ascending")
            objectList.Reverse();

        var result = JArray.FromObject(objectList);

        var response = new HttpResponseMessage(HttpStatusCode.OK);
        response.Content = CreateJsonContent(result.ToString());
        return response;
    }
}

The updates I’ve made to Alex Shlega’s example are highlighted in the code above. I’ll quickly explain those.

The first thing I do is make sure the JSON is converted to an array of objects and not strings.

var objectList = stringJArray.ToObject<List<Object>>();

This ensures that the types of numbers and other non-string data are preserved so that the sorting logic will distinguish them correctly. This comes in handy when sorting numbers in logical numeric order instead of string order, i.e. 0, 1, 2, 10 instead of "0", "1", "10", "2", and when handling null values.

Next I handle the sort logic, using the .NET Enumerable.Sort(...) extension method.

objectList.Sort((x, y) =>
{
  var strX = (x == null ? null : x.ToString());
  var strY = (y == null ? null : y.ToString());

  decimal decX, decY;
  if (decimal.TryParse(strX, out decX) && decimal.TryParse(strY, out decY))
    return (decX > decY) ? 1 : (decX < decY) ? -1 : 0;
  else
    return StringComparer.Ordinal.Compare(strX, strY);
});

To handle both integer and float number types, I check if the values are decimal using TryParse(...), and when true, I sort according to numeric logic.

If not numeric, then I treat all other types as a string (or null) and sort according to string sorting logic using StringComparer.Ordinal.Compare(...).

Here’s the output of the enhanced sort logic:

Simple as that!

But … it can be better!

This is definitely just a quick and easy sort. My main focus was on handling numbers correctly, empty strings, and null values.

When showing this to one of my brilliant co-workers (Reza Dorrani, thank you!) he suggested an enhancement to handle more complex data type scenarios, such as sorting objects by a specific property value.

This is on my to-do and will be the source of a future post.

Happy coding!

Follow My Blog

Get new content delivered directly to your inbox.

One thing you may have noticed in some of the Power Automate actions is the ability to select a predefined value from a list of choices when setting the value of an action’s parameter. (Think of any one of the SharePoint actions allowing you to choose a site URL and a list name, the Terminate action with the Status parameter Failed, Cancelled, Succeeded, or the Regex Options parameter in the Regular Expression custom code connector we built in the previous post, etc.)

This comes in really handy when you want to prevent the user from incorrectly configuring an action or guide them to a specific action input.

I’ve updated the Regular Expression custom code connector action to provide the user a new IsNumeric choice for the Regular Expression parameter.

The purpose of this new parameter choice is to perform a match against the supplied text and evaluate whether or not it is strictly a numeric value. For example: 1234567 is numeric, whereas THX1138 is not.

Let’s see how we can enhance our custom action.

Action Definition

As we saw previously, what your custom action can do is defined by the YAML swagger file.

This definition is the same as the previous post except for one new attribute: enum. This attribute requires an array of one or more literal values (enumerations), in our example [IsNumeric] .

swagger: '2.0'
info: {title: SteelCut Bytes Actions, description: SteelCut Bytes Power Automate Actions,
  version: '1.0'}
host: flow.steelcutbytes.com
basePath: /
schemes: [https]
consumes: []
produces: []
paths:
  /RegularExpression:
    post:
      responses:
        default:
          description: default
          schema:
            type: object
            properties:
              input: {type: string, description: The supplied string to search for
                  a match., title: Text to Match}
              pattern: {type: string, description: The supplied .NET regular expression
                  pattern to match., title: Regular Expression}
              matches:
                type: array
                items:
                  type: object
                  properties:
                    Groups: {type: object}
                    Success: {type: boolean}
                    Name: {type: string}
                    Captures:
                      type: array
                      items: {type: object}
                    Index: {type: integer}
                    Length: {type: integer}
                    Value: {type: string}
                description: An array of the Match objects found by the search. If
                  no matches are found, the method returns an empty array.
                title: Matches
              isMatch: {type: boolean, description: Indicates whether the .NET regular
                  expression finds a match in the input string., title: IsMatch}
      summary: Regular Expression
      description: Searches the specified input string for all occurrences of a specified
        .NET regular expression, using the specified matching options.
      operationId: RegularExpression
      parameters:
      - name: value
        in: body
        required: true
        schema:
          type: object
          properties:
            input: {type: string, description: The string to search for a match, title: Text
                to Match}
            pattern:
              type: string
              description: The .NET regular expression pattern to match
              enum: [IsNumeric]
              title: Regular Expression
            options:
              title: Regex Options
              description: Value that specifies options for matching
              type: string
              enum: [None, IgnoreCase, Singleline, Multiline]
              default: IgnoreCase
          required: [options, pattern, input]
definitions: {}
parameters: {}
responses: {}
securityDefinitions: {}
security: []
tags: []

When an enumeration is chosen, the value of that parameter will be a literal string equal to that enumeration. So in our example, the Regular Expression parameter will now be equal to the literal string IsNumeric when selected.

The new enumeration value needs to be evaluated and handled by our custom code.

Code

To use this new value in our code, we simply need to check for that value and then do something with it or because of it.

• If the value matches IsNumeric then use a hard-coded regular expression (regex pattern ^\d+$) instead.

• If the value is not IsNumeric then we treat it as an actual regular expression and use it as we did before.

public class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        switch (this.Context.OperationId)
        {
            case "RegularExpression":
                return await this.HandleRegexOperation().ConfigureAwait(false);
        }

        var response = new HttpResponseMessage(HttpStatusCode.BadRequest);
        response.Content = CreateJsonContent($"Unknown operation ID '{this.Context.OperationId}'");
        return response;
    }

    private async Task<HttpResponseMessage> HandleRegexOperation()
    {
        var contentAsString = await this.Context.Request.Content.ReadAsStringAsync().ConfigureAwait(false);
        var contentAsJson = JObject.Parse(contentAsString);

        var textToMatch = (string)contentAsJson["input"];
        var regexInput = (string)contentAsJson["pattern"];
        var regexOptions = (string)contentAsJson["options"];

        var regex = new Regex(
            (regexInput == "IsNumeric") ? @"^\d+$" :
            (regexInput),

            (regexOptions == "IgnoreCase") ? (RegexOptions.IgnoreCase | RegexOptions.Compiled) :
            (regexOptions == "Singleline") ? (RegexOptions.Singleline | RegexOptions.Compiled) :
            (regexOptions == "Multiline") ? (RegexOptions.Multiline | RegexOptions.Compiled) :
            (RegexOptions.None | RegexOptions.Compiled),

            TimeSpan.FromSeconds(1)
        );

        var jSerializer = JsonSerializer.Create(new JsonSerializerSettings
        {
            ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
        });

        var result = new JObject
        {
            ["input"] = textToMatch,
            ["pattern"] = regex.ToString(),
            ["isMatch"] = regex.IsMatch(textToMatch),
            ["matches"] = JArray.FromObject(regex.Matches(textToMatch), jSerializer),
        };

        var response = new HttpResponseMessage(HttpStatusCode.OK);
        response.Content = CreateJsonContent(result.ToString());
        return response;
    }
}

As you can see, this adds tremendous value to the end user by providing them a simpler configuration of the action and amplifies the intent of its use. In this case, these choices could reduce or even eliminate the need for them to know regular expression syntax.

What about my custom value? Why not both.

One of the nice things about the Power Automate GUI is that you can also supply a custom enumeration as a choice. You do this by simply choosing the Enter custom value choice at the bottom of the choice list. This turns the choice list field back into a text box.

So now, you can apply a custom Regular Expression pattern (as we did in the original version) like this:

1. Simply choose the Enter custom value choice to display the Regular Expression text box,
2. Enter your regular expression pattern in the Expression editor of the flow’s Dynamic content panel, by wrapping it in the string(...) expression.

For example:

@string('(?i)<a id="token" href="(?<token>.*[^\W]=*)"></a>')

Here’s the result:

Wow, wow, wow.

But wait … there’s more!

You can use this approach to provide whatever built-in regular expressions or checks you’d like. The possibilities are endless.

Here are some example expressions:

• Parsing the HRefs from HTML text
• Simply email validation
• Simple hyphenated US phone number with area code

Have fun.

Follow My Blog

Get new content delivered directly to your inbox.

When playing around with a new custom connection I was creating in Power Automate, I came across the Code (Preview) tab. The developer part of my brain thought “Oh, that’s neat, I can now include code, I like writing code. But I’m not sure why I would need or use that.” And that was that.

That was, until, I came across this Twitter post by John Liu. In it he mentions two other custom code connector solutions by Alex Shlega and Hiro which sparked him to create a parse CSV connector. I recommend checking out those other two as well. One thing I always appreciate about John (hopefully he’s ok with my referring to him as if we know each other) is that his posts and ideas always spark those “ooh, I wonder if …” thoughts which my brain latches onto.

This is when it clicked.

Code in custom connectors can be used 1) for simplifying complex Power Automate flow logic by (in some cases) replacing the need for multiple actions, and 2) to fill in missing Power Automate functionality. So this got me thinking, what expressions/actions are missing that I am need of? After one last pass through the Microsoft documentation and examples, their regular expression example grabbed my attention. Ooh yeah, I’ve needed that, let’s built that!

Building the Regular Expression action using a custom code connector.

Here’s a look at what we’re ultimately going to build. This action will allow us to supply the text to match, a regular expression pattern, some basic options, and receive the matches.

And here’s what we need to do it. The two main components of custom code connectors are: the action definition (what it can do) and the code (how it will do it).

Action Definition

The action can be defined using the custom connector GUI wizard or using the YAML swagger definition editor. If this is your first time building a connector, while a little intimidating, I would recommend that you use the GUI so that you become familiar with all of the components of the action definition.

But for simplicty of this post, I’ve included the swagger definition below. Simply use the “Swagger Editor” toggle to display the YAML editor and paste in the swagger definition.

To switch back to the wizard under the Defintion tab, use the “Swagger Editor” toggle again.

swagger: '2.0'
info: {title: SteelCut Bytes Actions, description: SteelCut Bytes Power Automate Actions,
  version: '1.0'}
host: flow.steelcutbytes.com
basePath: /
schemes: [https]
consumes: []
produces: []
paths:
  /RegularExpression:
    post:
      responses:
        default:
          description: default
          schema:
            type: object
            properties:
              input: {type: string, description: The supplied string to search for
                  a match., title: Text to Match}
              pattern: {type: string, description: The supplied .NET regular expression
                  pattern to match., title: Regular Expression}
              matches:
                type: array
                items:
                  type: object
                  properties:
                    Groups: {type: object}
                    Success: {type: boolean}
                    Name: {type: string}
                    Captures:
                      type: array
                      items: {type: object}
                    Index: {type: integer}
                    Length: {type: integer}
                    Value: {type: string}
                description: An array of the Match objects found by the search. If
                  no matches are found, the method returns an empty array.
                title: Matches
              isMatch: {type: boolean, description: Indicates whether the .NET regular
                  expression finds a match in the input string., title: IsMatch}
      summary: Regular Expression
      description: Searches the specified input string for all occurrences of a specified
        .NET regular expression, using the specified matching options.
      operationId: RegularExpression
      parameters:
      - name: value
        in: body
        required: true
        schema:
          type: object
          properties:
            input: {type: string, description: The string to search for a match, title: Text
                to Match}
            pattern:
              type: string
              description: The .NET regular expression pattern to match
              title: Regular Expression
            options:
              title: Regex Options
              description: Value that specifies options for matching
              type: string
              enum: [None, IgnoreCase, Singleline, Multiline]
              default: IgnoreCase
          required: [options, pattern, input]
definitions: {}
parameters: {}
responses: {}
securityDefinitions: {}
security: []
tags: []

Code

The code creates a .NET Regular Expression, matching the text against the pattern, returning the matches.

Simply paste the code below under the Code (Preview) tab in the connector GUI.

public class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        switch (this.Context.OperationId)
        {
            case "RegularExpression":
                return await this.HandleRegexOperation().ConfigureAwait(false);
        }

        var response = new HttpResponseMessage(HttpStatusCode.BadRequest);
        response.Content = CreateJsonContent($"Unknown operation ID '{this.Context.OperationId}'");
        return response;
    }

    private async Task<HttpResponseMessage> HandleRegexOperation()
    {
        var contentAsString = await this.Context.Request.Content.ReadAsStringAsync().ConfigureAwait(false);
        var contentAsJson = JObject.Parse(contentAsString);

        var textToMatch = (string)contentAsJson["input"];
        var regexInput = (string)contentAsJson["pattern"];
        var regexOptions = (string)contentAsJson["options"];

        var regex = new Regex(regexInput,
            (regexOptions == "IgnoreCase") ? (RegexOptions.IgnoreCase | RegexOptions.Compiled) :
            (regexOptions == "Singleline") ? (RegexOptions.Singleline | RegexOptions.Compiled) :
            (regexOptions == "Multiline") ? (RegexOptions.Multiline | RegexOptions.Compiled) :
            (RegexOptions.None | RegexOptions.Compiled),

            TimeSpan.FromSeconds(1)
        );

        var jSerializer = JsonSerializer.Create(new JsonSerializerSettings
        {
            ReferenceLoopHandling = ReferenceLoopHandling.Ignore,
        });

        var result = new JObject
        {
            ["input"] = textToMatch,
            ["pattern"] = regex.ToString(),
            ["isMatch"] = regex.IsMatch(textToMatch),
            ["matches"] = JArray.FromObject(regex.Matches(textToMatch), jSerializer),
        };

        var response = new HttpResponseMessage(HttpStatusCode.OK);
        response.Content = CreateJsonContent(result.ToString());
        return response;
    }
}

Caveats: code must be C#, finish in 5 secs or less*, and be no larger than 1MB.

*In my tests, I’ve found that if the code takes longer than 5 seconds to run, the action and flow will still run. But for best performance, you should still stick to this requirement by keeping your code as lean and optimized as possible. I’m not sure, but I feel like at some point Microsoft could prevent your action and flow from running successfully if it violates this time threshold. Just keep this in mind.

Output

Here’s the output of the Regular Expression custom code connection action.

As you can see from the output, it returns the array of Matches, along with the IsMatch true/false value. This allows the Matches output to be used in an expression like this:

@first(outputs('Regular_Expression')?['body/matches'])?['Groups/token/Value']

Now you can use Regular Expressions in your flows. How about that!

Currently, there are no official out-of-the-box actions or expressions in Power Automate that provide support for regular expressions, although it’s been requested. And recently, I found out that you can also do this via Excel Office scripts, but I’ve never been truly comfortable with Excel, a side effect of having to write VBA code early on in my career. (I know, I know, the Office Script code is actually Javascript which is better, but still …)

What other creative ways can we leverage custom code connectors? I’ll leave that as an exercise for the reader … and future blog posts.

Happy coding!

Follow My Blog

Get new content delivered directly to your inbox.