如何使用Google Apps Script API for Google Spreadsheet将诸如= SUM(A1：A17)的公式添加到一系列字段？
解决方法
这是使用
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);
