Convert String to Array to JSon

(0) Share

Report

Posted on 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?

Categories:

Building flows

I have the same question (0)

All responses (5)

Answers (1)

SanmeshG 1,947 Moderator on at

Like (0)

Report

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,

Was this reply helpful? Yes No
EBelmonte 23 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Chriddle 8,443 Super User 2025 Season 2 on at

Like (2)

Report
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" ]

Was this reply helpful? Yes No
SanmeshG 1,947 Moderator on at

Like (1)

Report

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,

Was this reply helpful? Yes No
EBelmonte 23 on at

Like (0)

Report

Thank you @Chriddle @SanmeshG I appreciate both of you.

Was this reply helpful? Yes No