3

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?

player0
  • 124,011
  • 12
  • 67
  • 124
nathan lachenmyer
  • 5,298
  • 8
  • 36
  • 57

1 Answers1

4

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)))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124