Easier way to write custom formula functions

[binyomen]

I've just started using formulas, and I really like them! However, I've been having trouble writing custom functions that take a range like Max, Sum, etc.

In #114 (comment), you provide an example where the function SumTime takes in the range as a string, then converts it to a list with GetCellRange. This only seems to work if your cursor is in that column, however, since GetCellRange uses the cursor position if a line and column number aren't provided. This doesn't match the behavior of the builtin functions, which decide the the column based on the cell you're setting (if it isn't explicit).

I've been trying to figure out how to pass in the correct line and column number similarly to the builtin functions, but I haven't been able to find a way to do that. The closest I've gotten is copying a bunch of code from EvaluateFormulaLine to get the correct line, but I don't have easy access to the actual formula line itself to parse out the column. I have tried passing in the column manually in the range, like '1,4:-1,4' instead of '1:-1', and that works but only if the cursor is somewhere on the table. If the cursor is on the formula line, it produces an empty range.

It's very possible I'm missing something here, but if I'm not it would be really great to have an easier way to implement these kind of functions. Or at least documentation on the "hard" way to do it.

Thanks so much!

[dhruvasagar]

@binyomen perhaps you can share an example of what you're trying to do ? It'll help me better understand what you're trying to do.

[binyomen]

Thanks for the quick response! I wasn't being very specific, sorry :)

Basically I've got a table where the first column is the name of an item, the second column is the quantity of the item, and the third column is the price of each item. Something like this:

| Name   | Quantity | Cost |
|--------|----------|------|
| Item 1 | 1        | 5    |
| Item 2 | 4        | 2.50 |
| Item 3 | 20       | 0.10 |

I want to write a function that can take a range like 1,2:-1,3 and multiply the two columns together for each row, then sum the total at the end. However, I have been unable to find an easy way to write a function that takes a range as an argument.

I hacked around this by adding a fourth column that contains the products of each row ($4 = $2 * $3) and then summing them in a separate formula ($-1,4 = Sum(1:-1)), but I can't do it without the extra row:

| Name   | Quantity | Cost | Total |
|--------|----------|------|-------|
| Item 1 | 1        | 5    | 5     |
| Item 2 | 4        | 2.50 | 10.0  |
| Item 3 | 20       | 0.10 | 2.0   |
|        |          |      | 0     |
| -      | -        | -    | 17.0  |
%% tmf: $4 = $2 * $3;$-1,4 = Sum(1:-1)

Similarly, I've been trying to look into ways to do other statistical operations on a single column besides Sum, Max, Average, etc. But that's blocked on this as well.

[dhruvasagar]

I was able to do it like this @binyomen :

function! functions#SumOfProducts(range) abort
  let vals = tablemode#spreadsheet#cell#GetCellRange(a:range)
  let nrows = len(vals[0])
  let ncols = len(vals)
  let sum = 0
  for irow in range(nrows)
    let p = 1
    for icol in range(ncols)
      let p *= str2float(vals[icol][irow])
    endfor
    let sum += p
  endfor
  return sum
endfunction

" | Name   | Quantity | Cost |
" |--------+----------+------|
" | Item 1 | 1        | 5    |
" | Item 2 | 4        | 2.50 |
" | Item 3 | 20       | 0.10 |
" |        |          | 17.0 |
" tmf: $4,3=functions#SumOfProducts('1,2:3,3')

NOTE: tmf: $4,3=functions#SumOfProducts('1,2:-1,3') Also works the same way.

[dhruvasagar]

Note that this does require your cursor to be within the table, that's something I shall fix.

[binyomen]

Thanks for looking into this! I think that solution works. And yeah, working with the cursor on the formula line would mostly give feature parity with built-ins I think, except for shorthand like "1:1" which isn't super crucial anyway :)