google-apps-script - 比较列中的代码后查找两个工作表之间的差异行

Question

我试图找到 NEW添加到 Data 的行表比较其Codes位于一列中，带有 Codes位于 Database 的列中床单。我通过找到两个代码之间的差异来做到这一点。

...
// Get Mappings array
let Maplist = shtMap.getRange(2, 1, r_Map - 1, 2).getValues();

// find new codes that have come in the Data sheet
let DataCodes = [...new Set(shtData.getRange(2, 1, r_Data - 1, 1).getValues().flat())];
let DatabaseCodes = [...new Set(shtDatabase.getRange(2, 2, r_Database - 1, 1).getValues().flat())];

let diff =[];
// ===> Below is the line I want to modify to incorporate all the logic given in my Notes section.
diff = DataCodes.reduce( (diff,x) => !DatabaseCodes.includes(x) ? [...diff, [x]] : diff, []);

// Map Emp name to diff Array ===> not working....getting error!
diff = diff.map(function(x, i , arr){
    if(arr.indexOf(x) == i){
        return [...diff, [arr[i][1]]);
    }
}, (Maplist));

// Add Data Validation to `Status` column
var Rng = shtDatabase.getRange(r_Database + 1, 7, diff.length -1, 1);
Rng.clearDataValidations().clearContent();
var rule = SpreadsheetApp.newDataValidation().requireValueInList([`Open`,`Complete`], true).build();
Rng.setDataValidation(rule); // Update `Status` value
...

上面的代码给了我 Difference i.e. new Codes已进入 Database床单。有没有办法修改上面的 Reduce function code拉入 entire new rows ，使 diff Array然后可以附加到 Database 的末尾床单？
注:

这里Delivery Date在 Database Sheet指的是FinalDate在 Data Sheet .是否可以修改上面的代码以考虑不匹配的列名，使FinalDate列值正好放在 Delivery Date 中柱子？

我还需要添加一个 DataValidation dropdown包含值 Open,Complete进Status Column的 Database Sheet , 对于 diff array 中的每个新行.上面的代码如何考虑数据验证代码？

如您所见，matching columns在 Database Sheet不连续且与 Data Sheet 的顺序相同，但遍布整个工作表。是否可以修改代码以将每个 diff 数组值正确放置到它们各自的列中？

最后，我必须比较每个Owner名称在 Database Sheet与 Mapping Sheet数组(包含 Owner 名称及其对应的 Emp 名称)，然后匹配其对应的 Emp命名并将其添加到 Emp栏目Database Sheet因此。

原因:

我想在内存中做这一切，最后把这个内存数组转移到Database Sheet一气呵成。否则，重复访问(读/写)表格会花费大量时间来运行代码，尤其是当数据很大时。这可以通过一些 consice 代码实现吗？

数据表:

数据库表:

映射表

这是 [示例文件][4] 的链接。
[4]:
编辑:
https://docs.google.com/spreadsheets/d/1DARGtbN8EyEKyF9ceuOusStvuPIXjUR_8OrqfhPxpNQ/edit?usp=sharing
@TheMaster，这是写在文件中的完整代码:

function myTest(){

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shtData = ss.getSheetByName("Data");
  const shtMap = ss.getSheetByName("Mapping");
  const shtDatabase = ss.getSheetByName("Database");

    // get Data Sheet unique list of Owners
  let r_Data = FindLastRow(shtData);
  let c_Data = FindLastColumn(shtData);
  let list = shtData.getRange(2, 4, r_Data - 1, 1).getValues();
  let Datalist = [...new Set(list.flat())];

  // get Mapping Sheet unique list of Owners
  r_Map = FindLastRow(shtMap);
  c_Map = FindLastColumn(shtMap);
  list = shtMap.getRange(2, 1, r_Map - 1, 1).getValues();
  let Maplist = [...new Set(list.flat())];
  
  // find new codes that have come in the Data sheet
  r_Database = FindLastRow(shtDatabase);
  c_Database = FindLastColumn(shtDatabase);
  let DataCodes = [...new Set(shtData.getRange(2, 1, r_Data - 1, 1).getValues().flat())];
  let DatabaseCodes = [...new Set(shtDatabase.getRange(2, 2, r_Database - 1, 1).getValues().flat())];
  
  // find the difference between the 2 arrays and append the new ones to Mapping Sheet, then sort.
  let diff = Datalist.reduce( (diff,x) => !Maplist.includes(x) ? [...diff, [x,'Unassigned']] : diff,[]);
  if(diff.length !== 0){
    shtMap.getRange(r_Map + 1, 1, diff.length, diff[0].length).setValues(diff);
  }

  // Sort the Mappings Sheet on 2nd column, then 1st column
  shtMap.getDataRange().offset(1, 0, shtMap.getDataRange().getNumRows() - 1).sort([{column: 2, ascending: true}, {column: 1, ascending: true}]);
  

//  diff =[];
  // ===> Below is the line I want to modify to incorporate all the logic given in my Notes section.
  diff = DataCodes.reduce( (diff,x) => !DatabaseCodes.includes(x) ? [...diff, [x]] : diff, []);


  // From Notes : Map Emp name to diff Array ===> not working....getting error!
  diff = diff.map( function(x, i, arr){
    if(arr.indexOf(x) == i){
      return [...diff, arr[1][i]];
    }
  }, (Maplist));
  
  // From Notes : Add Data Validation to `Status` column
  var Rng = shtDatabase.getRange(r_Database + 1, 7, diff.length -1, 1);
  Rng.clearDataValidations().clearContent();
  var rule = SpreadsheetApp.newDataValidation().requireValueInList([`Open`,`Complete`], true).build();
  Rng.setDataValidation(rule); // Update `Status` value
}


function FindLastRow(sht) {
  return sht.getRange(1,1).getDataRegion().getLastRow();
};

function FindLastColumn(sht) {
  return sht.getRange(1,1).getDataRegion().getLastColumn();
};

编辑:
@Tanaike，我更新了帖子中的屏幕截图并更新了电子表格。例如 Data 中的黄色行表是 new rows所以需要放在 Database正确工作表，并在 Status 中添加数据验证专栏， Emp姓名在 Emp列(在将 Owner 与映射表匹配后)。 FinalDate栏目 Data表是 Delivery Date栏目 Database床单。
这个想法是创建一个 consice 代码来创建一个单一的二维数组(它应该包括数据验证和 Emp 名称)，将所有新数据正确放置在 Database 中。床单。我想避免每次都将数据写入工作表以更新 Database 中的每一列床单。

Answer 1

将以下示例脚本添加到 myTest() 的底部如何？ ?
修改后的脚本:
从:

  var Rng = shtDatabase.getRange(r_Database + 1, 7, diff.length -1, 1);
  Rng.clearDataValidations().clearContent();
  var rule = SpreadsheetApp.newDataValidation().requireValueInList([`Open`,`Complete`], true).build();
  Rng.setDataValidation(rule); // Update `Status` value
}

到:

  var Rng = shtDatabase.getRange(r_Database + 1, 7, diff.length -1, 1);
  Rng.clearDataValidations().clearContent();
  var rule = SpreadsheetApp.newDataValidation().requireValueInList([`Open`,`Complete`], true).build();
  Rng.setDataValidation(rule); // Update `Status` value

  // I added below script.
  const mapObj = shtMap.getRange("A2:B" + shtMap.getLastRow()).getValues().reduce((o, [a, b]) => Object.assign(o, {[a]: b}), {});
  const existingIdsObj = shtDatabase.getRange("B2:B" + shtData.getLastRow()).getValues().reduce((o, [b]) => Object.assign(o, {[b]: true}), {});
  const putValues = shtData.getRange("A2:G" + shtData.getLastRow()).getValues().reduce((ar, [a,b,c,d,e,f,g]) => {
    if (!existingIdsObj.hasOwnProperty(a)) ar.push([b, a, f, c, , , , d, e, , , g, , mapObj[d]]);
    return ar;
  }, []);
  shtDatabase.getRange(shtDatabase.getLastRow() + 1, 1, putValues.length, putValues[0].length).setValues(putValues);
}

为了检查重复的ID并放置Emp值，我使用了 mapObj 的 JSON 对象和 existingIdsObj .