google-sheetsgoogle-apps-script

Control existing check boxes depending on values in another column


In column A I have rows of checkboxes and in column I there will either be "Do Not Print" or blank. Blank cells should turn the corresponding checkbox on and "Do Not Print" should keep them off. If the row is blank, the checkbox should also be off (or removed). I want this to be a regular function, not an on Edit. I can't use a formula because I want to ability to override the checkbox if needed.

I've been trying to modify the below formula, but with no success. I'm thinking it has to loop through every row in Column I and return a true or false.

Sample sheet

function autoCheck() {
 const sheet = SpreadsheetApp.getActiveSheet();
 const sName = sheet.getName();
 const iRange = sheet.getRange('I2:I101')
// sheet.getRange('A2:A101').removeCheckboxes();

 if (sName === "sheet1") {
const len = sheet.getRange('I2:I101').getValues().filter(row => row[0] != 'Do Not 
Print').length
  const iRange = sheet.getRange(9,1,len,1);
 // sheet.getRange('A2:A101').removeCheckboxes();
 // range.insertCheckboxes().uncheck();
 Logger.log(iRange);
if (iRange.getValues()[0][0] === "") {
  sheet.getRange('A2:A101').uncheck();
  iRange.check();
}
}

}

Solution

  • Use Array.map() and Range.setValues(), like this:

    function autoCheck() {
      const sheet = SpreadsheetApp.getActiveSheet();
      if (!sheet.getName().match(/^(sheet1|Another Sheet)$/i)) throw new Error('Cannot run on this sheet');
      const lastRow = sheet.getLastRow();
      const checkboxRange = sheet.getRange('A2:A' + lastRow);
      const checkboxValues = checkboxRange.getValues().flat();
      const result = sheet.getRange('I2:I' + lastRow).getDisplayValues().flat()
        .map((v, index) =>
          v === 'Do Not Print'
            ? [false]
            : v === ''
              ? [true]
              : [checkboxValues[index]]
        );
      checkboxRange.setValues(result);
    }
    

    See Array.map(), Range.setValues() and ternary operator.