Search code examples
google-sheetsgoogle-apps-scripttriggers

onEdit() functionality when performing undo operation in sheets

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;
}
Solution

  • It's crucial to refrain from using undo and redo in Google Apps Script. Doing so can help you steer clear of potential issues and maintain the integrity of your data.

    Google Apps Script doesn't include methods for determining when a user has edited a spreadsheet by typing, pasting, or executing an "undo" or "redo" command.

    If your spreadsheet is shared or you cannot avoid undo or redo, consider adding a means to "turn off" the onEdit trigger. I.E. You might add a checkbox and use its state to control the onEdit flow, like in the checkbox is checked exit before doing any change.

    A second approach might be to keep a log of the changes made by each user. To keep it short, you can use an on-open installable trigger to clear the log entries corresponding to the user who has opened the spreadsheet and, once a day, delete all log entries.

    A third approach might be to use the Google Drive API to get the spreadsheet revisions.

    Related