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

by

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

More Related Contents:

  1. How to define global variable in Google Apps Script
  2. How do I communicate a page(modal dialog) with its sibling (sidebar)?
  3. Is it possible to ‘prefill’ a google form using data from a google spreadsheet?
  4. How to protect the Apps Script code in a Google spreadsheet?
  5. Is there a way to evaluate a formula that is stored in a cell?
  6. Automatic timestamp when a cell is filled out
  7. Apps Script: how to get hyperlink from a cell where there is no formula
  8. How to auto select range from a filter without having to manually enter it?
  9. Script to Change Row Color when a cell changes text
  10. Sheet.getRange(1,1,1,12) what does the numbers in bracket specify?
  11. How to get onFormSubmit to trigger automatically?
  12. Copying a spreadsheet copies all linked files as well
  13. How to merge the two cells of a table in a Google text Document without this weird result?
  14. Upload an image to s document
  15. “SpreadsheetApp.getUi() cannot be called from this context”
  16. copy data from one sheet to another in google app script and append a row, one small issue
  17. Using built-in spreadsheet functions in a script
  18. Google App Scripts cannot be given Authorization or Permission
  19. How to get the correct range to set the value to a cell?
  20. Refresh data retrieved by a custom function in Google Sheet
  21. How to extract the link from a cell now that links are not reflected as HYPERLINK?
  22. Get All Links in a Document
  23. Dynamically creating spreadsheet menu items
  24. Increment Cell value by one by clicking
  25. email notification if cell is changed
  26. Dynamic selection options in Google Apps Script for forms
  27. Determine current user in Apps Script
  28. How to ignore empty cell values for getRange().getValues()
  29. Google spreadsheet “Cannot call method “getRange” of null”
  30. Publish an add-on privately

Leave a Comment