javascript - Is there a way to determine the type of edit used with onEdit in a Google Sheets Script?

Question

I need to know if a user is deleting a row, editing a cell, or pasting a bunch of cells. I keep looking through documentation and I cannot find a way to determine what the user's action is. Is there a way in to know what kind of action is used in onEdit?

标签： javascriptgoogle-apps-scriptgoogle-sheetsgoogle-workspace

Answer 1

You can use the installable change event listener for Sheets.

https://developers.google.com/apps-script/guides/triggers/events#change

It is possible to determine if a row was deleted by testing for the REMOVE_ROW change type.

It is also possible to determine whether a single cell or multiple cells were edited. See code below.

Pasting will not insert rows or columns, so if the change type "EDIT" happened, then you can assume that rows or columns were not inserted. I don't know of any way to manually edit multiple cells at the same time, so if the active range includes multiple cells, then it was probably a "paste."

function nameOfFunction(e) {
  var A1Notation,typeOfChange;

  //Install this function as a trigger for Sheets change

  typeOfChange = e.changeType;//Get the type of change that was made
  Logger.log('typeOfChange: ' + typeOfChange)
  Logger.log('typeof typeOfChange: ' + typeof typeOfChange)

  switch(typeOfChange) {
    case 'REMOVE_ROW':
      Logger.log('A row was deleted')
      break;
    case 'EDIT':
      A1Notation = SpreadsheetApp.getActiveRange().getA1Notation();
      Logger.log('A1Notation: ' + A1Notation)
      if (A1Notation.indexOf(":") === -1) {//There is NOT a colon in the A1 notation
        Logger.log('An Edit was made to a single cell')
      } else {
        Logger.log('An Edit was made to MULTIPLE cells')
      }
      break;
    case 'OTHER':
      /*  This is NOT an edit of types
        EDIT, INSERT_ROW, INSERT_COLUMN, REMOVE_ROW, REMOVE_COLUMN, INSERT_GRID, REMOVE_GRID, FORMAT
      */
      Logger.log('This is NOT an edit or a row Deletion')
      break;
    default:

  }

}