onEdit() 'row is not defined' error using checkboxes

Question

This script tries to add or remove checkboxes from column A if columns B-D are populated or not. If every cell in those three columns of that row is populated then a checkbox is inserted in column A. If even one cell in those three columns of that row is not populated, then no checkbox is inserted.

column A = checkboxes

column B = title

column C = tstart

column D = tstop

function refreshCheckboxes(){
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet_name');
  var rows = sheet.getDataRange().getValues();
  var headers = rows.shift();
  var checkbox = row[0]
  var title = row[1];
  var tstart = row[2];
  var tstop = row[3];
  for (var i = 0; i < rows.length; i++) {  
    if (rows[[title][tstart][tstop]] !== "") {  
      checkbox.insertCheckboxes();
    } else {
      checkbox.clearDataValidations();
      checkbox.clearContent();
    }
  }
}

CURRENT OUTCOME:

ReferenceError: row is not defined is occurring when the row variables are being set. I'm not sure why I'm getting that error because I have similar functions that define the row variables in this way. My other worry with this script is about how columns B-D are set up. Is an array appropriate here and and is there an better way to set this up?

DESIRED OUTCOME:

To identify what is causing the row definition error and correct it. To verify if that AND() functionality works.

I already have an onEdit() that works perfectly other than it applying to only one row at a time thus I wanted to make a custom to run as needed.

Thanks!

Answer 1

Use and installable trigger and edit sheet name. I assumed one header row

function onMyEdit(e) {
  //e.source.toast('Entry');
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet Name" && e.range.columnStart > 1 && e.range.columnStart < 5 && e.range.rowStart > 1) {
    //e.source.toast("Gate1")
    const rg = sh.getRange(e.range.rowStart,1)
    if(sh.getRange(e.range.rowStart,2,1,3).getValues().flat().every(e => e)) {
      let v = rg.getDataValidation();
      if(v && v.getCriteriaType() == SpreadsheetApp.DataValidationCriteria.CHECKBOX) {
        //do nothing
      } else {
        //e.source.toast('Checkbox')
        rg.insertCheckboxes();
      }
    } else {
      //e.source.toast("clear");
      rg.clearDataValidations();
      rg.clearContent();
    }
  }
}

Demo:

Answer 2

I believe your goal is as follows.

• You want to insert the checkboxes when all columns "B" to "D" have the values.
• You want to remove the checkboxes when one of the columns "B" to "D" doesn't have the values.
• You want to know the reason for the issue of ReferenceError: row is not defined.

Modification points:

• In your showing script, row is not defined. This is the reason for the error. This has already been mentioned in a comment. Even when this is resolved, I think that unfortunately, rows[[title][tstart][tstop]] cannot be used.

From this situation, how about modifying your script as follows?

Modified script:

Please set your sheet name.

function refreshCheckboxes() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet_name');

  // --- I modified the below script.
  // Retrieve values from "B2:D".
  var rows = sheet.getRange("B2:D" + (sheet.getLastRow() || 1)).getDisplayValues();
  
  // Create 2 range lists for inserting and removing the checkboxes.
  var { insert, remove } = rows.reduce((o, r, i) => {
    if (r.includes("")) {
      o.remove.push(`A${i + 2}`);
    } else {
      o.insert.push(`A${i + 2}`);
    }
    return o;
  }, { insert: [], remove: [] });

  // Insert checkboxes.
  if (insert.length > 0) {
    sheet.getRangeList(insert).insertCheckboxes();
  }

  // Remove checkboxes.
  if (remove.length > 0) {
    sheet.getRangeList(remove).removeCheckboxes();
  }
}

In this script, when all columns "B" to "D" have the values, the checkboxes are inserted into column "A". When one of the columns "B" to "D" doesn't have the values, the checkboxes of column "A" are removed.

References:

• getRangeList(a1Notations)
• insertCheckboxes()
• removeCheckboxes()