在Google Apps脚本中使用公式= PRINCPER()

Question

需要法国的一些帮助。我尝试在没有成功的情况下使用Google Sheet Sheet Financial公式（通过Google Apps脚本）。

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 €
  
};

在此处输入图像描述

Answer 1

两种方法

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})`);    
  
};

或

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})`);    
  
};

如果这对您不起作用，请替换PPMT由Princper替换