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

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.

CodePudding user response：

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!

CodePudding user response：

In your situation, how about the following modification?

Modified script:

var src = boqPipeworkSheet.getRange(7, 1, 1, 8);
var dst = boqPipeworkSheet.getRange(7, 8, boqPipeworkSheet.getLastRow(), 8);
src.copyTo(dst, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);

Note:

If you want to copy the same formulas of boqPipeworkSheet.getRange(7, 1, 1, 8) with the same range, you can use the following modification.

  var src = boqPipeworkSheet.getRange(7, 1, 1, 8);
  var dst = boqPipeworkSheet.getRange(7, 8, boqPipeworkSheet.getLastRow(), 8);
  dst.setFormulas(Array(dst.getNumRows()).fill(src.getFormulas()[0]));

Reference:

copyTo(destination, copyPasteType, transposed)

Added:

When I saw your script, from let rngFormulas = boqPipeworkSheet.getRange(7, 8, boqPipeworkSheet.getLastRow(), 8), I had thought that you wanted to copy the formula of A7:G7 to the H8:O. But if you want to copy the formula of A7:G7 to the A8:G, you can use the following modification. As a sample script, I would like to add this because I thought that this method might be useful for other users.

var src = boqPipeworkSheet.getRange(7, 1, 1, 8);
var dst = boqPipeworkSheet.getRange(8, 1, boqPipeworkSheet.getLastRow() - 7, 8);
src.copyTo(dst, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);