Expand script for multiple columns-CodePudding

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("Sheet2"); //change this to the name of your sheetui = SpreadsheetApp.getUi();

var names = ss.getRange("C1:C");

var namesValues = names.getValues(); //Get array of all the names

var checkboxes = ss.getRange("B1:B");

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("C1:C") I select only one column it works. But when I want to set it for more columns (eg C1:E) it doesn't work.

Hope you can help me. THANKS!!!!

EDIT:

https://docs.google.com/spreadsheets/d/1MjIuZbON_nlaENqyARt_UnQZZ-ooZOMLjKmDM-nZl4M/edit#gid=1464332245

this is the file where I'm trying. Sheet 2. you can see from the apps script what changes if instead of var names = ss.getRange ("C1:C") I write (C1:E). Try it by yourself (see the differences of the column of checkboxes). THANK YOU!!!!

EDIT 2:

This is the input (before running the script)

This is what I want (cell with 1 to have the checkboxes and cell with 0 not to have it)

CodePudding user response：

From your provided sample input and output situations, I believe your goal is as follows.

You want to put the checkboxes to column "D" when the columns "A" and "B" are 1.

In this case, how about the following sample script? From your showing script, in your situation, you might want to run the script by the script editor. So in this modification, the event object is not used.

Sample script:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var values = sheet.getRange("A1:B"   sheet.getLastRow()).getValues();
  var rangeList = values.map((r, i) => r.every(e => e == 1) ? `D${i   1}` : "").filter(String);
  sheet.getRangeList(rangeList).insertCheckboxes();
}

Note:

This sample script is for your provided sample Spreadsheet. So when you changed the Spreadsheet, the script might not be able to be used. Please be careful this.

References:

Added:

When I had asked whether my understanding of In your goal, when the values of the columns "A" and "B" are 1, you want to put the checkbox to the column "D". Is my understanding correct? is correct, you said Exactly!!!. But, from your following reply,

This was very useful for my example but as you also said, it does't work on my original file. I changed some values in the sheet to make it more similar to what I actually have

I want to add checkboxes on B where C, D and E have 1

If you want to put the checkboxes when the columns "C", "D" and "E" are 1, the sample script is as follows.

Sample script:

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var lastRow = sheet.getLastRow();
  var values = sheet.getRange("C1:E"   lastRow).getValues();
  sheet.getRange("B1:B"   lastRow).removeCheckboxes();
  var rangeList = values.map((r, i) => r.every(e => e == 1) ? `B${i   1}` : "").filter(String);
  sheet.getRangeList(rangeList).insertCheckboxes();
}