如何获取表单提交的最新行数据并整理至Google Sheets对应工作表？

核心实现思路 #

• 用持久化存储记录已处理的最后行号，避免重复处理旧数据
• 自动识别Response Data中的新增行，拆分指定列区域到对应目标工作表
• 支持手动触发整理或表单提交自动触发

修改后的完整代码 #

function onOpen() {
  try {
    const spreadsheet = SpreadsheetApp.getActive();
    const menuItems = [
      {name: '整理最新提交数据', functionName: 'organizeLatestResponses'},
      {name: '设置自动整理触发', functionName: 'setupFormSubmitTrigger'}
    ];
    spreadsheet.addMenu('数据整理', menuItems);
  } catch (e) {
    Logger.log('初始化菜单失败: %s', e.message);
  }
}

function organizeLatestResponses() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const responseSheet = ss.getSheetByName("Response Data");
  if (!responseSheet) {
    SpreadsheetApp.getUi().alert('未找到"Response Data"工作表');
    return;
  }

  // 获取已处理的最后行号（首次运行默认1，对应表头行）
  const props = PropertiesService.getScriptProperties();
  const lastProcessedRow = parseInt(props.getProperty('lastProcessedRow')) || 1;
  const totalRows = responseSheet.getLastRow();

  if (totalRows <= lastProcessedRow) {
    SpreadsheetApp.getUi().alert('没有新的提交数据需要处理');
    return;
  }

  // 批量处理所有新增行
  for (let row = lastProcessedRow + 1; row <= totalRows; row++) {
    // 拆分C-F列数据到Ink工作表
    const inkDataRange = responseSheet.getRange(row, 3, 1, 4); // 行号row，第3列(C)开始，1行4列
    const inkSheet = ss.getSheetByName("Ink");
    if (inkSheet) {
      const targetRow = inkSheet.getLastRow() + 1;
      inkDataRange.copyTo(inkSheet.getRange(targetRow, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    }

    // 拆分G-J列数据到colors工作表
    const colorsDataRange = responseSheet.getRange(row, 7, 1, 4); // 行号row，第7列(G)开始，1行4列
    const colorsSheet = ss.getSheetByName("colors");
    if (colorsSheet) {
      const targetRow = colorsSheet.getLastRow() + 1;
      colorsDataRange.copyTo(colorsSheet.getRange(targetRow, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    }
  }

  // 更新已处理行号记录
  props.setProperty('lastProcessedRow', totalRows.toString());
  SpreadsheetApp.getUi().alert('已完成最新数据整理');
}

// 设置表单提交自动触发整理（仅需运行一次）
function setupFormSubmitTrigger() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('organizeLatestResponses')
    .forSpreadsheet(ss)
    .onFormSubmit()
    .create();
  SpreadsheetApp.getUi().alert('已设置表单提交自动整理');
}

关键细节说明 #

• 持久化行号记录：通过PropertiesService存储最后处理的行号，关闭表格后记录不会丢失
• 精准区域定位：用getRange(row, column, numRows, numColumns)按行列索引定位，比硬编码单元格地址更可靠
• 自动触发配置：运行setupFormSubmitTrigger后，每次有新表单提交会自动执行整理，无需手动操作
• 容错处理：增加工作表存在性检查，避免因工作表缺失导致报错

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