如何在Google Sheets中实现批量单元格更新及跨工作表物品调换数值同步功能

针对你这个需要在主工作表和各人员独立库存表之间同步物品调换的需求，我们可以用Google Apps Script来实现这个自定义功能，下面是具体的步骤和代码方案：

一、先确认表格结构规范 #

为了脚本能准确识别数据，先统一表格格式：

• 主工作表（比如命名为「物品清单」）：A列为物品名称，B、C、D...列为对应人员的持有数量，比如B列是史蒂夫，C列是戴夫，D列是威廉。
• 各人员独立工作表：工作表名称直接用人员名字（比如「史蒂夫」「戴夫」），A列为物品名称，B列为该物品的持有数量。

二、编写Apps Script实现调换逻辑 #

打开你的Google Sheets，点击菜单栏「扩展程序」→「Apps Script」，把默认代码替换成下面的脚本：

function swapItems(fromItem, toItem, swapQty) {
  // 配置参数
  const mainSheetName = "物品清单";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName(mainSheetName);
  
  // 获取主表的物品列和人员列
  const itemRange = mainSheet.getRange("A:A").getValues().flat();
  const personColumns = mainSheet.getRange(1, 2, 1, mainSheet.getLastColumn() - 1).getValues().flat();
  
  // 找到要调换的两个物品在主表的行号
  const fromRow = itemRange.indexOf(fromItem) + 1;
  const toRow = itemRange.indexOf(toItem) + 1;
  
  if (fromRow === 0 || toRow === 0) {
    SpreadsheetApp.getUi().alert("未找到指定的物品，请检查名称是否正确！");
    return;
  }
  
  // 遍历每个人员，更新主表和个人表
  personColumns.forEach((person) => {
    // 更新主表：减少fromItem的数量，增加toItem的数量
    const fromCurrentQty = mainSheet.getRange(fromRow, personColumns.indexOf(person) + 2).getValue();
    const toCurrentQty = mainSheet.getRange(toRow, personColumns.indexOf(person) + 2).getValue();
    
    if (fromCurrentQty < swapQty) {
      SpreadsheetApp.getUi().alert(`${person}的${fromItem}数量不足，无法完成调换！`);
      return;
    }
    
    mainSheet.getRange(fromRow, personColumns.indexOf(person) + 2).setValue(fromCurrentQty - swapQty);
    mainSheet.getRange(toRow, personColumns.indexOf(person) + 2).setValue(toCurrentQty + swapQty);
    
    // 更新人员个人表
    const personSheet = ss.getSheetByName(person);
    if (!personSheet) {
      SpreadsheetApp.getUi().alert(`未找到${person}的库存工作表，请检查名称是否正确！`);
      return;
    }
    
    const personItemRange = personSheet.getRange("A:A").getValues().flat();
    const personFromRow = personItemRange.indexOf(fromItem) + 1;
    const personToRow = personItemRange.indexOf(toItem) + 1;
    
    if (personFromRow === 0 || personToRow === 0) {
      SpreadsheetApp.getUi().alert(`${person}的工作表中未找到指定物品，请检查！`);
      return;
    }
    
    const personFromQty = personSheet.getRange(personFromRow, 2).getValue();
    const personToQty = personSheet.getRange(personToRow, 2).getValue();
    
    personSheet.getRange(personFromRow, 2).setValue(personFromQty - swapQty);
    personSheet.getRange(personToRow, 2).setValue(personToQty + swapQty);
  });
  
  SpreadsheetApp.getUi().alert(`已成功将${swapQty}个${fromItem}调换为${toItem}，所有表格已同步更新！`);
}

// 添加自定义菜单，方便操作
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('物品调换')
    .addItem('执行调换', 'showSwapDialog')
    .addToUi();
}

// 弹出输入对话框，让用户输入调换参数
function showSwapDialog() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    '物品调换设置',
    '请按格式输入：要调换的物品名称,目标物品名称,调换数量（示例：苹果,香蕉,10）',
    ui.ButtonSet.OK_CANCEL
  );
  
  if (response.getSelectedButton() === ui.Button.OK) {
    const input = response.getResponseText().split(',');
    if (input.length !== 3 || isNaN(parseInt(input[2]))) {
      ui.alert('输入格式错误，请按照示例格式输入！');
      return;
    }
    const fromItem = input[0].trim();
    const toItem = input[1].trim();
    const swapQty = parseInt(input[2].trim());
    swapItems(fromItem, toItem, swapQty);
  }
}

三、配置使用方式 #

1. 保存脚本后，回到Google Sheets页面刷新一下，你会看到菜单栏多了一个「物品调换」选项。
2. 点击「物品调换」→「执行调换」，会弹出对话框，按照提示输入（比如苹果,香蕉,10），点击确定即可完成调换和同步更新。

四、注意事项 #

• 确保所有人员的工作表名称和主表中的人员列标题完全一致（大小写也要匹配）。
• 物品名称在主表和个人表中要完全一致，否则脚本会提示未找到物品。
• 第一次执行脚本时，需要授权脚本访问你的Google Sheets，按照提示完成授权即可。
• 如果某人员的待调换物品数量不足，脚本会弹窗提示并终止操作，避免出现负数库存。

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