如何创建Google Sheets Macro/Apps Script将Drive新增Excel数据同步到Master总表？

前置准备 #

• 提前在Google Drive中创建好主控总表（Master Google Sheet），记录下该表格的ID（表格URL中d/和/edit之间的字符串）
• 确认需要监听的Excel文件所在的Drive文件夹，记录对应文件夹ID

核心脚本实现 #

// 全局参数配置，替换为你自己的对应ID
const CONFIG = {
  WATCH_FOLDER_ID: "替换为你要监听的Excel所在文件夹ID",
  MASTER_SHEET_ID: "替换为你的主控总表ID",
  VEHICLE_DATA_ROWS_PER_BLOCK: 6 // 单台车辆对应有效数据行数
}

// 驱动函数：触发时读取新增Excel的内容
function processNewExcel(e) {
  // 校验触发事件是否为新增Excel文件
  const file = e ? DriveApp.getFileById(e.getId()) : null;
  if (!file || file.getMimeType() !== MimeType.MICROSOFT_EXCEL) return;

  // 将Excel转换为临时Google Sheet方便读取
  const tempSpreadsheet = SpreadsheetApp.openById(
    Drive.Files.copy({title: "temp_excel_convert", mimeType: MimeType.GOOGLE_SHEETS}, file.getId()).id
  );
  const sourceSheet = tempSpreadsheet.getSheets()[0];
  const sourceData = sourceSheet.getDataRange().getValues();

  // 处理源数据，提取有效行并填充车辆编号
  const processedData = processSourceData(sourceData);

  // 追加到主控总表
  const masterSheet = SpreadsheetApp.openById(CONFIG.MASTER_SHEET_ID).getSheets()[0];
  if (processedData.length > 0) {
    masterSheet.getRange(masterSheet.getLastRow() + 1, 1, processedData.length, processedData[0].length).setValues(processedData);
  }

  // 删除临时转换的表格，避免占用存储空间
  DriveApp.getFileById(tempSpreadsheet.getId()).setTrashed(true);
}

// 源数据处理函数：填充车辆编号、提取VEHICLE和VEHICLE TOTALS之间的有效数据
function processSourceData(sourceData) {
  const result = [];
  let currentVehicleNo = "";
  let isInVehicleBlock = false;
  let rowCounter = 0;

  for (let i = 0; i < sourceData.length; i++) {
    const row = sourceData[i];
    const firstColValue = row[0] ? row[0].toString().trim() : "";

    // 识别到VEHICLE标记，开始记录当前车辆编号，进入数据块
    if (firstColValue.startsWith("VEHICLE") && !firstColValue.includes("TOTALS")) {
      currentVehicleNo = firstColValue.replace("VEHICLE", "").trim();
      isInVehicleBlock = true;
      rowCounter = 0;
      continue;
    }

    // 识别到VEHICLE TOTALS标记，结束当前车辆数据块
    if (firstColValue === "VEHICLE TOTALS") {
      isInVehicleBlock = false;
      continue;
    }

    // 处于数据块中且未超出单车辆有效行数时，填充车辆编号并记录行
    if (isInVehicleBlock && rowCounter < CONFIG.VEHICLE_DATA_ROWS_PER_BLOCK) {
      // 将车辆编号添加到行的最后一列，可自行调整到指定列位置
      const processedRow = [...row, currentVehicleNo];
      result.push(processedRow);
      rowCounter++;
    }
  }
  return result;
}

// 触发器安装函数：运行一次即可绑定Drive新增文件触发事件
function installTrigger() {
  ScriptApp.newTrigger("processNewExcel")
    .forUserCalendar(CONFIG.WATCH_FOLDER_ID)
    .onChange()
    .create();
}

操作步骤 #

1. 打开Google Apps Script控制台，新建空白项目
2. 将上述代码复制到代码编辑框中，替换CONFIG配置项里的两个ID为你自己的对应ID
3. 在Apps Script编辑器左侧点击「服务」，找到「Drive API」添加后保存
4. 先运行一次installTrigger函数，按照提示完成权限授权，即可完成触发器安装
5. 后续对应文件夹内新增Excel文件时，脚本会自动运行，处理后的数据会自动追加到主控总表最后一行

提示：如果后续源数据的有效行数、车辆标记文本有变动，直接修改CONFIG里的参数和标记判断逻辑即可适配。

数据表示例：

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