Arrays within a single cell

Hi all!

Can you think of an easy and fool prove way of using an array within a single cell?

What do I actually want to do?
I need Zapier to push a matrix to coda. This matrix always has columns rows, but a varying number of rows. At the moment I require 1-10, but soon it might be 1-100 (which would make it unfeasible to push the data in individual colums).

image

So I want to be able to extract “oranges” and “2”, “apples” and “3”, “tequilla” and “5”.

As soon as the cell with “Oranges, Apples, Tequilla” is recognized as an array, I could just use the nth()-forumla. But is there any way to get it to that point?

I think the “easy” way currently is to convert your data to CSV and then import it.

Longer-term, it would be ideal if we could import multiple rows via JSON or XML formats.

1 Like

Ideally I would have Zapier push the data through, as the table is going to grow every few hours.

1 Like

Not sure if this was around in May, but maybe the ParseJSON formula will be helpful https://coda.io/formulas#ParseJSON

HI - ParseJSON was around then; but I do not think it can handle multiple rows as in the situation described.

But perhaps the new Automation feature could help with this now.

1 Like

@Philipp_Alexander_Asbrand-Eickhoff not sure if you are still looking for an answer but here’s an approach. you could get desired result with some formulas and i would not suggest automation for this use case.

Hi! Thanks a lot for this answer!

It helped me build a value-complexity prioritisation table using PercentileRank.

What I don’t understand is WHY it has to be so complex to preform operations with arrays. I just wanted to do a simple array sum but it was SO difficult. Am I doing something wrong?

This is my document:

hi @Jose_Fernandez,

sorry that its not obvious and welcome to Coda Community-

could you please help me understand what exactly you are trying to achieve - i see that you have a value and complexity - but I am completely lost on the formula over there - would you mind giving an example values (by typing them manually) so i can understand this better.

Thank you.

Hi Krunal,

Thanks for coming back to me. Well… I was trying to create a prioritisation matrix (complexity vs value).

The idea was to have the Product Manager select how valuable is something and the Developer would select how complex is the task (both selector use a Fibonacci scale in my Coda example). The operation then subtracts VALUE - COMPLEXITY and gives you X, then I was trying to find out in which percentile was X in relation to the other tasks using PercentileRank. Here’s a simpler example:

image

For this, it would be very similar to Excel’s formula.

The formula would be =PercentileRank(Tasks.X, thisRow.X)

here’s a document with exact same data you have up there with chart and a relative rank

Thanks Krunal. Yes. I wasn’t very clear because I did that exact chart on Excel for simplicity, but on my coda example above I’m not using column X.

My question is: how would you do the multiplication on column X directly on the percentileRank column?

Can you multiply arrays?

@Jose_Fernandez you can have a formula that is PercentileRank(Tasks.FormulaMap(Value-Complexity), thisRow.Value-thisRow.Complexity) then. I personally always prefer intermediate columns (and then hide them as needed) as it makes debugging much easier.

p.s I also updated document above to include above formula

Thanks a lot Krunal! I didn’t know how to use FormulaMap! This clarifies it a lot.

I was trying something like FormulaMap(Tasks.Value-Tasks.Complexity).