2

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.

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 
    }
  }
}
  • first of all thanks for your reply! I will try this out asap. – Mieer Darwesh Dec 6 '16 at 12:33
  • How can i make it so eventually that it will get all the columns instead of columns 2-5 ? – Mieer Darwesh Dec 7 '16 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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