How do use app-scripts to paste down a formula?

Question

Right now I'm able to paste the formula from row X to row Y, however, the calculations in the formulas are the exact same. Say row 1 is A1+B1, the formula for row 5 should be A5+B5, but I'm still getting A1+B1.

for (var x = col; x < lastCol; x++) {
    for (var j = 2; j <= lastRow; j++) {
      var range = sheet.getRange(j,x);

      range.setFormula(formulas[0][i]);
    }
    i++;
}

Should I be using another function instead of setFormula or doing something else? I'm iterating over X amount of columns.

Kos Kos · Accepted Answer · 2017-12-20T19:39:59

You can use new method autoFillToNeighbor introduced in October, 2017.

For example if you have three columns with just one formula in cell C2:

then you can autofill rest of C column just with one line of code:

function doTheMagic() {
  SpreadsheetApp.getActiveSheet().getRange("C2").autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
}

Result:

How do use app-scripts to paste down a formula?

1 Answers