2

I need to create an array out of google sheets columns:

product feature_a feature_b feature_c Array
p1 1 1 1 ["feature_a","feature_b","feature_c",]
p2 0 1 0 ["feature_b",]
p3 1 0 0 ["feature_a",]
p4 0 0 0 []

Currently my formula is

=ArrayFormula(if(isblank(A2:A);"";"[" & if(B2:B = 1; concatenate(char(34);$B$1; char(34);char(44));"") & if(C2:C = 1; concatenate(char(34);$C$1; char(34);char(44));"") & if(D2:D = 1; concatenate(char(34);$D$1; char(34);char(44));"") & "]"))

Is there a more elegant way to achieve this, esp. without referencing each column individually? My original sheet has much more columns.

Google sheet: enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
Zin Yosrim
  • 1,602
  • 1
  • 22
  • 40
  • 1
    Your question can be greatly improved if you add a table to the question. [Tables](https://webapps.stackexchange.com/a/161855/) are a better alternative than spreadsheets to show your data structure. If you share spreadsheets, make sure to also add images of your sheet to avoid closure of your question, as questions here must be [self](https://meta.stackoverflow.com/a/260455/) [contained](https://meta.stackexchange.com/a/149892). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), when you share Google files. – TheMaster Jul 06 '22 at 17:32
  • @TheMaster btw, (yes I am aware that its naive to expect some reason from mods) but did you try to raise the awareness of this google sheets security issue with them and demand some immediate action (like maybe some popup box with your message whenever the user enters google sheets URL into the question box or something) ...nah, scratch that, mods are programmed not to care about important stuff... – player0 Jul 06 '22 at 21:33
  • @player0 I'm sure mods cannot do that without help from company staff. Furthermore, it'll require massive consensus from all parties involved(But I'm the solo crusader here). – TheMaster Jul 07 '22 at 05:55
  • 1
    @player0 took me a bit to understand what your formula is doing. But, great solution - thanks – Zin Yosrim Jul 07 '22 at 07:10

1 Answers1

2

try:

=INDEX(IF(A2:A="";;"["&SUBSTITUTE(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IF(B2:D=1; """"&B1:D1&""""; ));;9^9))); " "; ",")&"]"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124