How to Flatten a vertical Array (plugin use)

Hi there,

with the good use of the Plugin now it is possible to get the 90% of what I can do through googlescript/sheet, in this case Im getting the value from a column, make those Unique and Join them.
But what Im in need is to take the result of the JOIN (the splitted array) and distribute in a column, like is possible through Googlesheet via the command FLATTEN, that’s in order to read those values through an Inline list or via the “choice” (combo box).

Any help?

Take a look at this:

I had to do something very similar just a few days ago. In my case, I wanted to transpose an array column. The general approach that I came up with (which worked for me) was:

  • Create another table with numbered rows, beginning at zero
  • Create a joined list column, targeting the array column
  • Turn that into a split text column
  • Create a single value column, targeting the Nth item from the start of the split text column, where N is the row number
  • That single value column can then be used in an inline list or choice component

The method I used, and the method that you see in Bob’s video are quite similar. I guess one main difference is that Bob’s example works with a fixed list of items, whereas my approach is more dynamic. As long as you have enough numbered rows in the table, it will work for any array, regardless of the actual values.


you know, more or less I had the same idea (but using a numbered column), I didn’t go through this because I was thinking was a bit too elaborated… and actually it is, I would prefer to have a kind o command that make it easier.
But ya, Ill try your way for now.

Thanks a lot.

Yes, I agree that it’s more complicated than it should be. And I’m certainly open to ideas for making it simpler.

Not even the time to think much more and the solution came easily!
Wow, yesterday night I lost 4 to 5 hours to think how to do that…

Here the pretty miracle!

If some one can improve it, please let me know!

Nice, but you have a couple of extra columns that you don’t need. Once you have your Unique items, then you only need two more columns:

  • the equivalent of your begin column, except start the numbering at zero
  • a single value column, that takes “begin” from start, from the unique column.

Here is a contrived example to show what I mean:


That’s simply great! We (@Robert_Petitto ) should make a tutorial of this.
What I would like to have now is a auto-numbering column, so that we can have a real dynamic feature, because if you exceed the provided number then it stop to work.
Or do you already have a solution?

Yes, that would be useful. And no, I don’t have a solution.
For my specific use case, I was starting with an array column with a fixed size.
So I just needed to create a matching number of numbered rows.

I don’t know if somehow via the CODE column, maybe with javascript… maybe not
Hyperformula ROW() doesn’t works, it doesn’t understand the ROW presence.
Feature request?

I wouldn’t be surprised if we have it fairly soon.

1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.