I need some help building a simple script that should do the following:

Source spreadsheet

Check if Col C on sheet source equals "Done"

1. If yes, copy and paste the rows from Col A and Col B that meet the criteria to the first empty row on sheet history a timestamp of the copy paste

2. Plus copy and paste the same values from Col A and B on spreadsheet Destination, first empty row on tab destination a timestamp of the copy paste

3. Finally delete the copied rows from the first sheet source so there are no empty rows

4. If not, leave the values be

Overall the goal is to CUT the values that meet the criteria and paste them on a different sheet and on a different spreadsheet at the same time.

CodePudding user response：

Here you go:

function main() {

  // get 'Done' rows from sheet 'source' and remove these rows
  var data = get_done();

  // put the rows on the sheet 'history' (same spreadsheet)
  copy_to_history(data);

  // pyt the rows on the sheet 'destination' (another spreadsheet)
  copy_to_destination(data);
}

function get_done() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('source');
  var data = sh.getDataRange().getValues();

  var keep = data.filter(x => x[2] != 'Done');
  sh.clearContents().getRange(1,1,keep.length,keep[0].length).setValues(keep);

  var timestamp = new Date();
  var done = data.filter(x => x[2] == 'Done').map(x => [timestamp, x[0], x[1]]);
  return done;
}

function copy_to_history(data) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('history');
  data.forEach(row => sh.appendRow(row));
}

function copy_to_destination(data) {
  var ss = SpreadsheetApp.openById('1niihiUXZii1gNnifn2ZLNf4JnvLs887mJMp53OcIphA');
  var sh = ss.getSheetByName('destination');
  data.forEach(row => sh.appendRow(row));
}

The main shortcoming of the script: it doesn't keep formulas and formatting.