How do I add formulas to Google Sheets using Google Apps Script?
Here's a more general answer.
Suppose you want to populate column B with a formula that is the value of column A, plus 1. For example,the formula in cell B1 would be "=A1 + 1".
Let's stipulate that first row of the range is 1, and the last is 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);
Making this work for different numbers of rows, and different starting row, is a matter of using variables instead of the hard-coded 1 and 20 in this example.
Set, Copy & Paste in a range
If you want for example A18 to hold the formula '=SUM(A1:A17)' and the same for columns B to Z you can set the formula in A18 and then copy A18 to B18:Z18. The same thing holds for more complicated ranges. The easiest to get going is to use the macro recorder and then inspect your script.
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
This is done using the setFormula for a selected cell. Below is an example of how to do this.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange("B5");
cell.setFormula("=SUM(B3:B4)");
You can also use setFormulaR1C1 to create R1C1 notation formulas. Example below.
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])");
To add several formulas to several fields use setFormulas. Example below
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);