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.
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();
}
}
}
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.