0

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]1 (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.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Waffles
  • 3
  • 1
  • 1
    Hi. Would you please supply some sample data, as well as an example of a successful outcome of user input. – Tedinoz Oct 17 '22 at 01:07
  • I believe I provided sample data in the image linked, but if that's not what you're looking for let me know. As for an example, I'd be happy to do that. If we start out with 0 weekly and 10 all time, entering 2 in weekly should set it to 2 and set all time to 12. If we then attempted to enter 11 into weekly, it would produce 12 and all time would be set to 22(adding 11 exceeded the maximum of 12 per week, and since we already had 2 for this week it added the remaining 10 to all time and maxed out weekly). Hope this helps! – Waffles Oct 17 '22 at 01:37

1 Answers1

0

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.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166