How to make a relative cell reference in a function reference within the setFormula() method?

Question

The code below is part of a script. It simply multiplies a value in the C-range with a value in the D-range with a price that is calculated with the function calculatePrice. The function takes a parameter (a label found in the B-Range) upon which is loops through an object to find a matching price. All of that works, but cell B2 should be B3 in the next line, B4 in the line after that etc. How do I get this to work in this formula? I have tried B2:B but that does not work. Help is much appreciated.

sheet.getRange(2,5).setFormula('=ArrayFormula(if(not(isblank($C$2:$C)),$C$2:$C*$D$2:$D*calculatePrice(b2)," "))');

Answer 1

Try the following code instead:

sheet.getRange(2,5).setFormula('=ArrayFormula(if(not(isblank(C2:C)),C2:C*D2:D*calculatePriceArray(B2:B)," "))');

As you may notice, your function "calculatePrice" was wrapped by "calculatePriceArray":

function calculatePriceArray(input) {
  return Array.isArray(input) ? 
    input.map( function(e) {
      return e.map( function(f){
        return calculatePrice(f);
      })
    }) : 
  calculatePrice(input);
}

because we want to return an array. Really we have used the known solution here.