Home > OS >  Google Sheets Script Modification - Insert checkboxes starting at row 4
Google Sheets Script Modification - Insert checkboxes starting at row 4

Time:10-02

I'm using the checkbox script below which was kindly provided by another stackoverflow user.

However I'd like the script to start inserting the checkboxes and clearing from row 4 and down i.e. do not add checkboxes in rows 1-3 or clear in rows 1-3 if cell in col5 on rows 1-3 is edited.

Could someone please amend code to achieve this. Many thanks in advance.

function onEdit(e) {
  const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
  const watchColumn = 5;
  const checkboxColumns = [1, 2, 4];
  if (e.range.columnStart === watchColumn && e.source.getActiveSheet().getName().match(watchSheets)) {
    checkboxColumns.forEach(checkboxColumn => {
      const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, checkboxColumn);
      if (e.value) {
        checkboxCell.insertCheckboxes();
      } else {
        checkboxCell.clearDataValidations();
        checkboxCell.clearContent();
      }
    });
  }
}

Working Code is:

function onEdit(e) {
  const watchSheets = /^(Sheet1|Sheet2|Sheet3)$/i;
  const watchColumn = 5;
  const checkboxColumns = [1, 2, 4];
  if (e.range.columnStart === watchColumn && e.source.getActiveSheet().getName().match(watchSheets) && e.range.rowStart >= 4) {
    checkboxColumns.forEach(checkboxColumn => {
      const checkboxCell = e.source.getActiveSheet().getRange(e.range.rowStart, checkboxColumn);
      if (e.value) {
        checkboxCell.insertCheckboxes();
      } else {
        checkboxCell.clearDataValidations();
        checkboxCell.clearContent();
      }
    });
  }
}

CodePudding user response:

In your script, how about the following modification?

From:

if (e.value) {
  checkboxCell.insertCheckboxes();

To:

if (e.value && e.range.rowStart >= 4) {
  checkboxCell.insertCheckboxes();
  • In this modification, when the edited row is more than row 4 including row 4, the script of checkboxCell.insertCheckboxes() is run.
  • Related