I am developing a basic functionality in my spreadsheet where if data is entered in two cells, a category name and amount, they will populate another part of the spreadsheet and then be cleared from where they were originally entered. This is making use of the event object in appscript that enables one to perform an action if a cell is edited. The problem I am having is there are unfortunate side effects once I have logged the event and performed the action.

If I hit ctrl+z to undo what I did and the two cells where I originally entered the data are populated, the event is re-triggered, forcing my spreadsheet to enter a loop where I can’t undo the data that was logged. Is there a way around this effect? I tried using the oldValue field of the event object but it’s not working the way I want. My initial approach was to try and only perform the action when the cells were previously undefined. It turns out that when I undo, the cells were also previously undefined and the loop begins. Is there a way around this?

function onEdit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //Events
  let range = e.range;
  let oldVal = e.oldValue;

  //Expense Category added or deleted
  addCategory(sheet, range, oldVal);
}


function addCategory(sheet, range, oldVal) {
  let row = range.getRow();
  let col = range.getColumn();
  let addRange            = "I7:I9";
  let categoryRange       = "F12:F";
  var category2Add        = "I7";
  let amount2Add          = "I8";
  let startRow            = 12;
  let categoryColumn      = 6;
  
  //There is add category request but no amount entered
  if (row == 7 && col == 9 && sheet.getRange(amount2Add).isBlank()) {
    //SpreadsheetApp.getActive().toast("I'm here");
    return;
  }
  //There is add category request but no category entered
  if (row == 8 && col == 9 && sheet.getRange(category2Add).isBlank()) {
    //SpreadsheetApp.getActive().toast("yaya");
    return; 
  }
  if (row == 7 && col == 9 && !sheet.getRange(amount2Add).isBlank() && typeof oldVal === 'undefined') {
    SpreadsheetApp.getActive().toast("test 1 is: " + oldVal);
    doWork(sheet, startRow, categoryColumn, sheet.getRange(amount2Add).getValue(), sheet.getRange(category2Add).getValue(), categoryRange, addRange);
    return;
  }
  if (row == 8 && col == 9 && !sheet.getRange(category2Add).isBlank() && typeof oldVal === 'undefined') {
    SpreadsheetApp.getActive().toast("test 2 is:  " + oldVal);
    doWork(sheet, startRow, categoryColumn, sheet.getRange(amount2Add).getValue(), sheet.getRange(category2Add).getValue(), categoryRange, addRange);
    return;
  }
  return;
}

function doWork(sheet, startRow, categoryColumn, amount, category, categoryRange, transferRange) {
  //Entry is a negative number
  if (amount < 0) {
    Browser.msgBox("Initial amount must be greater than zero!");
    sheet.getRange(transferRange).clearContent();
    return;
  }
  //Entry is not a number
  if (isNaN(amount)) {
    Browser.msgBox("Initial amount must be a number!");
    sheet.getRange(transferRange).clearContent();
    return;
  }
  let expenseCategoryList = sheet.getRange(categoryRange).getValues().filter(r => r!= "").flat();
  let row2Add             = expenseCategoryList.length;
  let categoryCell        = sheet.getRange(startRow + row2Add, categoryColumn);
  let categoryAmountCell  = sheet.getRange(startRow + row2Add, categoryColumn + 2);
  categoryCell.setValue(category);
  categoryAmountCell.setValue(amount);
  sheet.getRange(transferRange).clearContent();
  return;
}