I have this sheet: https://puu.sh/ISskB/7329c743ce.png

Basically all I want is to copy and paste all rows which contain column F2:F1000>20% into another sheet. I am planning to schedule this script to run one time per day, so it should not override any data pasted in previous days.

Thanks

CodePudding user response：

It can be something like this:

function set_trigger() {
  ScriptApp.newTrigger("main").timeBased().everyDays(1).create();
}

function main() {
  copy_all_rows_to_dest_sheet();
  remove_redundant_rows();
}

function copy_all_rows_to_dest_sheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var src_sheet = ss.getSheetByName('Sheet1');
  var src_range = src_sheet.getDataRange();
  var dest_sheet = ss.getSheetByName('Sheet2');
  var dest_range = dest_sheet.getRange(dest_sheet.getLastRow() 1,1);
  src_range.copyTo(dest_range);
}

function remove_redundant_rows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet2');
  var data = sheet.getDataRange().getValues();

  // get indexes of redundant rows
  var rows = data.map(x => x.slice(0,-1).join());
  var indexes = [];
  var i = 0;
  while (rows.length) {
    var row = rows.shift();
    // var percent = data[i].pop();
    var percent = data[i][5];   // <---------------------- update
    if (percent < .2 || rows.includes(row)) indexes.push(i);
    i  ;
  }

  // remove the rows by the indexes
  while (indexes.length) sheet.deleteRow(indexes.pop() 1);
}

If you run the function set_trigger() it will run the function main() every day.

The function main() copies all the rows from the sheet 'Sheet1' at the end of the sheet 'Sheet2' and removes all the duplicated rows and all the rows that have value in the last column less than 20%.

Update

Here is the variant of the main function that doesn't keep a formatting (it takes all formatting from the destination sheet):

function main_no_formatting() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var src_sheet = ss.getSheetByName('Sheet1');
  var src_range = src_sheet.getDataRange();
  var [header, ...src_data] = src_range.getValues();
  
  var dest_sheet = ss.getSheetByName('Sheet2');
  var dest_range = dest_sheet.getDataRange();
  var [_, ...dest_data] = dest_range.getValues();

  var data = [...src_data, ...dest_data];
  var rows = data.map(x => x.join());
  var filtered_data = [];

  for (var i=0; i<data.length; i  ) {
    var row = rows.shift();
    var percent = data[i][5];
    if (percent < .2) continue;
    if (rows.includes(row)) continue;
    filtered_data.push(data[i])
  }

  var table = [header, ...filtered_data];
  
  dest_sheet.clear();
  dest_sheet.getRange(1,1,table.length,table[0].length).setValues(table);
}

Be careful with % signs. If they disappear from the destination sheet the code wont work.

My sheet is here.