求助：基于Google Sheets App Script实现学生辅助措施表数据同步

我是一名特殊教育教师，正在制作表单追踪学生的辅助措施，目前开发Google Sheets App Script，需要将「Accommodation Tracking」工作表中的信息同步到「Test Database」工作表。具体需求：

• 当学生姓名旁的复选框被勾选时，将该学生所有以True/False标识的辅助措施信息同步到目标表对应表头下
• 每次提交时，每个勾选的学生在目标表中仅保留一条记录

以下是我当前的代码：

function updateTestDatabase() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var trackingSheet = ss.getSheetByName("Accommodation Tracking");
  var databaseSheet = ss.getSheetByName("Test Database");
  
  var trackingData = trackingSheet.getRange("A12:A21").getValues();
  var trackingStatus = trackingSheet.getRange("B12:B21").getValues();
  var trackingDataD = trackingSheet.getRange("D12:D21").getValues();
  var trackingStatusD = trackingSheet.getRange("B13:B21").getValues();

  var databaseData = databaseSheet.getRange("A:A").getValues();
  var databaseDataH = databaseSheet.getRange("H:H").getValues();

  for (var i = 0; i < trackingData.length; i++) {
    if (trackingData[i][0] == true && trackingStatus[i][0] == true) {
      for (var j = 0; j < databaseData.length; j++) {
        if (databaseData[j][0] == "") {
          databaseSheet.getRange(j + 1, 1).setValue(trackingData[i][0]);
          databaseSheet.getRange(j + 1, 7).setValue(trackingStatus[i][0]);
          break;
        }
      }
    }
  }

  for (var k = 0; k < trackingDataD.length; k++) {
    if (trackingDataD[k][0] == trackingData[k][0] && trackingStatusD[k][0] == true) {
      databaseSheet.getRange(k + 1, 8).setValue(trackingStatusD[k][0]);
    }
  }
}

相关说明 #

• 示例截图：「Accommodation Tracking」工作表示例、「Test Database」工作表对应示例
• 可操作示例表格：Google Sheets测试数据库表

修正后的代码及说明 #

针对需求，重写代码解决重复记录问题并优化同步逻辑：

function updateTestDatabase() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const trackingSheet = ss.getSheetByName("Accommodation Tracking");
  const databaseSheet = ss.getSheetByName("Test Database");

  // 获取追踪表A12到H21的完整数据（含姓名、勾选状态、所有辅助措施）
  const trackingRows = trackingSheet.getRange("A12:H21").getValues();
  
  // 获取数据库现有数据，建立学生姓名到行号的映射，快速判断是否存在
  const databaseRows = databaseSheet.getDataRange().getValues();
  const studentRowMap = new Map();

  // 遍历数据库记录，跳过表头（第一行）
  for (let i = 1; i < databaseRows.length; i++) {
    const studentName = databaseRows[i][0];
    if (studentName) {
      studentRowMap.set(studentName, i + 1); // 表格行号从1开始，故i+1
    }
  }

  // 处理每个追踪表的学生记录
  for (const row of trackingRows) {
    const studentName = row[0];
    const isChecked = row[1]; // B列的复选框状态

    // 仅处理已勾选且有姓名的学生
    if (isChecked && studentName) {
      // 提取D到H列的辅助措施数据（对应数组索引3到7）
      const accommodations = row.slice(3, 8);

      if (studentRowMap.has(studentName)) {
        // 学生已存在，更新对应行的辅助措施列（D到H列）
        const targetRow = studentRowMap.get(studentName);
        databaseSheet.getRange(targetRow, 4, 1, accommodations.length).setValues([accommodations]);
      } else {
        // 学生不存在，新增一行记录（按数据库表头顺序填充）
        const newRecord = [studentName, "", "", ...accommodations];
        databaseSheet.appendRow(newRecord);
        // 更新映射，加入新行号
        studentRowMap.set(studentName, databaseSheet.getLastRow());
      }
    }
  }
}

关键优化点 #

1. 避免重复记录：用Map存储数据库中已有的学生姓名和对应行号，提交时直接判断是否存在，无需遍历整列
2. 减少API调用：一次性获取完整数据范围，避免多次调用getRange和setValue，提升运行效率并符合Google Apps Script调用限制
3. 统一逻辑：合并新增和更新逻辑，代码更简洁易维护
4. 精准数据同步：直接映射追踪表的辅助措施列到数据库表对应位置，确保数据对齐

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