Google Apps Script中SetValue覆盖行而非写入新行的问题

问题描述 #

定时运行的脚本按时间和关键词条件将数据从master工作表复制到其他工作表，但Logger工作表的记录会重复写入同一行（比如先写Main Page的记录，再写Andhra Pradesh的记录时会覆盖同一行），没有追加到工作表最后一行。

问题根源 #

全局变量startRow在脚本加载时就计算好了entry.getLastRow()+1，后续调用mainfilter()、andhrafilter()、assamfilter()这三个函数时，都使用这个固定的行号值，没有在每次写入Logger后更新行号，导致所有记录都写到了同一行。

修复方案 #

推荐采用收集日志后一次性写入的方式，既解决行号冲突问题，又减少Spreadsheet API调用次数，提升运行效率：

修改后的完整代码 #

var entry = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Logger');
var dt = new Date();
var today = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate()).valueOf();
var lastdate = new Date(dt.getFullYear(), dt.getMonth(), dt.getDate() - 32).valueOf();
var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('master');

function run() {
  // 收集所有日志记录
  const logEntries = [];
  
  logEntries.push(mainfilter());
  logEntries.push(andhrafilter());
  logEntries.push(assamfilter());
  
  // 过滤空记录，一次性写入Logger
  const validLogs = logEntries.filter(item => item.length > 0);
  if (validLogs.length > 0) {
    entry.getRange(entry.getLastRow() + 1, 1, validLogs.length, validLogs[0].length).setValues(validLogs);
  }
}

function mainfilter() {
  var vA = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  var found = false;
  var output = [];
  for (var i = 0; i < vA.length; i++) {
    var date = new Date(vA[i][0]);
    var dv = new Date(date.getFullYear(), date.getMonth(), date.getDate()).valueOf();
    if (dv >= lastdate && dv <= today) {
      output.push([vA[i][0], vA[i][1], vA[i][2], vA[i][3], vA[i][4], vA[i][6], vA[i][8]]);
      found = true;
    }
  }
  const csh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mainpage');
  if (found) {
    csh.getRange(csh.getLastRow() + 1, 1, output.length, output[0].length).setValues(output);
    return [dt, "Main Page", "Copied"];
  } else {
    return [dt, "Main Page", "No data"];
  }
}

function andhrafilter() {
  var vA1 = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  var csh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('andhra');
  var target1 = 'Andhra Pradesh';
  var found1 = false;
  var output1 = [];
  for (var i1 = 0; i1 < vA1.length; i1++) {
    var date1 = new Date(vA1[i1][0]);
    var dv1 = new Date(date1.getFullYear(), date1.getMonth(), date1.getDate()).valueOf();
    if (dv1 >= lastdate && dv1 <= today && vA1[i1][7] == target1) {
      output1.push([vA1[i1][0], vA1[i1][1], vA1[i1][2], vA1[i1][3], vA1[i1][4], vA1[i1][6], vA1[i1][8]]);
      found1 = true;
    }
  }
  if (found1) {
    csh1.getRange(csh1.getLastRow() + 1, 1, output1.length, output1[0].length).setValues(output1);
    return [dt, "Andhra Pradesh", "Copied"];
  } else {
    return [dt, "Andhra Pradesh", "No data"];
  }
}

function assamfilter() {
  var vA4 = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues();
  var csh4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('assam');
  var target4 = 'Assam';
  var found4 = false;
  var output4 = [];
  for (var i4 = 0; i4 < vA4.length; i4++) {
    var date4 = new Date(vA4[i4][0]);
    var dv4 = new Date(date4.getFullYear(), date4.getMonth(), date4.getDate()).valueOf();
    if (dv4 >= lastdate && dv4 <= today && vA4[i4][7] == target4) {
      output4.push([vA4[i4][0], vA4[i4][1], vA4[i4][2], vA4[i4][3], vA4[i4][4], vA4[i4][6], vA4[i4][8]]);
      found4 = true;
    }
  }
  if (found4) {
    csh4.getRange(csh4.getLastRow() + 1, 1, output4.length, output4[0].length).setValues(output4);
    return [dt, "Assam", "Copied"];
  } else {
    return [dt, "Assam", "No data"];
  }
}

核心修改点 #

1. 移除全局startRow变量，改为在run()函数中统一收集所有日志记录，最后一次性写入Logger工作表，彻底避免行号冲突。
2. 每个过滤函数不再单独写入日志，而是返回日志内容的数组，由主函数统一处理。
3. 减少了Spreadsheet API的调用次数，提升脚本运行效率。

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