将VBA双向VLOOKUP功能转换为Google Sheets Apps Script求助

我对VBA和Google Sheets Apps Script都不熟，已经靠搜索实现了Excel里的功能：主表B2橙色输入框粘贴数据后拆分到下方黄色区域，C列用VLOOKUP从多个参考表匹配数据，编辑B/C列时另一列同步更新（因为VLOOKUP只能左到右，参考表已经把A列复制到C列）。现在要把可用的VBA代码转成Google Sheets Apps Script方便共享，其他功能都完成了，就这部分卡壳。

原VBA代码：

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address = "$B$2" Then
    If Len("B2") > 1 Then
        Dim regex As Object
        Dim X As Variant
        Dim Y As Variant
        Set regex = CreateObject("VBScript.RegExp")
        regex.Pattern = "font[0-9]{1,2}="
        Y = Replace(Range("B2").Value, "(", "")
        Y = Replace(Y, ")", "")
        Y = regex.Replace(Y, "")
        X = Split(Y, ",")
        Range("B3").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
        Range("C6").Value = Application.WorksheetFunction.VLookup(Range("B6"), Sheets("A (Hum)").Range("A:B"), 2, 0)
        Range("C7").Value = Application.WorksheetFunction.VLookup(Range("B7"), Sheets("B (Blaster)").Range("A:B"), 2, 0)
        Range("C8").Value = Application.WorksheetFunction.VLookup(Range("B8"), Sheets("C (Force)").Range("A:B"), 2, 0)
        Range("C9").Value = Application.WorksheetFunction.VLookup(Range("B9"), Sheets("D (Lockup)").Range("A:B"), 2, 0)
        Range("C10").Value = Application.WorksheetFunction.VLookup(Range("B10"), Sheets("E (FoC)").Range("A:B"), 2, 0)
        Range("C11").Value = Application.WorksheetFunction.VLookup(Range("B11"), Sheets("F (Ignition)").Range("A:B"), 2, 0)
    End If
End If

'Stop the reciprocal vlookups causing an error
Application.EnableEvents = False

' IF Column B is selected, changed column C
If Target.Address = "$B$6" Then
    Range("C6").Value = Application.WorksheetFunction.VLookup(Range("B6"), Sheets("A (Hum)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$7" Then
    Range("C7").Value = Application.WorksheetFunction.VLookup(Range("B7"), Sheets("B (Blaster)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$8" Then
    Range("C8").Value = Application.WorksheetFunction.VLookup(Range("B8"), Sheets("C (Force)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$9" Then
    Range("C9").Value = Application.WorksheetFunction.VLookup(Range("B9"), Sheets("D (Lockup)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$10" Then
    Range("C10").Value = Application.WorksheetFunction.VLookup(Range("B10"), Sheets("E (FoC)").Range("A:B"), 2, 0)
End If
If Target.Address = "$B$11" Then
    Range("C11").Value = Application.WorksheetFunction.VLookup(Range("B11"), Sheets("F (Ignition)").Range("A:B"), 2, 0)
End If

' And Vice Versa
If Target.Address = "$C$6" Then
    Range("B6").Value = Application.WorksheetFunction.VLookup(Range("C6"), Sheets("A (Hum)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$7" Then
    Range("B7").Value = Application.WorksheetFunction.VLookup(Range("C7"), Sheets("B (Blaster)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$8" Then
    Range("B8").Value = Application.WorksheetFunction.VLookup(Range("C8"), Sheets("C (Force)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$9" Then
    Range("B9").Value = Application.WorksheetFunction.VLookup(Range("C9"), Sheets("D (Lockup)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$10" Then
    Range("B10").Value = Application.WorksheetFunction.VLookup(Range("C10"), Sheets("E (FoC)").Range("B:C"), 2, 0)
End If
If Target.Address = "$C$11" Then
    Range("B11").Value = Application.WorksheetFunction.VLookup(Range("C11"), Sheets("F (Ignition)").Range("B:C"), 2, 0)
End If

'Re-enable events after updates
Application.EnableEvents = True

End Sub

以下是对应功能的Apps Script代码，绑定到你的表格即可使用：

function onEdit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const mainSheet = ss.getSheetByName("主表"); // 替换为你的主表实际名称
  const target = e.range;
  const targetA1 = target.getA1Notation();

  // 处理B2单元格的粘贴拆分逻辑
  if (targetA1 === "$B$2") {
    const b2Value = target.getValue().toString();
    if (b2Value.length > 1) {
      // 正则替换去掉指定字符，清理数据
      const regex = /font\d{1,2}=/g;
      let processedValue = b2Value.replace(/[()]/g, "").replace(regex, "");
      // 按逗号拆分并去除每个元素的首尾空格
      const splitValues = processedValue.split(",").map(val => val.trim());
      // 清空B3开始的旧数据
      mainSheet.getRange("B3:B").clearContent();
      // 写入拆分后的数据（转置为列）
      mainSheet.getRange(3, 2, splitValues.length, 1).setValues(splitValues.map(val => [val]));

      // 初始化C6-C11的匹配值
      updateCFromB(mainSheet, 6, "A (Hum)");
      updateCFromB(mainSheet, 7, "B (Blaster)");
      updateCFromB(mainSheet, 8, "C (Force)");
      updateCFromB(mainSheet, 9, "D (Lockup)");
      updateCFromB(mainSheet, 10, "E (FoC)");
      updateCFromB(mainSheet, 11, "F (Ignition)");
    }
  }

  // 加锁防止递归触发onEdit事件
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;

  try {
    // 处理B列单元格变更，同步更新对应C列
    switch(targetA1) {
      case "$B$6":
        updateCFromB(mainSheet, 6, "A (Hum)");
        break;
      case "$B$7":
        updateCFromB(mainSheet, 7, "B (Blaster)");
        break;
      case "$B$8":
        updateCFromB(mainSheet, 8, "C (Force)");
        break;
      case "$B$9":
        updateCFromB(mainSheet, 9, "D (Lockup)");
        break;
      case "$B$10":
        updateCFromB(mainSheet, 10, "E (FoC)");
        break;
      case "$B$11":
        updateCFromB(mainSheet, 11, "F (Ignition)");
        break;
    }

    // 处理C列单元格变更，同步更新对应B列
    switch(targetA1) {
      case "$C$6":
        updateBFromC(mainSheet, 6, "A (Hum)");
        break;
      case "$C$7":
        updateBFromC(mainSheet, 7, "B (Blaster)");
        break;
      case "$C$8":
        updateBFromC(mainSheet, 8, "C (Force)");
        break;
      case "$C$9":
        updateBFromC(mainSheet, 9, "D (Lockup)");
        break;
      case "$C$10":
        updateBFromC(mainSheet, 10, "E (FoC)");
        break;
      case "$C$11":
        updateBFromC(mainSheet, 11, "F (Ignition)");
        break;
    }
  } finally {
    lock.releaseLock();
  }
}

// 从B列值匹配参考表，更新对应C列
function updateCFromB(mainSheet, rowNum, refSheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const refSheet = ss.getSheetByName(refSheetName);
  const bValue = mainSheet.getRange(rowNum, 2).getValue();
  if (!bValue) {
    mainSheet.getRange(rowNum, 3).clearContent();
    return;
  }
  
  // 获取参考表A:B的数据范围
  const refData = refSheet.getRange("A:B").getValues();
  // 遍历查找匹配项
  for (let row of refData) {
    if (row[0] === bValue) {
      mainSheet.getRange(rowNum, 3).setValue(row[1]);
      return;
    }
  }
  // 无匹配值时清空C列单元格
  mainSheet.getRange(rowNum, 3).clearContent();
}

// 从C列值匹配参考表，更新对应B列
function updateBFromC(mainSheet, rowNum, refSheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const refSheet = ss.getSheetByName(refSheetName);
  const cValue = mainSheet.getRange(rowNum, 3).getValue();
  if (!cValue) {
    mainSheet.getRange(rowNum, 2).clearContent();
    return;
  }
  
  // 获取参考表B:C的数据范围
  const refData = refSheet.getRange("B:C").getValues();
  // 遍历查找匹配项
  for (let row of refData) {
    if (row[0] === cValue) {
      mainSheet.getRange(rowNum, 2).setValue(row[1]);
      return;
    }
  }
  // 无匹配值时清空B列单元格
  mainSheet.getRange(rowNum, 2).clearContent();
}

使用说明 #

1. 打开你的Google Sheets表格，点击菜单栏的「扩展程序」→「Apps Script」
2. 清空默认代码，粘贴上面的脚本
3. 将代码中的"主表"替换为你的实际主表名称
4. 保存脚本，回到表格后即可触发功能：
   • 在B2粘贴数据后会自动拆分到B3开始的区域
   • 编辑B6-B11时，C列对应单元格会自动匹配参考表数据
   • 编辑C6-C11时，B列对应单元格会自动反向匹配参考表数据

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