How to copy specific columns to another sheet if value of cell changes in master sheet?

Question

I want to create a logbook that keeps all historic data from my master sheet per row.

So what I am working on is:

If specific cell in mastersheet changes, copy the row corresponding with the changed cell to my logbook sheet. And in the process never delete existing rows in the logbook but only adding new rows to it.

function CopyDataToLogbook() {
  var sss = SpreadsheetApp.openById('...'); //source spreadsheet
  var ss = sss.getSheetByName('Recherche'); //source sheet
  //Get full range of data
  var SRange = ss.getDataRange();
  //get A1 notation identifying the range
  var A1Range = SRange.getA1Notation();
  //get the data values in range
  var SData = SRange.getValues();

  var tss = SpreadsheetApp.openById('...'); // target spreadsheet
  var ts = tss.getSheetByName('Logbook'); //target sheet
  //set the target range to the values of the source data
  ts.getRange(A1Range).setValues(SData);
} 

This is what I have currently, which copies the exact data from Master to Destination. But this does not pay attention to 'only 3 or 4 specific columns to be copied not all columns.' and it does not pay attention to keeping existing rows in destination sheet. so it needs to look for the next empty row and add data from there on.

I am pretty new to this google app scripting. Any help would be nice.

Answer 1

You should install a trigger to fire on edits and use its event object (denoted e above), for example as follows.

function CopyDataToLogbook(e) {
  var sss = e.source;            // edited spreadsheet
  var sheet = e.range.getSheet();    // edited sheet
  if (sheet.getName() == 'Recherche') {  // check where the edit is
    if (e.range.getColumn() >= 2 && e.range.getColumn() <= 5) { // check that the edit was to certain columns 
      var row = e.range.getRow();   // row where edit happened; 
      var values = sheet.getRange(row, 2, 1, 4).getValues(); // columns 2-5 in that row
      var tss = SpreadsheetApp.openById('...'); // target spreadsheet
      var ts = tss.getSheetByName('Logbook');   //  log sheet
      ts.appendRow(values[0]);                  // append row with our values 
    }
  }
}