如何在Google Apps Script中改写ArrayFormula与VLOOKUP公式？

你的代码和原公式的逻辑存在几个关键偏差，导致返回null，以下是问题分析和修复方案：

问题点拆解 #

• 匹配逻辑颠倒：原公式是用Petition Status Report的J列值，匹配WO SR 22/23的P列，返回对应行的B列；但你代码里取的是B3:P范围，用B列（r[0]）去匹配J列值，完全搞反了匹配关系。
• 无效数据干扰：getRange("J3:J")会包含大量空行，容易导致无意义的匹配。
• 返回值对应错误：即使匹配成功，你返回的逻辑也和原公式不符。

修正后的代码 #

function vLookUpVALUE() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const wsWOSR = ss.getSheetByName("WO SR 22/23");
  const wsPetition = ss.getSheetByName("Petition Status Report");
  
  // 获取J列有效数据（从J3到最后一行）
  const lastRowPetition = wsPetition.getLastRow();
  const searchValues = wsPetition.getRange("J3:J" + lastRowPetition).getValues().flat();
  
  // 构建P列到B列的映射表，提升匹配效率
  const lastRowWOSR = wsWOSR.getLastRow();
  const wosrData = wsWOSR.getRange("B3:P" + lastRowWOSR).getValues();
  const lookupMap = new Map();
  wosrData.forEach(row => {
    const pColValue = row[14]; // B是索引0，P列对应索引14
    const bColValue = row[0];
    if (pColValue) lookupMap.set(pColValue, bColValue); // 跳过空的P列值
  });
  
  // 生成匹配结果，对应原公式的IFERROR逻辑
  const matchData = searchValues.map(value => {
    return [lookupMap.get(value) ?? null];
  });
  
  // 可选：将结果写入工作表（示例写入K列，可自行调整）
  wsPetition.getRange("K3:K" + (2 + matchData.length)).setValues(matchData);
  
  console.log(matchData);
}

关键说明 #

• 用Map构建键值对映射，比循环find效率更高，数据量大时优势明显。
• 精准限制数据范围，避免空行干扰匹配逻辑。
• 完全对齐原公式逻辑：以P列作为匹配键，返回对应B列的值，同时用?? null实现IFERROR的空值处理。

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