Set row of values based on checkbox in Google Sheets-CodePudding

I have a sheet where each row has a checkbox in the C column. I want to write my script such that if the checkbox in column C is unchecked, then columns G-K for that row will all be set to “N/A”.

I’ve seen things on here like getRange(“G2:K2”), but the row number is dynamic and I’m not sure how to do that.

I have it in an onEdit function and have the event row and column stored in variables.

Any help is appreciated. Thanks!

CodePudding user response：

Description

Using the onEdit event object e you can get the range of the edited cell from that using an offset set the values of columns G to K

Script

function onEdit(e) {
  if( e.range.getSheet().getName() === "Sheet1" ) { // Make sure we are on the right sheet
    if( e.range.getColumn() === 3 ) {  // Make sure the edit occured in column C
      if( e.value === "FALSE" ) {  // Unchecked
        e.range.offset(0,4,1,5).setValues([["N/A","N/A","N/A","N/A","N/A"]]);
      }
    }
  }
}

Reference

CodePudding user response：

Could try a forEach loop. What is does is it reads each line of row col C to determine if its check and set n/a in the columns. So here in this case, it skips the row that is checked.

https://spreadsheet.dev/foreach-loop-in-apps-script

function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var data=ss.getDataRange().getValues();

data.forEach(function(row,col){
if (col == '') return;
if (row[2] == true) return; //If colC is TRUE skip

data.getRange(col   1, 7,1,5).setValue("n/a"); set colG - K  as n/a if colC false
});
}


  [1]: https://spreadsheet.dev/foreach-loop-in-apps-script