CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom Sawkins
Tom Sawkins

Reputation: 361

Auto Fill CheckBox as new row added - AppsScript / Google Sheets

I have the following code. It currently adds the checkbox to E3. I would like to modify so as a new row is added it will add a checkbox to Column E. 

function AddCheckBox(e) {
    var spreadsheet = SpreadsheetApp.getActive();
    var cell = SpreadsheetApp.getActive().getRange('E3');
    var criteria = SpreadsheetApp.DataValidationCriteria.CHECKBOX;
    var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
    cell.setDataValidation(rule);
}

I tried this, but it didn't work:

spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

Appreciate any help.

Upvotes: 1

Views: 999

Answers (1)

Tanaike
Tanaike

Reputation: 201368

  • You want to put a checkbox to the column "E" on the active range.
    • In your question, you tested spreadsheet.getActiveRange().autoFillToNeighbor(SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);. From this, I thought like above.
  • a new row is added it means that the values are put to the cells.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Pattern 1:

In this pattern, the active range is used.

Sample script:

When this script is run, a checkbox is put to the column "E" of the row of the active range.

function AddCheckBox(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Donations Received")  // Modified
  var activeRange = sheet.getActiveRange();
  var criteria = SpreadsheetApp.DataValidationCriteria.CHECKBOX;
  var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  var range = sheet.getRange(activeRange.getRow(), 5);
  range.clearContent();
  range.setDataValidation(rule);
}

Pattern 2:

In this pattern, the last row is used.

Sample script:

When this script is run, a checkbox is put to the column "E" of the last row of the active sheet.

function AddCheckBox(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Donations Received")  // Modified
  var criteria = SpreadsheetApp.DataValidationCriteria.CHECKBOX;
  var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
  var range = sheet.getRange(sheet.getLastRow(), 5);
  range.clearContent();
  range.setDataValidation(rule);
}

Pattern 3:

In this pattern, it supposes that AddCheckBox(e) is installed as the installable trigger of OnEdit event trigger.

Sample script:

When this script is run, a checkbox is put to the column "E" of the edited row.

function AddCheckBox(e) {
  var range = e.range;
  var sheet = range.getSheet();
  if (sheet.getSheetName() == "Donations Received") {  // Added
    var criteria = SpreadsheetApp.DataValidationCriteria.CHECKBOX;
    var rule = SpreadsheetApp.newDataValidation().requireCheckbox().build();
    var range = sheet.getRange(range.getRow(), 5);
    range.clearContent();
    range.setDataValidation(rule);
  }
}

Note:

  • About range.clearContent();, if you are not required, please remove it.

If I misunderstood your question and this was not the result you want, I apologize.

Upvotes: 2

Related Questions