Trouble expanding a function to every row in column

Question

I have a spreadsheet which has 3 different sheets each containing a column marked A column "Price (buy)" and B column "Price (sell)".

I want to calculate the "Price (sell)" column with a function but I can't find a way to expand it to new rows being added.

I have tried with ArrayFormula but can't get it to send a new row to the function and it simply returns the same result each time. Below is one of the ArrayFormula I've tried.

=ArrayFormula(iferror(if(A:A="";;if(row(B:B)=1;"Price (sell)";if(isblank(A:A)=False;sell_price($A2))))))

But this only returns 20 on each row regardless of the price in column A.

Example on how it should work:

Make a new row.
Enter price in the "Price (buy)" cell, let's say 10.
The function (below) would automatically process and the answer would be placed in the "Price (sell)" column on the same row.
"Price (sell)" should show 20.

The function would be different so this is just for the sake of example.

function sell_price(price) {
return price + 10;
}

Is there a way to make this work on all 3 different sheets and automatically expand to new rows being added in any of the 3 sheets? The information would come from a script via the API if that makes any difference.

When you say "make a new row", do you mean inserting a new one, or simply starting to write on the next row? — Florian Minges
Also: have you thought about simply putting a formula into every cell in the B column, instead of just using one arrayformula which is usually a bit trickier to handle? — Florian Minges
Ah yes, insert new row is what I meant. Sorry about that. I have thought about having a formula on every cell in B but I thought maybe it was possible to do it somehow else so I don't have to add that to the script, which also updates etc the information. — Shopro

Florian Minges Florian Minges · Accepted Answer · 2017-01-22T12:33:54

Seems like you have a tiny little error in your formula, where instead of referencing the whole column, you only reference cell $A2. If you change $A2 to A:A it should work. :)

EDIT: To complement to this, it seems as if your sell_price() function can't handle multiple cells at the moment. What you could do is to write a wrapper function for it, that loops over every cell in the range you input, and makes the calculation for them. Example below.

function wrapper(values) {

  // creates an empty array
  var returnValue = []; 

  // loops over every value in values in order
  for (var index = 0; index < values.length; index = index + 1) {

    // applies the formula
    returnValue[index] = sell_price(values[index][0]);
  }

  // outputs the scores of all the cells
  return returnValue;
}

function sell_price(value) {
  return value + 10;
}

Then you could simply call the wrapper function instead of sell_price() in your spreadsheet and it should work, ie wrapper(A:A).

Trouble expanding a function to every row in column

1 Answers