CODEWITHSUNDEEP
power-automatesmartsheet-api
bmickey
bmickey

Reputation: 47

Issues getting flow to send the correct json in body when using powerautomate's http request

I'm using a PowerAutomate Flow to call a native SmartSheet API that does a POST. The POST IS working but my MULTI_PICKLIST type field is not being populated correctly in SmartSheet due to the double quotes.

The API is: concat('https://api.smartsheet.com/2.0/sheets/', variables('vSheetID'), '/rows')

In the Body section of the http rest API I form my JSON and the section of interest looks like this:

{
    "columnId": 6945615984781188,
    "objectValue": {
      "objectType": "MULTI_PICKLIST",
      "values": [
        @{variables('vServices')}
      ]
    }
  }

My variable vServices raw output looks like:

{
    "body": 
    {
        "name": "vServices",
        "value": "Test1, Test2"
    }
}

The format needs to be like this (it works using PostMan).

{
    "columnId": 6945615984781188,
    "objectValue": {
      "objectType": "MULTI_PICKLIST",
      "values": [
        "Test1","Test2"
      ]
    }
}

As a step in formatting my vServices variable I tried to do a replace function to replace the ',' with a '","' but this ultimately ends up as a \",\"

Any suggestion on how to get around this? Again, ultimately I need the desired JSON Body to read but haven't been able to achieve this in the Body section:

{
    "columnId": 6945615984781188,
    "objectValue": {
      "objectType": "MULTI_PICKLIST",
      "values": [
        "Test1","Test2"
      ]
    }
}

vs this (when using replace function).

{
    "columnId": 6945615984781188,
    "objectValue": {
      "objectType": "MULTI_PICKLIST",
      "values": [
        "Test1\",\"Test2"
      ]
    }
}

Thank you in advance,

Upvotes: 1

Views: 2622

Answers (1)

bmickey
bmickey

Reputation: 47

I resolved my issue by taking the original variable, sending it to a compose step that did a split on the separator of a comma. I then added a step to set a new variable to the output of the compose step. This left me with a perfectly setup array in the exact format I needed! This seemed to resolve any of the issues I was having with double quotes and escape sequences.

Upvotes: 1

Related Questions