e.oldValue is not cooperating with e.Value-CodePudding

I'm working on a counter system that has both an items per week column and an all time number of items column. In an attempt to gain efficiency, I've rigged together a system that allows the user to input how many they'd like to add into the weekly column, where upon the code: adds it to the weekly and the all time, determines if the amount being added causes the weekly amount to exceed the maximum per week(12), and if so only adds up to the limit(e.g. weekly counter is at 8 and user tries to add 5, system overrides and only adds 4 to both columns). Below is said code:

function onEdit(e) {
  //stores the row and column value of cell edited
  var row = e.range.getRow();
  var col = e.range.getColumn();
  //stores the name of sheets used, pri is input data is output
  var priSheet1 = "Sheet1"
  var dataSheet1 = "Sheet1"
  //stores the columns of the cells we're interested in, m is input d is output
  var mcol1 = 6
  var mcol2 = 7
  var dcol1 = 7
  var dcol2 = 18
  var dcol3 = 20
  var dCell = e.source.getSheetByName(dataSheet1).getRange(row, mcol2).getValue();
  //determines if cell edited is in both the sheet and column we're interested in
  if(col == mcol1 && e.source.getActiveSheet().getName() === priSheet1 && e.value != 0){
    e.source.getSheetByName(dataSheet1).getRange(row, dcol3).setValue(e.oldValue);
    var sum = e.source.getSheetByName(dataSheet1).getRange(row, dcol3).getValue()   e.value
    if(sum <= 12){
      e.source.getSheetByName(dataSheet1).getRange(row, dcol1).setValue(e.value   dCell);
      e.source.getSheetByName(dataSheet1).getRange(row, mcol1).setValue(sum);
      e.source.getSheetByName(dataSheet1).getRange(row, dcol2).setValue(new Date());
    }else if(sum > 12){
      var overflow = 12 - e.oldValue
      e.source.getSheetByName(dataSheet1).getRange(row, dcol1).setValue(overflow   dCell);
      e.source.getSheetByName(dataSheet1).getRange(row, mcol1).setValue(12);
      if(e.oldValue == 12){
      }else if(e.oldValue != 12){
        e.source.getSheetByName(dataSheet1).getRange(row, dcol2).setValue(new Date());
      }
    }
  }else if(col == mcol1 && e.source.getActiveSheet().getName() === priSheet1 && e.value == 0){
    e.source.getSheetByName(dataSheet1).getRange(row, mcol1).setValue(0);
  }
}

However, running this creates weird values when adding e.oldValue and e.Value: [value logs] (values are e.oldValue, untouched all time value, e.value, sum of e.value and e.oldValue, and final all time value after calculation). As you add more numbers, sum starts to get weird as well(adding 2 when 1 is already there results in 12 and 210 respectively). This is happening through multiple methods of coding and I'm at a loss.

CodePudding user response：

e.oldValue and e.value might return "unexpected" values, i.e., in Google Sheets a blank cell is "converted" to 0 when using the cell value in an arithmetic operation like a formula making an addition operation of cell A1 and A1 :=A1 A2.

If a cell is blank and then it's edited, e.oldValue will return undefined, not 0. If a cell has a value, then it's cleared, e.value will return undefined.

In JavaScript binary operator has two "modes", as arithmetical addition operator and as a string concatenation operator. In order to prevent "weird" results, you might want to verify the data types or coerce the values to the desired data type. I.E: you might use Number(value) to convert "4" to 4. You could use isNaN(value) to check if value is a number.

Additionally,

Google Sheets has limitations regarding the numbers that it can handle.
Under certain circumstances, onEdit might not be triggered.