Shape Response Data in a Custom Connector using C# Code

Integrating ERP systems with Power Platform can be a challenging task, especially when dealing with API responses that are not compatible with PowerApps.

Recently, I encountered a similar issue while working on a custom connector for Kingdee K3, a popular ERP system in China. The ExecuteBillQuery API returned a pure 2-dimensional array without any key or field name, making it impossible to pass data to PowerApps without defining the payload of the response.

In this blog post, I’ll share my approach to shape the 2D array into 1D using custom C# code in a custom connector.

[["1","2","3"],["4","5","6"]] // ExecuteBillQuery API Response

BTW, as I’m sure you all know, PowerApps don’t have arrays, even you write down [1,2,3], powerapps will automatically recongize it as a single-column tables with field named Value! Not to mention dealing with a 2-dimensional array.

Convert 2D array to 1D

What we need to do is to Shape the 2D Array into 1D, as below. Since the fieldname is dynamics based on request query, I choose to ABC to identify the field value.

Shape data using custom code

To convert the 2D array into the desired format, we need to write custom C# code using the preview feature called “Custom Code”. The code must be in C#, have a maximum execution time of 5 seconds, and can’t be more than 1MB. Learn more

The following code snippet converts the 2D array into a list of dictionaries and then converts it into a JArray that can be passed to PowerApps.

public class Script : ScriptBase
{
    public override async Task<HttpResponseMessage> ExecuteAsync()
    {
        // Check if the operation ID matches what is specified in the OpenAPI definition of the connector
        if (this.Context.OperationId == "ExecuteBillQuery")
        {
            return await this.Shape2DArray().ConfigureAwait(false);
        }

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

    private async Task<HttpResponseMessage> Shape2DArray()
    {
        // Use the context to forward/send an HTTP request
        HttpResponseMessage response = await this.Context.SendAsync(this.Context.Request, this.CancellationToken).ConfigureAwait(continueOnCapturedContext: false);

        // Do the transformation if the response was successful, otherwise return error responses as-is
        if (response.IsSuccessStatusCode)
        {
            var responseString = await response.Content.ReadAsStringAsync().ConfigureAwait(continueOnCapturedContext: false);
            
            // Example case: response string is some JSON array
            var inputArray = JArray.Parse(responseString);

            List<Dictionary<string, string>> list = new List<Dictionary<string, string>>(inputArray.Count);

            // convert 2D JSON Array to List Dictionary
            foreach (JArray row in inputArray)
            {
                Dictionary<string, string> dict = new Dictionary<string, string>();

                for (int i = 0; i < row.Count; i++)
                {
                    string key = ((char)('A' + i)).ToString(); // Convert index to corresponding letter
                    string value = row[i].ToString();
                    dict.Add(key, value);
                }
                
                list.Add(dict);
            }

            // Convert the list to a JArray
            JArray jsonArray = JArray.FromObject(list);
            
            // Wrap the original JSON object into a new JSON object with just one key ('Output')
            var newResult = new JObject
            {
                ["Output"] = jsonArray,
            };
            
            response.Content = CreateJsonContent(newResult.ToString());
        }

        return response;
    }
}

Testing the custom connector:

After writing the custom C# code, we need to test it to ensure it works as expected. We can test the custom connector in the custom connector page or PowerApps. In our case, the test passed in both environments, as shown in the following screenshots:

PS: Don’t forget to paste the payload to reponse!

Wrap up

In conclusion, shaping response data using custom C# code in a custom connector is a powerful technique to make API responses compatible with PowerApps. While the code snippet provided in this blog post is specific to Kingdee K3’s ExecuteBillQuery API, you can modify it to work with other APIs that return a 2D array without any key or field name. I hope this blog post helps you in your Power Platform integrations with ERP systems.