logo标签列表

如何使用Google Apps Script在Google Sheets中添加公式？

google-apps-scriptgoogle-sheetsgoogle-sheets-formula
67

如何添加类似这样的公式：

=SUM(A1:A17)

如何使用Google Apps Script为Google Sheets应用程序将公式应用于一系列字段？

你是想在一系列单元格中寻找相同的公式“=SUM(A1:A17)”，还是想根据工作表中的位置更改求和范围？ - Ruben Portz
5个回答
104

使用setFormula函数来为所选单元格设置公式。以下是如何完成此操作的示例。

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");
cell.setFormula("=SUM(B3:B4)");

您也可以使用setFormulaR1C1方法来创建R1C1符号表示法的公式。以下是示例：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");
// This sets the formula to be the sum of the 3 rows above B5
cell.setFormulaR1C1("=SUM(R[-3]C[0]:R[-1]C[0])");

要将多个公式添加到多个单元格中，请使用setFormulas。以下是一个示例：

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

// This sets the formulas to be a row of sums, followed by a row of averages right below.
// The size of the two-dimensional array must match the size of the range.
var formulas = [
  ["=SUM(B2:B4)", "=SUM(C2:C4)", "=SUM(D2:D4)"],
  ["=AVERAGE(B2:B4)", "=AVERAGE(C2:C4)", "=AVERAGE(D2:D4)"]
];

var cell = sheet.getRange("B5:D6");
cell.setFormulas(formulas);
这是一个非常糟糕的答案...不知道为什么会被接受和赞同。请参考文档以了解如何获取范围，setFormula是一个范围方法（2012年也是如此 :-)）。 - Serge insas
22
@Sergeinsas请发表您的方法。 - Mingwei Samuel
我认为setFormula函数是可以用的，但同意答案应该更好地描述如何使用它，而不是专注于如何选择单元格。 - Pablo Jomer
8

以下是更一般性的答案。

假设您想使用一个公式填充列B，该公式是列A的值加1。例如，单元格B1中的公式将为“=A1+1”。

让我们规定范围的第一行为1，最后一行为20。

// create an array the same size as the number of rows.
var data = [];
// populate the array with the formulas.
for (var i=0; i < 20; i++)
{
  // note that as usual, each element of the array must itself be an array 
  // that has as many elements as columns. (1, in this case.)
    data[i] = ['=A' + (i+1).toString() + ' + 1 ' ];
}
// set the column values.
sheet.getRange(1,2,20,1).setFormulas(data);

这个例子中，要让这个方法适用于不同数量的行和不同的起始行，需要使用变量而不是硬编码的数字1和20。
5

设置、复制和粘贴范围

如果您想让例如A18保持公式“=SUM(A1:A17)”，并且对于B到Z列也是如此，您可以在A18中设置公式，然后将A18复制到B18：Z18。对于更复杂的范围，同样适用。最简单的方法是使用宏记录器，然后检查您的脚本。

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
sheet.getRange('A18').setFormula('=SUM(A1:A17)');
sheet.getRange('A18').copyTo(sheet.getRange('B18:Z18'));
//There are other parameters to 'copyTo' that you can use if needed
三个参数的 copyTo 不是必需的，因为默认值已经是 PASTE_NORMAL 和 false（不转置）。使用单参数简短版本没有问题，对吧？ - Martin Burch
是的，马丁，你说得对。我现在让示例代码更加简洁，并注释说明了如果需要，可以使用'copyTo'函数的其他参数。 - Frans Van Assche
4
我使用了Frans Van Assche建议的copyTo公式，但使用了从数字行和列到A1表示法的转换，因为我没有预定义的范围。例如，以下代码会将D列到之前在总计列C中创建的数据中包括多少列（不包括标题）的每一行相加。
  const startRow = 2; // first row to sum
  const startCol = 4; // first column to sum
  let endCol = sheet.getLastColumn(); // last column with data
  let endRow = sheet.getLastRow(); // last row with data
  let sumRange = sheet.getRange(startRow, startCol, 1, endCol - startCol + 1); // range for first cell of formula
  let copyRange = sheet.getRange(startRow, startCol - 1, endRow - 1, 1); // range to copy formulas into
  let sumFormula = "=SUM(" + sumRange.getA1Notation() + ")"; // define formula
  let totalsCol = startCol - 1; // this could be whichever column you want the totals to be in, for example, a constant or endCol + 1 if you want the totals at the end
  let startCell = sheet.getRange(startRow, totalsCol); // first cell to copy formula into
  startCell.setFormula(sumFormula); // add formula to first cell
  startCell.copyTo(copyRange); // copy formula from first cell to other range
3

setValues()也可以代替setFormulas()使用。

  var disht = spss.getSheetByName("DI");
  var divals = disht.getRange("A1:G50").getValues();
  divals[40][5]= "=round(SUM(F10:F39),0)"; //F41 =round(SUM(F10:F39),0)
  divals[42][5]= "=(F42+F41)*(F8/100)";  //F43 =(F42+F41)*(F8/100)
  divals[43][5]= '=if(G8>0,(F41+F42)*(G8/100),"")' ; //F44 =if(G8>0,(F41+F42)*(G8/100),"")
  divals[44][5]= '=if(isblank($G$7),"",sum($F$41:$F$44)*$G$7*0.01)';//F45 =if(isblank($G$7),"",sum($F$41:$F$44)*$G$7*0.01)
  divals[45][5]= '=round(F41,0)+round(F42,0)+round(F43,0)+round(F44,0)+round(F45,0)' ; //F46 =round(F41,0)+round(F42,0)+round(F43,0)+round(F44,0)+round(F45,0)
  divals[47][5]= "=F46-F47"; //F48 =F46-F47
  divals[44][4]= '=if(isblank($G$7),"","TCS")'; //E45  =if(isblank($G$7),"","TCS")
  disht.getRange("A1:G50").setValues(divals);
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接