Overview: I have an internal order form that dumps orders into a Google Sheets. Unfortunately, the form tool I’m using dumps all product information into a single cell. Each is a new line, but all in one cell. Like this
There is potential an order could have more than just 2 items like pictures above.
My Scenario:
I’m first parsing by each new line ([^\n]*\n+).
I’m aggregating the parsing so that I can iterate on it (might be over-kill. Total newbie here)
Then I’m parsing each line to pull out the Title, Price, Size, and Quantity:
^(?<ProdTitle>.*)(?=\s+\()|(?<=Amount: ).*?(?<Price>.*)(?=\s+USD)|(?<=Size: ).*?(?<Size>.*)(?=\,)|(?<=Quantity: ).*?(?<Qty>.*)(?=\))
Which gives me this:
Ideally what happens next is I can compose text in this format:
{
"ProdTitle": "{{59.ProdTitle}}"
"Price": {{59.Price}}
"Size": "{{59.Size}}"
"Quantity": {{59.Qty}}
}
But what I’m getting is, 8 operations, each with 1 peice of the parsed info:
Ideally I can get each line in one set of brackets that will repeat for the same number of products in the cell, so in the above I’d get:
{
"ProdTitle": "SS TEE"
"Price": 50
"Size": "L"
"Quantity": 1
},
{
"ProdTitle": "BUTTON DOWN LS JACKET"
"Price": 175
"Size": "XL"
"Quantity": 1
}
Any help is greatly appreciated!
