javascript - 如何在 App Script 中正确使用 setFormula

Question

我在应用程序脚本中正确使用 setFormula 时遇到问题，我尝试在不确定范围的单元格中使用 setFormula，但我不知道如何指定要增加的行范围，它不仅仅是一个特定范围。我尝试制作的脚本是一个条件，如果在一定范围内的单元格中有信息，则将公式放入单元格中。

function formulas() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var rows = activeSheet.getMaxRows();

  for(var  i=7; i <= rows; i++){
    var workingCell = activeSheet.getRange(i, 3).getValue();

     if(workingCell != ""){
       activeSheet.getRange(i, 4).setFormula("=$B$5"); //this is fine
       activeSheet.getRange(i, 5).setFormula("=((100/H7)*I7)/100"); //but this not
  }
 }
}

如果第 8 行是 ("= ((100/H8) * I8)/100 ") 等等，我该怎么做。

编辑
问题是我尝试将它应用于许多单元格，并且我添加的行将根据我放置公式的行而增加......如果在行 D9 和 D10 中添加了公式，则范围是H9、I9和H10、I10

Answer 1

正如评论中指出的，最简单的“修复”是连接您的 i将变量循环到公式中，如下所示:

function formulas() {
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1");
  var rows = activeSheet.getLastRow(); //maxRows consider blank rows, you don't need those

  for(var i=7; i <= rows; i++){
    var workingCell = activeSheet.getRange(i, 3).getValue();

     if(workingCell != ""){
       activeSheet.getRange(i, 4).setFormula("=$B$5");
       activeSheet.getRange(i, 5).setFormula("=((100/H" +i+ ")*I" +i+ ")/100");
     }
  }
}

无论如何，此函数对电子表格执行了太多的获取和设置，并且随着工作表的增长，它的性能会很差。您应该尝试通过批量发布它们来最小化所有集合和获取，也就是说，针对更大的范围而不是逐个单元格。

您的用例使用此方法存在问题，因为您的范围内有一些空白点(当 workingCell 为空白时)。如果为这些值设置“空白”公式对您来说不是问题，那么您可以使用以下方法大大加快脚本速度:

function formulas() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet 1"); //not necessarily active
  var workingCells = sheet.getSheetValues(7, 3, -1, 1); //-1 == lastRow
  var r1c1formulas = [];
  for (var i=0; i < workingCells.length; i++){
     if (workingCells[i][0] != "") {
       r1c1formulas.push(['=R5C2', '=((100/R[0]C[3])*R[0]C[4])/100']);
     } else
       r1c1formulas.push(['=""','=""']);
  }
  sheet.getRange(7, 4, workingCells.length, 2).setFormulasR1C1(r1c1formulas);
}

第二个“技巧”是使用 R1C1 符号中的公式而不是常规的 A1 样式。检查setFormulaR1C1文档在这里。

R1C1 表示法乍一看可能令人望而生畏，但它相当简单，我想说它比 'A1' 更简单。我会在这里总结一下。 R是行号，C列，并且在字母前面有行号和列号(而不是字母)。所以=$B$5写成 =R5C2 .

最后一点不同的是相对引用。在A1符号你只是不放置 '$' 标志。当您尝试一次设置一堆公式(正是您的用例)时，这不是很直观，也不是那么灵活。因为在A1上相对公式是不同的公式，引用不同=B1与 =C1 不同(这可能是“相同的”如果设置在同一行和连续列中的两个单元格上)。

无论如何，在 R1C1 表示法中，相对引用被计算为作为引用的单元格的行数和列数。

因此，当您设置公式 =H7*I7 时到细胞 E7 , 你数一下 H在E之前3列和 I 4. 而且都在同一行，所以零行差。最后，要写一个相对引用，您可以将数字包装在 [] 中。 .因此=H7 * I7在 E7变成 =R[0]C[3] * R[0]C[4] .