Inserting a text in formula for google apps script-CodePudding

I am trying to insert a formula in Google app script but facing errors even after multiple approaches.

var b7_range = b1_range.offset(0,9).getA1Notation();   // M43
var b8_range = b1_range.offset(0,14).getA1Notation();  // R43
var b9_range = b1_range.offset(0,23);                  //AA43

const Custom = "Custom";
var formula = "=" "IF(" "$" b7_range  "==" Custom "," Custom "," "($" b7_range "*(1-" b8_range ")))";
b9_range.setFormula(formula);

after execution I am getting in cell AA43 as

=IF($M43==Custom,Custom,($M43*(1-R43)))

What I want is:

=IF($M43=="Custom","Custom",($M43*(1-R43)))

I even tried

"=" "IF(" "$" b7_range  "== Custom, Custom," "($" b7_range "*(1-" b8_range ")))" 

//and 

"=" "IF(" "$" b7_range  "==" "Custom" "," "Custom" "," "($" b7_range "*(1-" b8_range ")))"

but not able to get the desired output, please help!

CodePudding user response：

In your situation, how about using the template literals as follows?

From:

var formula = "=" "IF(" "$" b7_range  "==" Custom "," Custom "," "($" b7_range "*(1-" b8_range ")))";

To:

var formula = `=IF($${b7_range}=="${Custom}","${Custom}",($${b7_range}*(1-${b8_range})))`;

In this modification, when if b1_range is the cell "D43", the formula of =IF($M43=="Custom","Custom",($M43*(1-R43))) is put to the cell "AA43".

Reference:

Template literals