I am trying to delete a row from another sheet on checkmark change in onEdit(e).

I have a timeStamp in column 1, info in column 2 and a checkbox in column 3. Two sheets Original and copy. I am trying to createTextFinder on the timeStamp in the Copy sheet and delete that row.

function onEdit(e) {
 const src = e.source.getActiveSheet();
 const r = e.range;

 if (src.getName() != "Original" || r.columnStart != 3 || r.rowStart == 1) return;

 const originalText = src.getRange(r.rowStart,1).getValue();
 const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Copy");
 const copyText = dest.createTextFinder(originalText);
 const cell = copyText.findNext();
 const row = cell.getRow();
 dest.deleteRow(row);
}

CodePudding user response：

Modification points:

• Although I'm not sure about your actual Spreadsheet, from your showing script, if the values of column "A" is the date object, getValue() returns the date object. And, when you are using the number format for the column "A", dest.createTextFinder(originalText) is always null. By this, I think that an error occurs at cell.getRow(). I thought that this might be the reason of your current issue.

From const copyText = dest.createTextFinder(originalText), if the display values (number format) of column "A" of "Original" and "Copy" sheets are the same, how about the following modification?

Modified script:

In this modification, it supposes that the number format of column "A" of "Original" and "Copy" sheets is the same.

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;

  if (src.getName() != "Original" || r.columnStart != 3 || r.rowStart == 1 || !r.isChecked()) return;
  const originalText = r.offset(0, -2).getDisplayValue();
  const dest = e.source.getSheetByName("Copy");
  const find = dest.createTextFinder(originalText).findNext();
  if (!find) return;
  dest.deleteRow(find.getRow());
}

• In this modified script, when a checkbox of column "C" is checked, the display value of column "A" of the same row is retrieved. And, using the retrieved value, the value is searched from "Copy" sheet. When the value is found, the row is deleted.

• From your showing script, this script searches the date from a sheet. For example, if the date is put to the column "A" of "Copy" sheet, you can also modify const find = dest.createTextFinder(originalText).findNext(); to const find = dest.getRange("A2:A" dest.getLastRow()).createTextFinder(originalText).findNext();.

• In this modifiction, when the checkbox is unchecked, the script is not run by !r.isChecked(). If you want to run the script when the checkbox is both checked and unchecked, please remove || !r.isChecked().

Note:

• If the number format of column "A" of "Original" sheet is different from "Copy" sheet, please test the following modification.

  function onEdit(e) {
    const src = e.source.getActiveSheet();
    const r = e.range;
  
    if (src.getName() != "Original" || r.columnStart != 3 || r.rowStart == 1 || !r.isChecked()) return;
    const originalText = r.offset(0, -2).getValue().getTime();
    const dest = e.source.getSheetByName("Copy");
    const dstValues = dest.getRange("A2:A"   dest.getLastRow()).getValues();
    const find = dstValues.findIndex(([a]) => a.getTime() == originalText);
    if (find == -1) return;
    dest.deleteRow(find   2);
  }
  

References:

• getDisplayValue()
• createTextFinder(findText)