Copy/paste only one edited cell in a range in another sheet in the row that matches the value of a c-CodePudding

In a Spreadsheet, I have 3 source tabs in which I fill in comments, and I would like to copy the comments from these 3 tabs into one other tab which is like a comments database (which would be the target tab).

All 4 tabs have the same column format, so comment from column "L" into column "L" of the comment database tab. The comments are edited in the ranges L4:P.

And to determine the row, it would be based on the value in column "H". That is to say that the values of H4:H of the 3 source tabs are all carried over into H4:H of the comments database tab.

What would be desirable is to paste only the edited cell in the right place in the target tab replacing the content if there is already one.

In my

CodePudding user response：

Try:

function onEdit(e) {
  var ss = e.source;
  var sourceSheet = ss.getActiveSheet();
  var shName = sourceSheet.getName();
  var range = e.range;
  var row = range.getRow();
  var col = range.getColumn();

  var destSheet = ss.getSheetByName("COMMENTAIRES");
  
  if ((shName == "DATA" || shName == "EPHAD" || shName == "LIVRET" )&& (row > 4 && col >= 12 || col <= 16)){
    var dataToCopy = range.getValue();
    var email = sourceSheet.getRange(row, 8).getValue();
    var destRow = destSheet.createTextFinder(email).findNext().getRow();
    destSheet.getRange(destRow,col).setValue(dataToCopy);
  }
}

Result:

DATA Sheet

LIVRET Sheet

Explanation: Using the onEdit trigger you can make use of the (e) event to get the details of the edited cell like it's row and column. Then on the same row get the value from the column H. Then use the findNext() textFinder to find the match email on the tab "COMMENTAIRES" and paste it on the same row. This would work as long as there are no duplicate emails on the destination sheet.

findNext()