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!
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 }); } 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
