Sending Excel Table To Custom API Connection and Displaying Result

[JohnFairhall]

Hi,

I'm pulling in a simple Excel Spreadsheet to PowerApps which has a table (called Table1) and columns 'Numbers' (format General) and '_PowerAppsID_' Users can add 4 digit telephone extensions in bulk directly into the spreadsheet or add / delete individual numbers in the PowerApp. I then want them to press a button which submits all the data in the table to a custom API which will return a script which I want the PowerApp to display (so it can be manually copied & pasted into PowerShell). The Swagger for the API is below. Despite being able to display the spreadhseet in a browser gallery and do all the necessary adding /deleting I can get it to submit to the API - intellisense keeps saying it's expecting a table. I've manually created some tbale data and subitted that sucessfully but all i get back in Migratrion script is a boolean which changes to true when the action is run.

UpdateContext({MigrationScript:MD110MigrationScriptGeneration.generateMigrationScript(Table({Value: 1, Value2: 2}))})

If someone could please help explain how to post the table data in the needed format to the API and then display it I'd be extremely grateful. It seems like it should be simple but I've spent a lot of time reading documentation, etc and not been able to get anywhere.

Many thanks,

John

API Swagger:

{
  "swagger": "2.0",
  "info": {
    "title": "MD110 Migration Script Generation",
    "version": "1.0",
    "description": "For generating a script to migrate a list of numbers from the MD110 to Skype."
  },
  "host": "XXXXXXXXX",
  "basePath": "/telephones/md110",
  "schemes": [
    "https"
  ],
  "securityDefinitions": {
    "apiKeyHeader": {
      "type": "apiKey",
      "name": "Ocp-Apim-Subscription-Key",
      "in": "header"
    },
    "apiKeyQuery": {
      "type": "apiKey",
      "name": "subscription-key",
      "in": "query"
    }
  },
  "security": [
    {
      "apiKeyHeader": []
    },
    {
      "apiKeyQuery": []
    }
  ],
  "paths": {
    "/script": {
      "post": {
        "operationId": "generateMigrationScript",
        "parameters": [
          {
            "name": "numberList",
            "in": "body",
            "schema": {
              "$ref": "#/definitions/NumberList"
            },
            "description": "List of numbers"
          }
        ],
        "consumes": [
          "application/json"
        ],
        "responses": {
          "200": {
            "description": "Success"
          },
          "405": {
            "description": "Invalid input"
          }
        },
        "produces": [
          "application/text"
        ]
      }
    }
  },
  "definitions": {
    "NumberList": {
      "type": "object",
      "required": [
        "numbers"
      ],
      "properties": {
        "numbers": {
          "type": "array",
          "items": {
            "type": "integer",
            "format": "int32"
          },
          "example": [
            2222,
            2223,
            2224
          ]
        }
      }
    }
  }
}

[timl]

@JohnFairhall

If you were to add a button and set the OnSelect property to just the function below, do you get the error that says generateMigrationScript expects a table?

MD110MigrationScriptGeneration.generateMigrationScript(Table({Value: 1, Value2: 2}))

When you say you manually created some table data and submitted it, how did you do that? Did you do that through the test section in the area where you created the custom connector?

I asked a very similar question a few days ago and @CarlosFigueira helped me alot.

https://powerusers.microsoft.com/t5/Connectors/How-to-build-a-web-service-method-that-accepts-a-tabl...

The OpenAPI code in my post accepts and returns a table. I'm confident that you'll find your answer here.

The main difference is that my web service definition produces "application/json", whereas yours produces "application/text". I find it's easier to specify this via the 'resposne' part of designer beneath, than through code.

In terms of the web service input, notice how I define the input array type in the 'definitions' section, and how I define the field names here. You might possibly have more success if you adopt this syntax.

Good luck!

[JohnFairhall]

Thanks for the fast response @timl

Sorry, I've not been very clear.

The function I mentioned is on the OnSelect Property of Button and in full its:

UpdateContext({MigrationScript:MD110MigrationScriptGeneration.generateMigrationScript(Table({Value: 1}))})

I display MigrationScript in a label, and it displays false until the button is pressed and then changes to true. So that is with me manually putting some test table data in the function rather than whats being brought in from the excel spreadsheet (I just can't get the syntax right to use that data, see below). Even just using test data though I cant seem to process the text response properly as PowerApps seems to think the API only returns a True / False boolean.

What I really want is for the button to use the data from Excel that PowerApps has a connection to. My very incorrect way of doing this is:

UpdateContext({MigrationScript:MD110MigrationScriptGeneration.generateMigrationScript(Table1.Numbers)})

Table1 is the table in the excel spreadsheet and Numbers is the Column header which I want all the entries from to submit to the API.

I have found a way to do this which for my purposes is better than using PowerApps. I've setup a flow, which when it detects a change ot the excel spreadsheets, pulls all the Numbers column into a variable array and then submits it to the APIs custom connect and saves the result as a new file. So I don't think there's anything wrong with the API or its swagger. I'd still like to understand how to do it with PowerApps though as I'll definitely be trying to do similar things in future.

Thanks,

John

[timl · Accepted solution]

Hi John,

I think something like this might take you closer. This formula returns the single column Numbers from Table1, and renames the column name to Value. This should match the data structure of your working formula.

UpdateContext(
  {MigrationScript:MD110MigrationScriptGeneration.generateMigrationScript(
     RenameColumns(ShowColumns(Table1, "Numbers"), "Numbers", "Value"))
  }
)

[JohnFairhall]

 That's great thank you very much. Your function is working and sending the data to the API. PowerApps is still treating the returned MigrationScript as a boolean, so there's still a piece of the puzzle missing.

[timl]

Glad that part works now.

The remaining issue is because PowerApps doesn't know the structure of the data that generateMigrationScript returns.

The way to fix this is to configure the Response section of your custom connector. I'd call your web service with Postman, and paste the output into the header and body sections here.

Once you do this, PowerApps should be able to work with the data that generateMigrationScript returns. 

[JohnFairhall]

Thank you @timl for all your help. I'm marking your response as the accepted solution. I've not been able to get the display working, but I think thats down to the format that its outputting and easily solved by making sure future APIs we that we create and want to use in powerapps return JSON 

[timl]

Wonderful, I'm glad you were able to get at least half of this to work.