Apps Script无报错但无法更新Inv.表格日期问题求助

问题描述 #

有一段Apps Script，原本通过时间触发器每周运行一次，功能是从WipShirtAlt、WipEAalt、fromWip三个工作表获取日期数据，更新Inv.工作表的Next Appt列（B列）和Due Date列（C列）。此前运行正常，本周突然失效：脚本执行无报错，但Inv.表格对应列毫无变化。未修改过任何表格的列顺序及脚本代码，且该表格关联了AppSheet应用。移除时间触发器后手动运行脚本，执行日志无报错，但表格仍无更新。

原脚本代码如下：

function apptDates() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var wipShirtAltSheet = spreadsheet.getSheetByName("WipShirtAlt");
  var wipEAaltSheet = spreadsheet.getSheetByName("WipEAalt");
  var fromWipSheet = spreadsheet.getSheetByName("fromWip");
  
  var invSheet = spreadsheet.getSheetByName("Inv.");
  
  // Get the data range of the "WipShirtAlt" sheet
  var wipShirtAltDataRange = wipShirtAltSheet.getDataRange();
  var wipShirtAltData = wipShirtAltDataRange.getValues();
  
  // // Get the data range of the "WipEAalt" sheet
  var wipEAaltDataRange = wipEAaltSheet.getDataRange();
  var wipEAaltData = wipEAaltDataRange.getValues();
  
  // Get the data range of the "fromWip" sheet
  var fromWipDataRange = fromWipSheet.getDataRange();
  var fromWipData = fromWipDataRange.getValues();
  
  
  // Get the data range of the "Inv." sheet
  var invDataRange = invSheet.getDataRange();
  var invData = invDataRange.getValues();
  
  // // Iterate over the rows of "WipShirtAlt" sheet
  for (var i = 1; i < wipShirtAltData.length; i++) {
    var clientName = wipShirtAltData[i][1]; // names/client is the 2nd column (index 1)
    var date = wipShirtAltData[i][5]; //  column where appt dates stored is the sixth column (index 5)
    var due = wipShirtAltData[i][4]; // column where the due date is stored if the 5th column (index 4)
    
  //   // Search for a matching name in "Inv." sheet
    for (var j = 1; j < invData.length; j++) {
      if (invData[j][0] === clientName) { // names/client is the 2nd column (index 1)
        invSheet.getRange(j + 1, 2).setValue(date); //  column B is the second column (index 2) where appt column is
        invSheet.getRange(j + 1, 3).setValue(due); //column C is the third column (index 3) where wip due date column is
        break;
      }
    }
  }
  
  // // Iterate over the rows of "WipEAalt" sheet
  for (var k = 1; k < wipEAaltData.length; k++) {
    var clientName = wipEAaltData[k][1]; //  names/client is the 2nd column (index 1)
    var date = wipEAaltData[k][4]; //  column e is the fifth column (index 4)
    var due = wipEAaltData[k][5]; //column f is sixth column (index 5)
    
  //   // Search for a matching name in "Inv." sheet
    for (var l = 1; l < invData.length; l++) {
      if (invData[l][0] === clientName) { //  names/client is the first column (index 1)
        invSheet.getRange(l + 1, 2).setValue(date); // column B is the second column (index 2)
        invSheet.getRange(l + 1, 3).setValue(due);  //column C is the third column (index 3) where wip due date column is
        break;
      }
    }
  }
  
  // Iterate over the rows of "fromWip" sheet
  for (var m = 1; m < fromWipData.length; m++) {
    var clientName = fromWipData[m][1]; //  names/client is the 2nd column (index 1)
    var date = fromWipData[m][5]; //  column f is the 6 column (index 5) next appt
    var due = fromWipData[m][4]; //column e is the 5th column (index 4) due date
    
    // Search for a matching name in "Inv." sheet
    for (var n = 1; n < invData.length; n++) {
      if (invData[n][0] === clientName) { //  names/client is the first column (index 0)
        invSheet.getRange(n + 1, 2).setValue(date); //  column B is the second column (index 2)
        invSheet.getRange(n + 1, 3).setValue(due);  //column C is the third column (index 3) where wip due date column is
        break;
      }
    }
  }
}

核心排查点 #

• AppSheet同步冲突：关联AppSheet后，表格可能处于后台同步锁定状态，脚本写入操作被静默拦截或覆盖
• 字符串匹配失效：客户名存在空格、大小写差异、全角/半角符号，或单元格为公式结果，导致精确匹配失败
• 权限隐性变更：表格共享权限调整，导致脚本写入权限不足但未抛出错误

修复与优化步骤 #

1. 解决AppSheet同步冲突 #

在脚本开头添加延迟避开同步高峰，末尾强制刷新表格：

// 延迟30秒避开AppSheet同步周期
Utilities.sleep(30000);
// 脚本末尾添加强制刷新
SpreadsheetApp.flush();

2. 修复字符串匹配逻辑 #

将精确匹配改为忽略格式的模糊匹配：

// 替换原匹配条件
if (invData[j][0].toString().trim().toLowerCase() === clientName.toString().trim().toLowerCase())

3. 添加调试日志 #

在关键位置添加日志，确认脚本是否找到匹配项、数据是否正确：

// 处理客户数据时添加
console.log(`当前处理客户：${clientName}，日期：${date}，截止日期：${due}`);
// 匹配成功时添加
console.log(`找到匹配客户：${clientName}，已更新行：${j+1}`);

4. 优化脚本性能（批量写入） #

原脚本频繁调用setValue()易触发API限制，改为批量写入：

function apptDates() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var wipShirtAltSheet = spreadsheet.getSheetByName("WipShirtAlt");
  var wipEAaltSheet = spreadsheet.getSheetByName("WipEAalt");
  var fromWipSheet = spreadsheet.getSheetByName("fromWip");
  var invSheet = spreadsheet.getSheetByName("Inv.");
  
  // 延迟避开AppSheet同步
  Utilities.sleep(30000);
  
  // 获取所有工作表数据
  var wipShirtAltData = wipShirtAltSheet.getDataRange().getValues();
  var wipEAaltData = wipEAaltSheet.getDataRange().getValues();
  var fromWipData = fromWipSheet.getDataRange().getValues();
  var invData = invSheet.getDataRange().getValues();
  
  // 构建客户名-日期映射表，减少循环次数
  var clientDateMap = {};
  
  // 处理WipShirtAlt数据
  for (var i = 1; i < wipShirtAltData.length; i++) {
    var clientName = wipShirtAltData[i][1].toString().trim().toLowerCase();
    var date = wipShirtAltData[i][5];
    var due = wipShirtAltData[i][4];
    if (clientName) clientDateMap[clientName] = {date, due};
  }
  
  // 处理WipEAalt数据
  for (var k = 1; k < wipEAaltData.length; k++) {
    var clientName = wipEAaltData[k][1].toString().trim().toLowerCase();
    var date = wipEAaltData[k][4];
    var due = wipEAaltData[k][5];
    if (clientName) clientDateMap[clientName] = {date, due};
  }
  
  // 处理fromWip数据
  for (var m = 1; m < fromWipData.length; m++) {
    var clientName = fromWipData[m][1].toString().trim().toLowerCase();
    var date = fromWipData[m][5];
    var due = fromWipData[m][4];
    if (clientName) clientDateMap[clientName] = {date, due};
  }
  
  // 批量更新Inv.表格数据
  for (var j = 1; j < invData.length; j++) {
    var invClient = invData[j][0].toString().trim().toLowerCase();
    if (clientDateMap[invClient]) {
      invData[j][1] = clientDateMap[invClient].date; // B列对应索引1
      invData[j][2] = clientDateMap[invClient].due; // C列对应索引2
      console.log(`更新客户：${invClient}，行号：${j+1}`);
    }
  }
  
  // 一次性写入所有变更
  invSheet.getRange(1, 1, invData.length, invData[0].length).setValues(invData);
  SpreadsheetApp.flush();
}

5. 验证AppSheet设置 #

• 检查AppSheet自动化规则，确认无反向覆盖Inv.表格B、C列的操作
• 查看AppSheet同步历史，确认是否存在脚本写入后被同步覆盖的记录

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