如何在Google Apps Script中按条件修改Google Sheet数据

完整可运行代码 #

function updateDeviceAssetOwner() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 获取两个工作表对象
  const formSheet = ss.getSheetByName("Form Responses");
  const deviceSheet = ss.getSheetByName("Devices");

  // 读取表单响应所有数据，.slice(1)用于跳过表头行，无表头可删除该方法
  const formRows = formSheet.getDataRange().getValues().slice(1);
  // 读取设备表所有数据，.slice(1)用于跳过表头行，无表头可删除该方法
  const deviceRows = deviceSheet.getDataRange().getValues().slice(1);

  // 列索引配置，可根据实际表格列顺序调整：索引从0开始计数，对应表格第一列
  const DEVICE_USER_COL = 0; // Devices表user_name列索引
  const DEVICE_ASSET_COL = 1; // Devices表asset ID列索引
  const FORM_USER_COL = 0; // Form Responses表user_name列索引
  const FORM_OLD_ASSET_COL = 1; // Form Responses表old asset ID列索引
  const FORM_NEW_ASSET_COL = 2; // Form Responses表new asset ID列索引

  formRows.forEach(formRow => {
    // 空值校验逻辑：任意关键字段为空直接跳过当前记录，避免空值报错
    const userName = formRow[FORM_USER_COL];
    const oldAssetId = formRow[FORM_OLD_ASSET_COL];
    const newAssetId = formRow[FORM_NEW_ASSET_COL];
    if (!userName || !oldAssetId || !newAssetId) return;

    // 匹配旧资产ID对应设备行，置空用户名
    const oldAssetIndex = deviceRows.findIndex(deviceRow => String(deviceRow[DEVICE_ASSET_COL]).trim() === String(oldAssetId).trim());
    if (oldAssetIndex > -1) {
      deviceSheet.getRange(oldAssetIndex + 2, DEVICE_USER_COL + 1).setValue("");
    }

    // 匹配新资产ID对应设备行，赋值用户名
    const newAssetIndex = deviceRows.findIndex(deviceRow => String(deviceRow[DEVICE_ASSET_COL]).trim() === String(newAssetId).trim());
    if (newAssetIndex > -1) {
      deviceSheet.getRange(newAssetIndex + 2, DEVICE_USER_COL + 1).setValue(userName);
    }
  });
}

核心特性说明 #

• 内置空值校验逻辑，Form Responses中任意一条记录的user_name、old asset ID、new asset ID为空时，直接跳过该条记录，不会触发报错
• 资产ID匹配时自动转为字符串并去除首尾空格，避免数字/字符串格式不一致导致匹配失败的问题
• 匹配不到对应资产ID时自动跳过，不会强制修改数据

部署配置步骤 #

1. 打开目标Google Sheet，点击顶部菜单栏「扩展程序」->「Apps脚本」进入脚本编辑器
2. 将上述代码复制粘贴到编辑器中，自定义项目名称后保存
3. 配置自动触发规则：点击编辑器左侧「触发器」按钮，新增触发器，选择触发函数为updateDeviceAssetOwner，事件来源选择「电子表格」，事件类型选择「表单提交时」，保存后按提示授权脚本访问表格的权限即可
4. 若需要手动测试运行，直接在编辑器顶部选中updateDeviceAssetOwner函数，点击「运行」按钮即可完成一次全量数据更新

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