I'm new with these kind of things so sorry for the mistakes. I'm trying to use this script to remove checkboxes from a column everytime that the value in other columns is 0. The script is the following:
function onEdit() {var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Zodiac issues"); //change this to the name of your sheetui = SpreadsheetApp.getUi();
var names = ss.getRange("L3:L");
var namesValues = names.getValues(); //Get array of all the names
var checkboxes = ss.getRange("J3:J");
var cbRows = checkboxes.getHeight(); //Get # of rows in the rangesvar cbValues = checkboxes.getValues(); //Get array of all the checkbox column cell values//Logger.log(cbValues);
var newCBValues = new Array(cbRows); //Create an array to store all the new checkboxes values before we edit the actual spreadsheet
for (var row = 0; row < cbRows; row++) {
newCBValues[row] = new Array(0); // Make the array 2 dimensional (even though it only has 1 column, it must be 2D).
if (namesValues[row] == "0" || namesValues[row] == " ") { //If the name cell of this row is empty or blank then...
newCBValues[row][0] = " "; //Set the value to one space (which will make the cell NOT true or false, and thus NOT display a checkbox).
//Logger.log("newCBValues[" + row + "][0]: " + newCBValues[row][0]);
}else{ //otherwise, if the name cell isn't blank...
if (cbValues[row][0] === true) {
newCBValues[row][0] = true; //Keep the checkbox checked if it's already checked
}else{ //If the name cell isn't blank, and it's not true...
newCBValues[row][0] = false; //Then Keep it or set it to False (an empty checkbox):
}
}
}
checkboxes.setDataValidation(SpreadsheetApp.newDataValidation().requireCheckbox()).setValues(newCBValues);
}
If in var names = ss.getRange("L3:L") I select only one column it works. But when I want to set it for more columns (eg L3:N) it doesn't work.
Hope you can help me. THANKS!!!!
EDIT:
this is what I obtain if I write var names = ss.getRange("B1:B")
But if I want to include also column A ( so var names = ss.getRange("A1:B") ) the result is this one
Aucun commentaire:
Enregistrer un commentaire