MoreBeerMorePower

Power Platform中心だけど、ノーコード/ローコード系を書いてます。

Calculate Sum & Average in Power Automate using C# code in a custom connector

f:id:mofumofu_dance:20210920172917p:plain

The use of C# code in custom connectors is a very effective way to simplify/extend the Power Automate flow by writing a few lines of code.

docs.microsoft.com

According to the documentation, no actual request will be made to the backend service unless the SendAsync method is executed. This makes it possible to just process the request content and return it as a response, without the help of Azure Functions, etc.

An approach with this idea of providing functionality that Power Automate does not have has been made by Alex Shelga regarding string array sorting:

www.itaintboring.com

In this post, I will show you how to calculate the sum and average of a numeric array, which is not provided by Power Automate, using a very simple C# code.

f:id:mofumofu_dance:20210920171950p:plain

Custom connector

Since the request will not be sent to the backend service without executing SendAsync, any definition of the connector is acceptable, but request body should be an array of numbers (float).

In the Create Custom Connector screen, enable the Swagger Editor, copy and paste the following Swagger, and the connector definition is complete.

f:id:mofumofu_dance:20210920171238p:plain

swagger: '2.0'
info: {title: CodeConnector, description: Code Connector, version: '1.0'}
host: google.com
basePath: /
schemes: [https]
consumes: []
produces: []
paths:
  /:
    post:
      responses:
        default:
          description: default
          schema: {}
      summary: Calculate Sum and Average
      operationId: calcSumAve
      parameters:
      - name: body
        in: body
        required: true
        schema:
          type: array
          items: {type: number, format: float}
definitions: {}
parameters: {}
responses: {}
securityDefinitions: {}
security: []
tags: []

C# code

Since LINQ namespace is available in the C# code script, you can use Sum and Average in LINQ to calculate the sum and average of the number array in the request body.

Please refer to the following document for syntax of Sum/Average.

LINQ Sum / LINQ Average

In code editor, incoming request array will be converted to float list, and then calculated sum & average will return as response body.

public class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        var contentAsString = await this.Context.Request.Content.ReadAsStringAsync().ConfigureAwait(false);
        var stringJArray = JArray.Parse(contentAsString);
        var floatArray = stringJArray.ToObject<List<float>>();
        float ave = floatArray.Average();
        float sum = floatArray.Sum();
        HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
        response.Content = CreateJsonContent("{\"average\": "+ave+",\"sum\":"+sum+"}");
        return response;
    }
}

That's all!

You can download swagger and c# code from my repo.

github.com

f:id:mofumofu_dance:20210920172434p:plain