Reference formulas from one sheet into multiple sheets

Question

I would like to reference (pull in) formulas (not the result of the formula) from one sheet into multiple sheets so that they calculate on the sheet they are referenced.

For example, if I have =SUM(Q2:X2) in a formulas sheet, I would like to pull that into 10 other sheets (budget A sheet, budget B sheet, etc.) so that it would sum Q2:X2 for each budget sheet it is referenced into, not the formulas sheet.

I have many budget sheets with the same formulas in the same locations. However, they can change from quarter to quarter. I would like to update the formulas in one place, rather than in each sheet. I think this might be possible with getFormula, but I'm very new to scripting and not sure how I would use that.

Answer 1

You want to copy a formula from one sheet to many other sheets in the same spreadsheet.

You are right that a script including getFormula (for which there is relatively little discussion) and setFormula (for which, as @Rubén says, there are many precedents) is on the right track. The following code provides a working example of how you might proceed.

• Cell B3 of "template" contains a formula =sum(C3:G3). This formula does not exist on any other sheet (or a different formula may exist in that cell).
• the formula is obtained using getFormula doc
• the names of the other sheets are declared in the variable budgetsheets
• a loop is created work through each of the sheets
• in each sheet
  • the sheet name is defined
  • the range is set (getRange) doc(A1Notation) and doc(row,column)
  • the formula is copied (setFormula) doc

The code could be more efficient (such as selecting the cell to be copied, making the range a variable, selecting the sheet names programmatically) but it is adequate as a working example.

---

function wa13533201() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcename = "template";
  var source = ss.getSheetByName(sourcename);
  var sourcerange = source.getRange("B3");
  var sourceformula = sourcerange.getFormula();

  var budgetsheets = [
    "Sheet2",
    "Sheet3",
    "Sheet4"
  ];

  for (var i=0;i<budgetsheets.length;i++){

    //Logger.log("DEBUG: sheet = "+budgetsheets[i]);
    var targetname = budgetsheets[i];
    var target = ss.getSheetByName(targetname);
    var targetrange = target.getRange("B3");
    targetrange.setFormula(sourceformula);
  }
}