CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptgoogle-sheets-formula
itsPav
itsPav

Reputation: 612

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

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.

Upvotes: 2

Views: 7017

Answers (1)

user555121
user555121

Reputation:

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

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

enter image description here

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:

enter image description here

Upvotes: 6

Related Questions