Solved: Turning Tab Delimited JSON into Array for Table Pasting

Turning Tab Delimited JSON into Array for Table Pasting

Hello, all. Long time reader, first time poster.

Could I please have some help with the finishing touches on what I have built so far? I can’t get the final steps right.

The big picture: I want to be able to paste content from an excel file into a form, feed it through Powerautomate, and paste it into another excel file. The first file will be raw data from things like sales data, and the latter file will have scripts, formulas, etc, which makes the whole process worthwhile. I am using forms because I want the front end to be as simple as possible for my users.

Said another way, I want to copy excel data which looks like this:

Into Microsoft Forms, and from there Powerautomate takes the Tab Delimited (with line breaks) information and puts it into a format that allows me to paste into a Onedrive file.

What I have so far:

My test form submission looks like this (the step “Compose Faux Array Input”):

{

"responder": "anonymous",

"submitDate": "7/27/2023 2:38:51 AM",

"rb348ff": "Column A\tColumn B\tColumn C\n0.57263906\t0.721963989\t0.233819196\n0.658907399\t0.511149912\t0.139190558\n0.708845808\t0.988536183\t0.74282814\n0.270527158\t0.707212518\t0.709652369\n"

}

Then I parse JSON and set it up as a string variable:

That gets me this far:

Then I split the line breaks and tab breaks:

For the top function I am doing this: “Compose - Split by Line Break”:

split(variables('Array_JSON_Output'),decodeUriComponent('%0A'))

And then from “Apply to Each”, I am taking the output from “Compose - Split by Line Break”, and using the nested Compose:

split(items('Apply_to_each'),' ')

Note: that gap is a “tab”.

The results look like:

And so on….

So how do I get this into a format that I can start pasting into a linked excel file? I can’t use “Select” + “Add a Row to a Table” if my Columns are not variables (?).

Other notes:

The input size will not be regular. Approx 5 to 8 columns, up to 100s or 1000s of rows.
Column names will change.
This type of coding is not in my comfort zone, and I do not know enough Javascript. I am more comfortable in Excel and PowerQuery.

Various thoughts:

Is this approach a dead end?
Can I do a shortcut using Split + Appending an Array variable + “Create CSV Table”?
“Join”? String vs Array variables? “Union”? Transpose so I can get into a tidier JSON array? Concatenate/append into a single string at the end? So many things to consider!

Thank you very much in advance. I can answer follow up questions.

Quick Links

Leaderboard > Power Automate - Building Flows