Make range change value based on checkbox - Google Sheets-CodePudding

I'm looking to have a checkbox on A3, that when checked makes types "0" on every cell of the range A6:A350. If possible, that unticks itself at the end ubt leaving the values on the range. Now it would be easy using a if condition on that range, the problem is that I want to be able to manually change values if needed. Can a script do this?

I've looked for this, but can only seem to find the opposite, and without coding background it isnt easy to reverse it.

Thanks in advance

CodePudding user response：

That can only be done with a script. You can fill a range with zeros like this:

function fillWithZeros() {
  SpreadsheetApp.getActiveSheet().getRange('A6:A350').setValue(0);
}

To run that easily, assign fillWithZeros to a button.

If you really need to run it when a checkbox is ticked, use the checkboxButtons_ script.

CodePudding user response：

try this code:

function onEdit(e) {
 var row;
 var col;
 var sheetName;
 var sheet;

 if(e.value == "TRUE"){
  if((row = e.range.getRow()) == 3 && (col = e.range.getColumn()) == 1){
   if((sheetName = e.range.getSheet().getSheetName()) == "TargetSheet"){
     sheet = e.range.getSheet();
     sheet.getRange(6,col,345,1).setValue(0);
   }
  }
 }
}

TargetSheet should be replaced by the name of the sheet that you will be using, this is done so that this function does not change the values of other sheets.

Edit: here is the same code rewritten to use the event object instead of API calls.

function onEdit(e) {
  let sheet;
  if (e.value !== 'TRUE'
    || e.range.rowStart !== 3
    || e.range.columnStart !== 1
    || (sheet = e.range.getSheet()).getName() !== 'TargetSheet') {
    return;
  }
  sheet.getRange(6, e.range.columnStart, 345, 1).setValue(0);
  e.range.setValue(false);
}