时间戳

首页 > 解决方案 > 在比较列中的代码后查找两个工作表之间的差异行

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

问题描述

我试图通过比较位于一列中的行与位于工作表的列中的NEW行来查找添加到工作表的行。我通过找到两个代码之间的差异来做到这一点。DataCodesCodesDatabase

...
// 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表的末尾？

笔记：

原因：

数据表： 数据库表：映射表数据表 数据库表 映射表

这是 [示例文件][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匹配后）。工作表中的列是工作表中的列。OwnerFinalDateDataDelivery DateDatabase

这个想法是创建一个简洁的代码，该代码将创建一个 2D 数组（应包括 datavalidation 和 Emp 名称），以将所有新数据正确放置在工作Database表中。我想避免每次都将数据写入工作表以更新工作表中的每一列Database。

标签： google-apps-scriptgoogle-sheetsrowreducedifference

解决方案

如何将以下示例脚本添加到您的底部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 和 put值，我使用了和Emp的 JSON 对象。mapObjexistingIdsObj

推荐阅读