如何用Google Apps Script实现两列匹配时的列数据批量拼接？

需求：当A列与D列内容相等时，将所有匹配行的B列数据用 | 拼接后写入C列。之前用公式 ARRAYFORMULA(TEXTJOIN(" | ",True,IF($A$2:A=D2,$B$2:$B,""))) 可以实现，但大数据集下运行极慢，自己写的脚本仅能逐行拼接当前行内容，无法得到所有匹配项的拼接结果。

原脚本代码 #

function my_concat() {
  
var ssraw = SpreadsheetApp.openById("1blPwXgg1DTJCTxmWikU5b0IZUgDxxQR13WbN7UI4_Yo");
var sheetraw = ssraw.getSheetByName("TEST");
var range = sheetraw.getRange("B2:B");
var data = range.getValues();
var last = range.getLastRow();


for(var i = 2; i < data.length; i++){
var range1 = sheetraw.getRange(i,1).getValue();
var range2 = sheetraw.getRange(i,4).getValues();
    
    if(range1 == range2){
    var data1 = (data[i] + " | " + data[i]);
    sheetraw.getRange('C' + 2 + ':C' + last).setValue(data1);


}
}
}

原脚本问题分析 #

1. 效率极低：循环中多次调用getRange()和getValue()，每次都要和服务器交互，大数据下卡顿严重
2. 逻辑错误：range2 = sheetraw.getRange(i,4).getValues()返回的是二维数组，和单个值range1比较永远不相等，实际执行逻辑完全错误
3. 拼接逻辑错误：仅重复拼接当前行的B列值，未收集所有匹配A列值的B列数据
4. 写入错误：每次循环都覆盖整列C，完全不符合需求

修正后的脚本 #

function concatMatchingData() {
  const ss = SpreadsheetApp.openById("1blPwXgg1DTJCTxmWikU5b0IZUgDxxQR13WbN7UI4_Yo");
  const sheet = ss.getSheetByName("TEST");
  // 一次性读取所有数据（A2:D最后一行），减少服务器交互次数
  const allData = sheet.getRange(2, 1, sheet.getLastRow() - 1, 4).getValues();
  
  // 用对象分组：key是A列值，value是对应的B列值数组
  const aToBMap = {};
  allData.forEach(row => {
    const aVal = row[0];
    const bVal = row[1];
    if (!aToBMap[aVal]) {
      aToBMap[aVal] = [];
    }
    aToBMap[aVal].push(bVal);
  });
  
  // 生成C列的结果数组
  const result = allData.map(row => {
    const dVal = row[3];
    // 如果D列值在分组里，就拼接所有B列值，否则留空
    return aToBMap[dVal] ? [aToBMap[dVal].join(" | ")] : [""];
  });
  
  // 一次性写入C列，大幅提升效率
  sheet.getRange(2, 3, result.length, 1).setValues(result);
}

修正说明 #

1. 一次性读写：仅两次与服务器交互（读取所有数据、写入结果），大数据下效率远超原脚本和公式
2. 分组逻辑：先把A列和对应的B列值分组存储，后续直接通过D列值快速查找匹配的B列数据
3. 正确拼接：将所有匹配的B列值用|拼接，完全符合需求
4. 批量写入：一次性写入所有结果，避免多次服务器请求

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