Power Automate - Building Flows

Answered

Convert String to Array to JSon

Like (0) Share

Report

Posted on 23 Feb 2024 13:06:10 by EBelmonte

Hello,

I have this string that was originally like this

{"Test Last Name","Test First Name","T","",35386,45341}

and used split function to become this

[

"{\"Test Last Name\"",

"\"Test First Name\"",

"\"T\"",

"\"\"",

"35386",

"45341}"

]

How do you make a JSON out of this or from the original text?

All responses (5)

Answers (1)

EBelmonte 23 on 27 Feb 2024 at 12:37:15

Like (0)

Report

Re: Convert String to Array to JSon

Thank you @Chriddle @SanmeshG I appreciate both of you.
SanmeshG 1,947 Moderator on 27 Feb 2024 at 08:25:57

Like (1)

Report

Re: Convert String to Array to JSon

okay !

Hi @EBelmonte ,

What @Chriddle said makes sense , so you can iterate on this array and use the index of the array items to pass the values to the correct columns in your SP list.

Thanks,
Verified answer

Chriddle 7,932 Super User 2025 Season 2 on 26 Feb 2024 at 17:02:53

Like (2)

Report
Re: Convert String to Array to JSon

The output from office scripts looks like this:

{ "result": [ [ "D Test Last Name", "D Test First name", "T", "", "Male", "Single", "Brother", "28/May/2002" ], [ "", "", "", "", "", "", "", "" ], [ "", "", "", "", "", "", "", "" ] ], "logs": [] }

This is already json 😉

Assuming this is the output of an action named "Compose" you can just use

first(outputs('Compose')['result'])

to get the following array

[ "D Test Last Name", "D Test First name", "T", "", "Male", "Single", "Brother", "28/May/2002" ]
EBelmonte 23 on 23 Feb 2024 at 14:22:08

Like (0)

Report

Re: Convert String to Array to JSon

Hello @SanmeshG,

First, thank you for taking the time to read thru my query.

1. The data originally came from an Excel an range - I used Office Scripts (see below) to get the values. I can't convert the data to a table as I'm not the owner of the template.

function main(workbook: ExcelScript.Workbook) {
  let rngDep = workbook.getWorksheet('Sheet1').getRange('L20:S25')
  return rngDep.getTexts()
}

The output from office scripts looks like this:

{ "result": [ [ "D Test Last Name", "D Test First name", "T", "", "Male", "Single", "Brother", "28/May/2002" ], [ "", "", "", "", "", "", "", "" ], [ "", "", "", "", "", "", "", "" ] ], "logs": [] }

2. I then use compose to just get the "result" portion of the office script output - to replace the brackets with curly braces:

replace(replace(string(body('Run_script_-_Emp')?['result']),'[','{'),']','}')

The output for the replace:

{{"D Test Last Name","D Test First name","T","","Male","Single","Brother","28/May/2002"},{"","","","","","","",""},{"","","","","","","",""}}

3. I then use another compose to remove the first and last curly braces:

substring(outputs('To_String_Then_Replace_-_Emp'),1,sub(length(outputs('To_String_Then_Replace_-_Emp')),2))

The output for substring is:
{"D Test Last Name","D Test First name","T","","Male","Single","Brother","28/May/2002"},{"","","","","","","",""},{"","","","","","","",""}

4. Lastly, I used the Split function

split(outputs('Remove_First_Last_Char_-_Emp'),',')

The output for the split is:
[
  "{\"D Test Last Name\"",
  "\"D Test First name\"",
  "\"T\"",
  "\"\"",
  "\"Male\"",
  "\"Single\"",
  "\"Brother\"",
  "\"28/May/2002\"}",
  "{\"\"",
  "\"\"",
  "\"\"",
  "\"\"",
  "\"\"",
  "\"\"",
  "\"\"",
  "\"\"}",
  "{\"\"",
  "\"\"",
  "\"\"",
  "\"\"",
  "\"\"",
  "\"\"",
  "\"\"",
  "\"\"}"
]
All these with the thought of being nearer to JSON. I'm just basically combining every bit of information that I can find online. I hope there's a better/quicker way of doing this. The end goal is I should be able to write those data to a SharePoint list.
SanmeshG 1,947 Moderator on 23 Feb 2024 at 13:29:57

Like (0)

Report

Re: Convert String to Array to JSon

Hi @EBelmonte ,

How is that string being generated ?

I wonder if there is any way to create JSON itself at first place than creating the string and then creating the array.

But one thing that I can think of is , you can iterate on that array using apply to each that you are getting after splitting and use the even and odd index of that to convert it into a JSON and then append the objects to create an array of objects given.

But , I am bit confused with the string that you have shared which of that is a key and which one is a value?

Thanks,

Community site session details

Convert String to Array to JSon

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Leaderboard > Power Automate

Featured topics

Product updates

Community site session details

Convert String to Array to JSon

Helpful resources

News and Announcements

Quick Links

Announcing our 2025 Season 2 Super Users!

Paul Stork – Community Spotlight

Congratulations to the June Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate

Featured topics

Product updates