Google Sheets: OnEdit match and clear script

Question

I wish to create an "onEdit" function that clears cells (Col L to Col O) from a row, if a cell value in Col L is not matching a cell in Col A.

Col A = "ID"
Col L = "ID Match"

Col A to Col K is an imported range. Col L to Col O is a static range. This static range has an onEdit function that sorts the row based on changes to Col L. It is in sync with Col A.

I am new to Script Editor, so detailed descriptions would definitely be appreciated, if you have the time to help.

I would love to open up the document, but it has sensitive information on it and is too complex to re-write.

I don't understand what your current onEdit is doing: sorts the row based on changes to Col L. Also, what does it mean that It is in sync with Col A? If I understand you correctly, you want to clear cells (L-O) from a row if the corresponding value in column L doesn't match the corresponding value in column A? What does it have to do with sorting the rows and sync with Col A? — Iamblichus
I posted an answer. I'm not sure if that's what you wanted, so please let me know if that works for you! — Iamblichus
I will try and test this soon. I think from your comment it may be correct. You are correct that I want to clear cells (L-O). What I am keen to avoid is, for example, deleting cells (L14-O14) if there is a corresponding figure in A15. — Adam Hurrell
Cells (A-K) are sorted by column A. And cells (L-O) are sorted by Column L. If someone removes a row in the original imported range (which is in Cells A-K), then it will be removed from the sheets data set. This leaves the rest of the data out of sync. — Adam Hurrell

Iamblichus Iamblichus · Accepted Answer · 2020-02-24T12:20:58

If I understand you correctly, you want to keep track of changes in column L, so that if the edited value in column L matches any of the values in column A, the contents from columns L-O are cleared.

If that's the case, you can do the following:

function onEdit(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var sheetName = "Sheet1" // Name of the sheet to track. Please change accordingly
  var column = range.getColumn();
  var row = range.getRow();
  var editedValue = range.getValue();
  var colA = sheet.getRange(1, 1, sheet.getLastRow()).getValues().map(function(row) { 
    return row[0]; // Get array of values in column A
  });
  // Check that the edited sheet is the one you want to track.
  // Check that edited column is L
  // Check that the edited value is not found in column A
  if (sheet.getName() === sheetName && column === 12 && colA.indexOf(editedValue) === -1) {
    sheet.getRange("L" + row + ":O" + row).clearContent();
  }
}

Note:

onEdit triggers only get fired by user's changes. Changes made by the script, or by data imported in any other way, don't fire the edit. If you want to keep track of this, I'd suggest you to make a time-based trigger instead.

Google Sheets: OnEdit match and clear script

1 Answers

Note:

Reference: