I am trying to set the value of multiple cells when the value of once cell changes in google sheets-CodePudding

I am trying to set the values of several cells in the same row of a cell that I am changing.

IF the value in AJ2 is True then the range AK2:AX2 should also be true.If I change AJ2 to false, then I should be able to uncheck any of the values in the range and they would then be false. I 'sort of' have this logic working. IF I have AJ2 checked and uncheck AK2, when I uncheck AJ2 it rechecks AK2.

I also need this to happen on all rows if I change the value in the column of AJ for their rows. not just row 2. I am fairly new to working with sheets and could use some help.

function onEdit(e) {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var cell = ss.getRange("AJ2"); 
var range =ss.getRange("AK2:AX2")
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 36&&ss.getActiveSheet().getName()=="MasterData") {

if (Cell = "TRUE")
 range.setValue("TRUE")}

}

CodePudding user response：

The onEdit(e) simple trigger returns an event object.

The event object contains the information about the spreadsheet that was edited, which you use instead of the "SpreadsheetApp.getActiveSpreadsheet()"

To access the information in the event object, you need to use the 'e':

function onEdit(e) {

  //the sheet that was edited
  var sheet = e.source.getActiveSheet();

  //the cell that was edited
  var cell = sheet.getActiveCell();

  //the row number of the cell that was edited
  var row = cell.getRow();

  //the column number of the cell that was edited
  var column = cell.getColumn();

From there, proceed as you normally would with sheets and ranges.

To update multiple cells at once, you can set the range with getRange(row, column, numRows, numColumns) and then setValues(values). Note that it's setValues(), not setValue().

CodePudding user response：

Try this

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const cell = e.range;
  if(sheet.getName() == "MasterData" && cell.getColumn()==36 && cell.isChecked()){
    sheet.getRange(cell.getRow(),37,1,14).setValue('TRUE')
  }
  if(sheet.getName() == "MasterData" && cell.getColumn()>36 && cell.getColumn()<=50 && !cell.isChecked()){
    sheet.getRange(cell.getRow(),36,1,1).setValue('FALSE')
  }
}