Convert String to Array to JSon

[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?

[SanmeshG]

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,

[EBelmonte]

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.

[Chriddle · Accepted solution]

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

[SanmeshG]

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,

[EBelmonte]

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