Parse CSV through Code in Power Automate Custom Connection

/

I was inspired reading Alex Shlega and Hiroaki Nagao ’s posts on using code with custom connections. So I set out to give it a go and work on another common problem I have: Parse CSV

First a picture showing you how it works.

Give it text, the action returns array of arrays.

Microsoft’s docs are here.
Write code in a custom connector | Microsoft Docs

And particularly, I need to parse CSV without using additional libraries, and using only the existing libraries available here. I noted that we do have access to System.Text.RegularExpressions, so I started my planning there.

Because parsing CSV correctly is a problem best sorted via use of a tokenizer, I went looking for a regular expression pattern that treats each line as a series of tokens. There are many patterns, but I like this one that I found on stackoverflow the best for my needs. https://stackoverflow.com/a/48806378

Code

So the code takes all the content of the body and splits by line breaks, then the regular expression is run over every line using Matches (this method returns multiple matches giving us a MatchCollection of tokens). In each match, I look for Group[2] which is the value without quotes “ and “. But if failing that match, we take Group[1] value.
We do not take the Match.Value because that would include the comma.

/end of regular expression explanation.

We cast the matches back to array via Linq and then back to JArray and return that back to Flow.

public class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        if (this.Context.OperationId == "csv")
        {
            return await this.HandleCSV().ConfigureAwait(false);
        }

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

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

        // (?:,|\n|^)("(?:(?:"")*[^"]*)*"|[^",\n]*|(?:\n|$))
        // https://stackoverflow.com/a/48806378

        var re = new Regex("(?!$)(\"((?:(?:\"\")*[^\"]*)*)\"|[^\",\r\n]*)(?:,|$)");
        var lines = Regex.Split(contentAsString, "\r\n|\r|\n");

        var result = new JArray(lines.Select(line=>{ 
            var matches = re.Matches(line);
            return new JArray(matches.Cast<Match>().Select(match => {
                return match.Groups[2].Success ?
                match.Groups[2].Value :
                match.Groups[1].Value;
            } ).ToArray());
        }).ToArray());

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

explain the regex and match groups

Swagger

This is the custom connection swagger YAML file.

swagger: '2.0'
info: {title: CustomCode, description: Custom Code, version: '1.0'}
host: johnliu.net
basePath: /
schemes: [https]
consumes: []
produces: []
paths:
  /Csv:
    post:
      responses:
        default:
          description: default
          schema:
            type: array
            items: {}
            description: Array
            title: Array
      summary: Parse CSV
      description: Parse CSV
      operationId: csv
      parameters:
      - name: value
        in: body
        required: true
        schema: {type: string, description: Text, title: value}
      x-ms-visibility: important
definitions: {}
parameters: {}
responses: {}
securityDefinitions: {}
security: []
tags: []

I want to add more parameters over time, and that will involve a tweak to the input parameters on the Swagger definition. But that’s probably a task for another day.

Links:

Write code in a custom connector | Microsoft Docs

C# code in Power Automate: let’s sort a string array? | It Ain't Boring (itaintboring.com)

Calculate Sum & Average in Power Automate using C# code in a custom connector - MoreBeerMorePower (hatenablog.com)

A group_by filter for liquid-node

/

Here’s a group_by filter for liquid-node

// https://stackoverflow.com/a/34890276/40857
const groupBy = function(xs, key) {
    return xs.reduce(function(rv, x) {
        (rv[x[key]] = rv[x[key]] || []).push(x);
        return rv;
    }, {});
};
// https://github.com/docs/liquid#registering-new-filters
engine.registerFilters({
    group_by: (input, key) => {
        const result = groupBy(input, key);
        // map dictionary back to arry
        return Object.keys(result).map(key1=>{
            return {
                key: key1,
                items: result[key1]
            };
        });
    }
});

Use this like so

{% assign grouped = data | group_by:'user' %}
{% for group in grouped %}
    <ul>{{group.key}}
        {% for item in group.items %}
        <li>{{item.name}}</li>
        {% endfor %}
    </ul>
{% endfor %}

Running Serverless Apollo GraphQL on AzureFunctions with cheap Azure Blob Table database(s)

/
leonardo-ramos-CJ4mbwSK3EY-unsplash.jpg

Merry Christmas holidays and happy new years.

This is a bit of a holiday reading, I wanted to start by give you the sense of what I’ve been tinkering as an experiment, and it looks increasingly like this is going into production before 2019 ends.


Plan

  • Have

    • I have a bunch of Azure storage Tables (about 70k rows of cheap database - at $1 / month storage) - they are spread out across several containers but for the basic purposes - I think of them as independent tables that lack proper relationships defined between them.
      I think that’s appropriate to describe most no-sql databases.

    • Azure Functions based CRUD REST APIs wraps around the Azure Blob Table - problem - no good caching or relationship mechanism. I wasn’t a big fan of keep rolling out CRUD REST endpoints, feeling that I should try find a different way.

  • Idea

    • Run a GraphQL server on Azure Functions

  • Learn:

    • If you want to run GraphQL on Azure Functions - there’s a dotnet way and there’s a NodeJS way

    • The dotnet version

      • https://www.tpeczek.com/2019/05/serverless-graphql-with-azure-functions.html

      • https://medium.com/@seekdavidlee/eklee-azure-functions-graphql-a-serverless-graphql-implementation-on-azure-484611c3680b

      • I’ve personally made a stack decision to stick to NodeJS/TypeScript/JavaScript, so I won’t be exploring the dotnet server.

    • The NodeJS version

      • A quick look around will send you to Apollo Server which has done 3 videos with Microsoft Azure/Channel9 on getting started, running on AppService, and running on Azure Functions (on consumption).

      • Part 1 https://www.youtube.com/watch?v=7R33hGFV4f0

      • Part 2 https://www.youtube.com/watch?v=Mt4bBpOdwyE

      • Part 3 https://www.youtube.com/watch?v=unUeFApHeT0

  • Write

    • Steps after Apollo + AzureFunctions 3-part videos.

    • Resolving GraphQL entities to Azure Blob Table

    • Resolving Relationships

    • Securing with Azure AD

    • Deploying to AzureFunctions

    • Reroute via Azure Function proxy

  • Future

    • Switch to Apollo RESTDataSource

    • Apollo Client with Angular and Observable bindings

    • Apollo Server with Redis cache



Learn

After the three videos, particularly the first and the third - to understand GraphQL server, and hosting it on AzureFunctions, you’ll end up with a working graphql server running on localhost showing you books array from memory. Getting here after watching three videos was extremely fast ~30minutes. Very surprising how much shortcut we took to get here this fast.

const typeDefs = gql`
    type Book {
        title: String
        author: String
    }
    type Query {
        books: [Book]
    }
`;

const resolvers = {
    Query: {
        books: () => books,
    },
};

let books = [
    {
        title: "A",
        author: "Mark B"
    },
    {
        title: "C",
        author: "John D"
    }
];

const playgroundSettings: any = {
    "schema.polling.enable": false
};
const server = new ApolloServer({ 
    typeDefs, 
    resolvers, 
    playground: {
        settings: playgroundSettings
    },
    debug: false 
});
export default server.createHandler();


A really fancy thing Apollo did was to put the graphql endpoint on POST, and run the playground test environment on GET. Pointing a browser to the same endpoint will show the playground.

Here is the playground running the sample books query. Debug works wonderfully running in localhost.

By default, the playground will do continuous polling on the endpoint. That’s going to keep the Function awake and incur a lot of costs. It might also keep bumping into any local debug breakpoints. So let’s turn that off. I also set debug to false.


Write - Resolving Azure Table

Next, we need to extend from here. My original AzureFunctions has REST endpoints that calls Azure storage Tables via the Azure.Storage SDK. So bring those in.

import { ApolloServer, gql } from "apollo-server-azure-functions"; 
import * as azure from "azure-storage";

And start to switch out the code

const typeDefs = gql`
    type Flow {
        RowKey: String
        name: String
        displayName: String
        critical: Boolean
        environmentName: String
        state: String
    }
    type Query {
        flows: [Flow]
    }
`;

const resolvers = {
    Query: {
        //books: () => books,
        flows: (parent,args,context) => azureTableQuery('flows'),
    }
};

const tableService = azure.createTableService();
const partition = "xxxx-partition-key";

const azureTableQuery = (table) => {
    let pRows = new Promise((resolve, reject)=>{

        let query = new azure.TableQuery().where('PartitionKey eq ?', partition);
        tableService.queryEntities(table, query, null, (error, result, response)=>{
            if(error){
                reject(error);
            }
            else {
                resolve(response.body['value']);
            }
        });
    });
    return pRows;
}

Notes: Azure.Storage does old style callbacks, so I’m wrapping them in a Promise that I can resolve. When I resolve - I want the raw JSON from the service, not the ‘tweaked’ JSON in result (it does weird things to your JSON entities, that’s a blog for another time).

A really nice thing about Apollo Server resolve function is that it knows how to handle promises naturally. So this tweak basically is me saying - hey, go fetch from Azure Storage Table from the ‘flows’ container.

To run this locally, we’ll need to store an Azure storage key in local.settings.json

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "",
    "FUNCTIONS_WORKER_RUNTIME": "node",
    "MyStore": "DefaultEndpointsProtocol=https;AccountName=mystore;AccountKey=FOOxxx"
  }
}

We can reference this like so:

const tableService = azure.createTableService(process.env["MyStore"]);

Write - Resolving relationships

const azureTableRetrieve = (table, key) => {
    return new Promise((resolve, reject)=>{
        tableService.retrieveEntity(table, partition, key, null,
        (error, result, response)=>{
            if(error){
                reject(error);
            }
            else {
                let entity: any = response.body;
                resolve(entity);
            }
        });
    });
}

const typeDefs = gql`
    type Environment {
        RowKey: String
        name: String
        displayName: String
        isDefault: Boolean
    }
    type Flow {
        RowKey: String
        id: String
        name: String
        displayName: String
        critical: Boolean

        environmentName: String
        ref_environment: Environment
        state: String
    }
}`;

const resolvers = {
    Query: {
        environment: (parent,{name},context) => azureTableRetrieve('environments', name),
        flows: (parent,args,context) => azureTableQuery('flows'),
        flow: (parent,{name},context) => azureBlobTableRetrieve('flows', name)
    },
    Flow: {
        ref_environment: (parent,args,context) =>
          azureTableRetrieve('environments', parent.environmentName),
    }
};

Notes:

I call “ref_” because I’m terrible at making property names. Lots of fields like “environmentName” or “environment” are already taken. It got too hard picking new names, I decided to call all entity references ref_

Deploy to Azure Functions

Need to deploy two parts:

Friends don’t let friends right click to deploy unless another friend put it in the right click menu. In that case, please don’t make me choose my friends.

Also, right click and upload local settings file to send our MyStore setting into Azure functions.

That’s two right clicks and deployment done!

Connect Azure Function proxy

My existing API routes are already covered with Azure Functions proxy. So I added a new route that takes graphql over to this separate Azure Functions. I also bury the functionkey in the route so users can’t see it.

Future

There’s a bunch more ideas for the future.

  • Explore federated authentication and user level security.

  • Figure out why Azure Storage SDK next generation doesn’t do Tables

  • Switch from using SDK to Azure Storage REST and use Apollo’s excellent RESTDataSource implementation instead (it already implemented HTTP caching, pagination, top, etc)

  • implement mutations, especially e-tag handling.

  • implement server side Redis cache

  • Implement Apollo Client to fetch data for my existing Angular front-end

  • Implement client side cache and observable pattern

Let me know if you find this interesting and want to follow along. I wrote this mostly as documentation for what I’m doing and the incremental changes I’m making as I go.

ForEach Property in #MicrosoftFlow JSON. With XPath? #microblog

/

I can't think of a way to do "ForEach Property Of JSON" in MicrosoftFlow or LogicApps - so I came up with this method that involves XPath.

Take example this JSON

{
  "a": 1,
  "b": 2,
  "c": 3
}

I want to do ForEach over the properties, so I need a way to convert this into:

[
  "a",
  "b",
  "c"
]

The usual suspects don't seem to work:

  • ForEach (only array)
  • Data Operations - Select (only array)
  • Array (wraps one object into array of one object)
  • CreateArray (wraps multiple objects into array)
  • Split - this could be used, but we'll have a hard time with nested JSON

Lets do XPath

XML objects must have one root element.  So let's wrap a root around our JSON

{
  "root": {
    "a": 1,
    "b": 2,
    "c": 3
  }
}

This next XPath splits each XML element under /root/ into a Nodeset (array of XML elements).

xpath(xml(outputs('Compose_2')), '/root/*')

Data Operations - Select

for each XML node, select just the name, map this for each node

xpath(item(), 'name(/*)')

Result

[
  "a",
  "b",
  "c"
]
for-each-json.png

I'm sure there'll be a better way one day.  But for now this will get me through.  I need this to be able to read nested JSON structures as part of my bigger plan.

JSON cheatsheet for Microsoft Flow

/

I have a big blog post in the works for a significant dive in Microsoft Flow functionality.  But I realized that before we get there, we need to GET GOOD at doing JSON in Microsoft Flow.

So this is the cheat sheet.  With all the caveats.  Enjoy!

Update: added a compound JSON object construction.

This is the part of the cheatsheet series on Microsoft Flow.

  1. JSON cheatsheet for Microsoft Flow (this article)
  2. Nested-Flow / Reusable-Function cheatsheet for Microsoft Flow
  3. Building non-JSON webservices with Flow
  4. One Connection to Proxy Them All - Microsoft Flow with Azure Functions Proxies
  5. Building Binary output service with Microsoft Flow

var X = { "x": 1 }

{ "x": 1 }

And this is the running results

X = JSON.parse("{ 'x': 1 }")

Flow-2.png 
"@json('{\"x\": 2}')"

X is updated.  But the run result panel for Set variable shows incorrect INPUTS value of X

{ ...X, { 'y': 2 } }
jQuery.extend( X, { 'y': 2 } )

"@union(variables('X'), json('{\"y\":2}'))"

Union is great - it'll let you add properties to existing objects easily.


var Y = { ...X, { 'y': 2 } }

Flow-4a.png

Use union to set variable

if ( Y.x != null )

sometimes, you need to check if an object contains a property

sometimes, you suddenly don't need the " double quotes for conditional statements. 
sometimes I feel this is a bug.

Flow-5.png

 

The UX can get quite silly.  It forgets how to render the basic view after you save.

(Y.x || Y.y)

"@coalesce(variables('Y').x, variables('Y').y )"

 

Use Coalesce to pick the first non-null value.  If the property doesn't exist this will error.  Protect the check with contains

X = Y

And we end this cheat sheet on a simple one.  Assigning variables to another.

Note you can't currently use the variable in setting the variable itself.

This would have been REALLY useful...  Because this would have let us keep appending to existing variable.

 

Finally, object composition
var Z = { z: Y }

"@json( concat( '{ \"z\": ', variables('Y'), ' }'  ))"

The result, see how a compound JSON object has been constructed.