Iterate over a comma-separated list in a Google Sheets Cell

Question

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?

Have you tried the given solution? Did it work for you? If not we could try finding out why. If yes, please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. — marikamitsos

player0 player0 · Accepted Answer · 2020-06-19T18:28:59

try:

=ARRAYFORMULA(SUBSTITUTE(IF(A2:A="",,"""list"" : [ "&REGEXREPLACE(
 TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(IFERROR(SPLIT(A2:A, ","))<>"", 
 "{ ""name"" : "&SPLIT(A2:A, ",")&" },♦", ))
 ,,999^99))), ",♦$", )&" ♦]"), "♦", CHAR(10)))

Iterate over a comma-separated list in a Google Sheets Cell

1 Answers