如何将Excel库存更新VBA脚本迁移至Google Sheets实现跨表库存更新

• 业务用途：搭建适配开票、库存管控场景的表格系统
• 工作表规则：
  • Shop（发票模板工作表）：A22:A41存储库存编码，B22:B41存储对应商品销售数量
  • Stock（库存工作表）：需在A5:I1000区域匹配Shop表传入的库存编码，完成两个字段更新：E列（第5列）在库数量扣减对应销量、G列（第7列）已售数量累加对应销量
• 现存问题：已在Excel端通过VBA实现上述逻辑，需要改造为Google Sheets可运行的版本。

Sub UpdateStockDetailsDjB()
    Dim intRow As Integer
    Dim intStk As Integer
    Dim strPrd As String
' 逐行处理A22到A41的商品行
    intRow = 22
    Do
        If Worksheets("Shop").Cells(intRow, 1).Value = "" Then
            Exit Do
        End If
        strPrd = (Worksheets("Shop").Cells(intRow, 1).Value)
            intStk = fncFindPrdStk(strPrd) ' 查找库存编码在Stock表的所在行
        Worksheets("Stock").Cells(intStk, 5).Value = Worksheets("Stock").Cells(intStk, 5).Value - Worksheets("Shop").Cells(intRow, 2).Value ' 更新在库数量
        Worksheets("Stock").Cells(intStk, 7).Value = Worksheets("Stock").Cells(intStk, 7).Value + Worksheets("Shop").Cells(intRow, 2).Value ' 更新已售数量
        If intRow = 41 Then
            Exit Do
        End If
        intRow = intRow + 1
    Loop
' 流程结束调用清空函数
Call Clearhop
End Sub


Function fncFindPrdStk(strPrd As String) As Variant
    Dim intRow As Integer
' 在Stock表A列查找对应库存编码
    intRow = 3
    Do
        If Trim(Worksheets("Stock").Cells(intRow, 1).Value) = strPrd Then
            Exit Do
        End If
        If intRow = 1510 Then
            MsgBox "库存编码缺失: " & strPrd, vbCritical
            Exit Do
        End If
        intRow = intRow + 1
    Loop
' 返回匹配到的行号
    fncFindPrdStk = intRow
End Function

Google Sheets使用Apps Script（类JavaScript语法）替代VBA，操作步骤如下：

• 打开目标Google表格，点击顶部菜单栏「扩展程序」→「Apps Script」进入脚本编辑页
• 清空编辑页默认生成的空函数，粘贴以下代码：

function updateStockDetailsDjB() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const shopSheet = ss.getSheetByName("Shop");
  const stockSheet = ss.getSheetByName("Stock");

  // 读取Shop表销售数据，过滤空编码行
  const saleRecords = shopSheet.getRange("A22:B41").getValues()
    .filter(row => row[0].toString().trim() !== "");

  // 读取Stock表库存数据，构建编码-行索引映射提升匹配效率
  const stockRange = stockSheet.getRange("A5:G1000");
  const stockRecords = stockRange.getValues();
  const codeIndexMap = new Map();
  stockRecords.forEach((row, idx) => {
    const code = row[0].toString().trim();
    if (code) codeIndexMap.set(code, idx);
  });

  const missingCodes = [];
  // 逐行更新库存
  saleRecords.forEach(record => {
    const productCode = record[0].toString().trim();
    const saleQty = Number(record[1]) || 0;
    if (!codeIndexMap.has(productCode)) {
      missingCodes.push(productCode);
      return;
    }
    const rowOffset = codeIndexMap.get(productCode);
    const stockRowNum = rowOffset + 5; // 库存数据从第5行开始，索引偏移+5
    // 扣减在库数量
    const stockRemain = stockRecords[rowOffset][4];
    stockSheet.getRange(stockRowNum, 5).setValue(stockRemain - saleQty);
    // 累加已售数量
    const soldTotal = stockRecords[rowOffset][6];
    stockSheet.getRange(stockRowNum, 7).setValue(soldTotal + saleQty);
  });

  // 缺失编码弹窗提示
  if (missingCodes.length > 0) {
    SpreadsheetApp.getUi().alert(`以下库存编码未在库存表中匹配到：${missingCodes.join("、")}`);
  }

  // 原VBA中Clearhop函数调用，有对应清空需求可自行实现后放开注释
  // clearhop();
}

• 保存脚本，授权后即可直接运行，也可以将函数绑定为表格按钮触发

适配说明：原VBA默认从Stock表第3行开始匹配编码，上述代码按你描述的A5起始匹配区域编写，如果需要对齐原VBA的查找范围，只需将读取库存的范围改为A3:G1510，行号偏移值从5改为3即可。

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