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.
