jeudi 24 mars 2022

Expand script for multiple columns

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

CODE FOR ONE COLUMN

CODE FOR MORE THAN ONE COLUMN




Aucun commentaire:

Enregistrer un commentaire