How to restrict onEdit to a specific range and modify a corresponding range(increase by one if true)-CodePudding

I want to have a cell which counts how many times the checkbox has been set to true. I tried to do it myselfs and wrote two different solutions but neither of them works as I want them to.

The first one:

function onEdit(e){
  // count warnings
  var warning1 = sheet1.getRange(6,10,lastRow,1).getValues();
  var warning1count = sheet1.getRange(6,21,lastRow,1).getValues();

  for(var i = 0; i < lastRow; i  ){
    if(warning1[i][0] == true){
      sheet1.getRange(6 i,21,1,1).setValue(warning1count[i][0]  1);
   }
}
}

This one increases the value by one to the cells that are TRUE whenever something changes in the whole sheet, I don't know how to specify it to make it add one whenever it is set TRUE and ignore all other edits I can't use a reference to a speciffic cell here, because I have every row looking the same and I want to count how many times true has been set in any of them separately.

The second one:

/**
 * Function to count warnings
 * 
 * @param {cell} cell Original value
 * @param {number} number New value
 * @return How many times warning was given
 * @customfunction
 */
function warning(cell, number){
var number = 0
  if(cell == true){
        number;
}
return number
}

It works just in a specific cell, but it gives back 0 or 1, so it basically tells me if the cell is set to true, I want the value to stay when it's false and increase by one whenever it is set to true.

EDIT: the other post doesn't have an answer for my question. It has only a reference to a specific cell - which I can't use in my situation. I have a range of cells (J6:J) which I want to interract with another range of cells (U6:U) but I want to change the value in U6 only if J6 is set true, the same with U7-J7 etc. I can't make it work, because whenever I use range it changes in the whole range whenever I edit any cell, so for example - if I set J6 to true it gives me 1 in U6, but when I set J7 true it adds one to U6 and U7, because both of corresponding cells (J6 and J7) are set true. I don't know how to make U6 react ONLY to J6.

I added a photo to make it easier to explain, I basically want the cell on the right to count how many times the cell on the left has been set to true.

enter image description here

CodePudding user response：

As written in the duplicate, restrict your onEdit:

/**
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
function onEdit(e) {
  const requiredRange = {
      //J6:J
      startRow: 6,
      endRow: Infinity,
      startColumn: 10,
      endColumn: 10,
    },
    requiredSheets = ['Sheet1'],
    editedRange = e.range,
    editedSheetName = editedRange.getSheet().getName(),
    isEditedRangeInRequiredSheet = () =>
      requiredSheets.some((sheetName) => editedSheetName === sheetName),
    [editedRow, editedCol] = [editedRange.rowStart, editedRange.columnStart],
    isEditedRangeInRequiredRange = () =>
      editedRow >= requiredRange.startRow &&
      editedRow <= requiredRange.endRow &&
      editedCol >= requiredRange.startColumn &&
      editedCol <= requiredRange.endColumn,
    columnOffset = 12;
  
  //exit code
  if (
    !isEditedRangeInRequiredSheet() &&
    !isEditedRangeInRequiredRange() &&
    !editedRange.isChecked()
  )
    return;
  
  //else
  const warningCountRange = editedRange.offset(0, columnOffset);
  warningCountRange.setValue(warningCountRange.getValue()   1);
}