Robust approach towards inserting a formula in range of cells in apps in apps script-CodePudding

I have got two table ranges to multiply and paste result in a third table as below,

In the third table, I have populated the formulas by removing the = for reference.

I could achieve this by simply inserting for every cell in table 3 by doing sheet.getRange("K2").setFormula("C2*(1-F2)") and similarly for all the other cells.

How can I do it using loops to avoid adding above line for every cell in the range K2:N7

Thank you!

CodePudding user response：

Here is a simple example of how to copy a formula from one cell to another. The reference cells of the copied formulas are updated to reflect the cell offset.

function test() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = spread.getSheetByName("Sheet1");
    let range = sheet.getRange("C1");
    range.setFormula("=A1 B1");
    // just to make sure the formula has been written to the spreadsheet
    SpreadsheetApp.flush();
    range.copyTo(sheet.getRange("C1:C5"));
  }
  catch(err) {
    console.log(err);
  }
}