0
  • I have two spreadsheets, SS1 and SS2.
  • I need to update SS2 when a row is inserted into or removed from SS1, or when a certain range of cells is changed in SS1.
  • I use an installed onChange() trigger to call a function to determine when a row is inserted or removed from SS1 as follows:
function changeHappened(e)
{
  if (e.changeType == "INSERT_ROW" || e.changeType == "REMOVE_ROW")
  {
    // do an autoresize in SS2
  }
}
  • This works perfectly.
  • I also need to do the autoresize in SS2 when a cell is changed within a certain range in SS1. I can't use onChange() for that because row and column information isn't available via onChange(), so I use an installed onEdit() trigger to call a function to determine if the change happened in the range I'm interested in and if so, to do the autoresize in SS2.
  • The problem is, inserting and removing rows not only fires onChange(), it also fires onEdit(). I need a way to determine in the function called by onEdit() if what happened was a row being added or removed because I don't want to update SS2 twice. I have tried many things, but haven't found a reliable test.

QUESTION: Can someone suggest a reliable test in an onEdit() called function to tell if the trigger happened due to inserting or removing a row?

I have tried checking the sheet, row, and column available in the onEdit() function, but they seem to come back with random values when a row is inserted or deleted. When I delete rows, sometimes the current sheet is even reported as another sheet in the spreadsheet file.

1 Answer 1

2

row and column information isn't available via onChange()

You can determine the active row and column in an installable "on change" trigger like this:

  const ss = SpreadsheetApp.getActive();
  const activeRange = ss.getActiveRange();
  const rowIndex = activeRange.getRow();
  const columnIndex = activeRange.getColumn();

reliable test in an onEdit() called function to tell if the trigger happened due to inserting or removing a row?

e.value and e.oldValue will both be undefined, but I think that may happen in some other contexts as well, such as multi-cell range cut and paste.

It would probably make sense to include all the necessary logic in changeHappened(e).

1
  • 1
    Thanks @doubleunary for this answer. It solved my problem perfectly. My understanding of the data available in onChange() was obviously mistaken (based on someone else's post I read). I have eliminated the onEdit() trigger, which simplifies my script significantly. Dec 6, 2023 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.