Skip to content

How to get formulas from a row and spread them in a range using script?

How can I get this one to populate the range defined in the code?

I understand that once I get it, I have to iterate over the range to set the formulas, but I just can’t get how to do it:

  let formulas = boqPipeworkSheet.getRange(7, 1, 1, 8).getFormulasR1C1();
  let rngFormulas = boqPipeworkSheet.getRange(7, 8, boqPipeworkSheet.getLastRow(), 8)
  rngFormulas.forEach(c => rngFormulas(c).setFormulas(formulas))

Of course, it gives me an error as there’s a lot to learn on forEach and a lot more.

Appreciate your time.

Answer

Got rather lost in your coordinates. Please put the ones you need.

notation: (row from num 1, column from num 1)
What this does is it copies formulas from cell (7, 1) to (7, 8).
Then it puts them on each row from row 8 till the last row. First row is (8, 1) to (8, 8).

  const formulas = boqPipeworkSheet.getRange(7, 1, 1, 8).getFormulasR1C1();
  const lastRow = boqPipeworkSheet.getLastRow();
  for (let i = 8; i <= lastRow; i++) {
    const rngFormulas = boqPipeworkSheet.getRange(i, 1, 1, 8)
    rngFormulas.setFormulasR1C1(formulas);
  }

Ask if you need more clarification!