CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Digital Farmer
Digital Farmer

Reputation: 2107

Fill Down Formula (Set a Fromula & Copy Down AutoFill) Google Sheets

I have this spreadsheet:

enter image description here

I am creating a script to add formulas on lines that are not empty, I got this result:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  ss.getRange('Odds Completas!O2').setFormula('=C2+J2');

  var lr = ss.getLastRow();
  var fillDownRange = ss.getRange(2, 15, lr-1);
  ss.getRange('Odds Completas!O2').copyTo(fillDownRange);
}

The formula is defined in O2, but the others below are not. Instead, the following message appears:

Number of rows in range must be at least 1. (row 6)

Upvotes: 1

Views: 1904

Answers (1)

Tanaike
Tanaike

Reputation: 201358

How about this answer? Please think of this as just one of several answers.

Modification points:

  • In your script, ss is declared by SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(). On the other hand, the range uses the a1Notation including the sheet name. I think that the reason of your issue is this.
    • At ss.getRange('Odds Completas!O2').setFormula('=C2+J2');, the formula is put to the cell "O2" of the sheet Odds Completas.
    • At var lr = ss.getLastRow();, the last row of the active sheet is retrieved.
    • In this case, for example, if the active sheet is a new sheet, lr returns 0. By this, the error occurs at ss.getRange('Odds Completas!O2').copyTo(fillDownRange);.
      • In your script, lr-1 is used. So even when the last row is 1, an error occurs. Please be careful this.

Modified script:

Pattern 1:

In this pattern, your script is not modified. And before you run the script, please open the sheet of Odds Completas and run the script. By this, the script is run as the active sheet of Odds Completas, and lr returns the last row of Odds Completas.

Pattern 2:

In this pattern, your script is modified.

function myFunction() {
  var sheetName = "Odds Completas";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  sheet.getRange('O2').setFormula('=Q2+J2');

  var lr = sheet.getLastRow();
  var fillDownRange = sheet.getRange(2, 15, lr-1);
  sheet.getRange('O2').copyTo(fillDownRange);
}

References:

Upvotes: 2

Related Questions