I have two sheets, source (Staff) and destination (PatternToApply). In source I have dynamic list of people (it's getting updated automatically from another spreadsheet) and in destination I have static list of people.

I am trying to workout a script which automatically compare the list from both sheets and...

1. if there is a new name in source sheet copy to destination sheet and insert a row below
2. if in source sheet name is missing but it is in the destination sheet delete row from destination sheet I'm still learning and don't know what I'm doing wrong. Please, any help will be much appreciated.

Here's the test spreadsheet https://docs.google.com/spreadsheets/d/1mxmnsLeQFlorHj-N8MJX-Tue0v6zTKw4fUDhjZ1bGqs/edit?usp=sharing

Thank you!

Here's what I have so far

function UpdateAgList() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('Staff');
  var sh2 = ss.getSheetByName('PatternToApply');
  var data1 = sh1.getRange(1,1,sh1.getLastRow(),1).getValues();
  var data2 = sh2.getRange(1,1,sh1.getLastRow(),1).getValues();
  
  
  for(i = 1; i<data1.length; i  ){
    if(data1[i] == data2[i]){

      }
      else if(data1[i] != data2[i] && data2[i] == null){
        var numColumns = data1.getLastColumn();
        var rows = data1.getRow();
        var rowt = data2.getRow();
        var target = sh2.getRange(rowt, 1);
        data1.getRange(rows, 1, 1, numColumns).copyTo(target);
        sh2.insertRowAfter();
      }
      else if(data1[i] != data2[i] && data1[i] == null){
        target.clearContent();
      }
  }
}

CodePudding user response：

Modification points:

• I thought that sh1.getLastRow() of var data2 = sh2.getRange(1,1,sh1.getLastRow(),1).getValues(); should be sh2.getLastRow().
• In your script, copyTo, getRange, and clearContent are used in a loop. In this case, the process cost will become high.

When these points are reflected in your script, how about the following modification?

Modified script:

function UpdateAgList() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh1 = ss.getSheetByName('Staff');
  var sh2 = ss.getSheetByName('PatternToApply');
  var data1 = sh1.getRange(1, 1, sh1.getLastRow(), 1).getValues();
  var targetRange = sh2.getRange(1, 1, sh2.getLastRow(), 1);
  var data2 = targetRange.getValues();
  var srcAr = data1.map(([a]) => a);
  var dstAr = data2.map(([a]) => a);
  var values = [
    ...dstAr.map(a => [a.toString() == "" ? a : (!srcAr.includes(a) ? "" : a)]),
    ...srcAr.reduce((ar, a) => (!dstAr.includes(a) && ar.push([a]), ar), []),
  ];
  targetRange.clearContent();
  sh2.getRange(1, 1, values.length).setValues(values);
}

• When this script is run, your goal is achieved while the empty rows are kept at the destination sheet.

• If you want to remove the empty rows at the destination sheet, please modify the above script as follows.

  • From

      var values = [
        ...dstAr.map(a => [a.toString() == "" ? a : (!srcAr.includes(a) ? "" : a)]),
        ...srcAr.reduce((ar, a) => (!dstAr.includes(a) && ar.push([a]), ar), []),
      ];
    

  • To

      var values = [
        ...dstAr.map(a => [a.toString() == "" ? a : (!srcAr.includes(a) ? "" : a)]),
        ...srcAr.reduce((ar, a) => (!dstAr.includes(a) && ar.push([a]), ar), []),
      ].filter(String);
    

References:

• map()
• reduce()