GAS合并两脚本实现员工信息去重与状态更新

核心需求 #

本人对脚本编写了解有限，日常仅通过修改现有公开代码适配自身需求，现有2段Google Apps Script需要合并实现以下业务逻辑：

• 脚本1原有能力：读取CopyFromWB工作簿内CopyFromSheet工作表B3单元格存储的员工编号，在独立工作簿PasteToWB的PasteToSheet工作表中检索该员工编号是否存在，存在则返回对应行号。
• 新增判断逻辑要求：
  1. 若检索到目标员工编号已存在：不通过lastrow()方法在表格末尾重复新增员工记录，直接使用匹配到的行号，跳过原有新增记录操作，仅在匹配行的第10、11列写入"In Training"
  2. 若员工编号不存在：保留脚本2原有逻辑，在表格末尾新增行写入完整员工信息

原有需要跳过的新增操作代码：

destinationSS.getRange(destinationSS.getLastRow() + 1, 1, 1, 4).setValues(sourceVals);
destinationSS.getRange(destinationSS.getLastRow(), 5).setValue(new Date());

匹配到已有记录时替换执行的示例代码（示例匹配行号为3）：

destinationSS.getRange(3, 10).setValue("In Training");
destinationSS.getRange(3, 11).setValue("In Training");

现有原始代码 #

脚本1（员工编号检索逻辑） #

function searcher() {
const searchString = SpreadsheetApp.openById("CopyFromWb").getSheetByName("CopyFromSheet").getRange(3,2).getValue();
const sheetSrch = SpreadsheetApp.openById("PasteToWb").getSheetByName("PasteToSheet");
const srchCol = 1;
const srchRng = sheetSrch.getRange(2,srchCol,sheetSrch.getLastRow());
const srchData = srchRng.getValues();
Logger.log(srchData);
const result = srchData.finder(searchString);
console.log(result);
 
}
 
Array.prototype.finder = function(val){
if(val == "") return false;
const arr = [];
for(let i=0;i<this.length;i++){
if(this[i].toString().indexOf(val) > -1){
arr.push(i);
}
}
return arr[0];
}

脚本1执行日志 #

12:59:22 Execution started
12:59:23 PM Info [[Employee #], [123455.0], [180211.0], [123454.0], [180212.0], [123456.0], [123457.0], [123454.0], [123454.0], [123454.0], [123454.0], [123454.0], [123454.0], [123454.0], [123454.0], []]
12:59:23 PM Info 3
12:59:24 Execution completed

脚本2（数据写入逻辑） #

function IDName() {
ImportRange(
" CopyFromWb",
" CopyFromSheet!B3:E3",
" PasteToWb",
);
}
 
function ImportRange(sourceID, sourceRange,destinationID) {
 
const sourceSS = SpreadsheetApp.openById(sourceID);
const sourceRng = sourceSS.getRange(sourceRange);
const sourceVals = sourceRng.getValues();
const destinationSS = SpreadsheetApp.openById(destinationID).getSheetByName("PasteToSheet");
//const destRange = destinationSS.getRange(destinationSS.getLastRow() + 1, 1, 1, 4);
 
destinationSS.getRange(destinationSS.getLastRow() + 1, 1, 1, 4).setValues(sourceVals);
destinationSS.getRange(destinationSS.getLastRow(), 5).setValue(new Date());
destinationSS.getRange(destinationSS.getLastRow(), 10).setValue("In Training");
destinationSS.getRange(destinationSS.getLastRow(), 11).setValue("In Training");
}

合并后可用代码 #

已将检索逻辑直接整合到写入流程中，修复原代码存在的参数格式错误、行号计算偏差问题，直接替换原有代码即可使用：

// 自定义数组查找方法，全局可用
Array.prototype.finder = function(val){
  if(val == "") return false;
  for(let i=0;i<this.length;i++){
    // 改为精确匹配，避免员工编号部分重合导致误判
    if(this[i][0] == val){
      return i;
    }
  }
  return false;
}

function IDName() {
  // 去掉原参数前后多余空格，避免openById接口报错
  ImportRange(
    "CopyFromWb",
    "CopyFromSheet!B3:E3",
    "PasteToWb"
  );
}

function ImportRange(sourceID, sourceRange, destinationID) {
  const sourceSS = SpreadsheetApp.openById(sourceID);
  const sourceRng = sourceSS.getRange(sourceRange);
  const sourceVals = sourceRng.getValues();
  // 从源数据中提取待检索的员工编号（B3单元格对应sourceVals[0][0]）
  const searchEmpId = sourceVals[0][0];
  const destinationSS = SpreadsheetApp.openById(destinationID).getSheetByName("PasteToSheet");
  
  // 检索目标表中是否已有该员工编号
  const srchCol = 1;
  const lastRow = destinationSS.getLastRow();
  // 从第2行开始检索，跳过表头行
  const srchRng = destinationSS.getRange(2, srchCol, lastRow - 1);
  const srchData = srchRng.getValues();
  const matchIndex = srchData.finder(searchEmpId);

  if(matchIndex !== false){
    // 匹配到已有记录：数组索引从0开始，对应实际行号为索引+2（检索范围起始行为第2行）
    const matchRow = matchIndex + 2;
    destinationSS.getRange(matchRow, 10).setValue("In Training");
    destinationSS.getRange(matchRow, 11).setValue("In Training");
  } else {
    // 未匹配到记录：按原有逻辑新增行写入数据
    const newRow = destinationSS.getLastRow() + 1;
    destinationSS.getRange(newRow, 1, 1, 4).setValues(sourceVals);
    destinationSS.getRange(newRow, 5).setValue(new Date());
    destinationSS.getRange(newRow, 10).setValue("In Training");
    destinationSS.getRange(newRow, 11).setValue("In Training");
  }
}

调整说明 #

• 移除原代码中工作簿ID、范围字符串前后的多余空格，避免接口调用失败
• 优化员工编号匹配逻辑，从模糊匹配改为精确匹配，避免员工编号部分重合时出现误判
• 修正行号计算逻辑：检索范围从第2行（表头下第一行）开始，数组索引对应实际行号需要加2，不会出现行号错位
• 新增行写入时提前计算新行号，避免连续调用getLastRow()导致的行号偏差

内容的提问来源于stack exchange，提问作者Kerry Bartholomew