I'm working on a Google Sheet and want to automate the process of turning the columns into a custom JSON object. I have one column in my sheet that is a comma-separated list of strings:
"apple", "orange", "banana"
and I want to create an Array field with this. It's easy to just create an array with CONCATENATE:
=CONCATENATE(CHAR(34), "list", CHAR(34), " : [", A1, "]")
gives me:
"list" : ["apple", "orange", "banana"]
Now I want to turn each item in the list into it's own JSON object. My desired output is:
"list" : [ { "name" : "apple" },
{ "name" : "orange" },
{ "name" : "banana" }
]
If there's just one item in the cell, this is an easy substitute:
==SUBSTITUTE("{ name: ''}", "''", A1)
gives me what I want:
{ "name" : "apple" }
But the trick is that I don't know how many items are in the cell to begin with, and I want to SUBSTITUTE and CONCATENATE for each item.
I've been trying to avoid scripting so far; is it possible to do this with some clever functions? Or do I need to use scripting to do this now?
