How do I add formulas to Google Sheets using Google Apps Script?

Question

How do I add a formula like:

=SUM(A1:A17) 

to a range of fields using the Google Apps Script API for Google Sheets?

Answer 1

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