use of formula =PRINCPER() in google apps script

Question

Need some help from FRANCE. I try to use google sheet financial formulas ( through google apps script) without success.

function financialCalculation() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  
 
  var a = sheet.getRange(3,2).getValue()    //     2,3%/12  = 0,1917%           (monthly borrowing rate)
  var b = sheet.getRange(11,1).getValue()   //     1                            (first period)
  var c = sheet.getRange(2,2).getValue()    //     240                          (twenty years of credit  = 240 periods)
  var d = sheet.getRange(1,2).getValue()    //     180000 euros                 (the value of a property)

  spreadsheet.getActiveRange().setFormula(`PRINCPER(${a};${b};${c};${d})`);    //#ERROR!    =PRINCPER(0.0019166666666666666;1;240;180000)  
  /* result of formula in sheet shows    =PRINCPER(0.0019166666666666666;1;240;180000))   
  /* result of formula SHOULD BE         =PRINCPER(0,0019166666666666666;1;240;180000)      with "," result is -591,38 €
  
};

enter image description here

Answer 1

2 ways

function financialCalculation() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  
  var a = sheet.getRange(3,2).getValue().toString().replace('.',',')    //     2,3%/12  = 0,1917%           (monthly borrowing rate)
  var b = sheet.getRange(11,1).getValue()   //     1                            (first period)
  var c = sheet.getRange(2,2).getValue()    //     240                          (twenty years of credit  = 240 periods)
  var d = sheet.getRange(1,2).getValue()    //     180000 euros                 (the value of a property)

  spreadsheet.getActiveRange().setFormula(`PPMT(${a};${b};${c};${d})`);    
  
};

or

function financialCalculationV2() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  
  var a = sheet.getRange(3,2).getValue()*12*10000    //     2,3%/12  = 0,1917%           (monthly borrowing rate)
  var b = sheet.getRange(11,1).getValue()   //     1                            (first period)
  var c = sheet.getRange(2,2).getValue()    //     240                          (twenty years of credit  = 240 periods)
  var d = sheet.getRange(1,2).getValue()    //     180000 euros                 (the value of a property)

  spreadsheet.getActiveRange().setFormula(`PPMT(${a}/12/10000;${b};${c};${d})`);    
  
};

if this doesn't work for you, replace PPMT by PRINCPER