如何在Google Sheet中清洗含多属性的聊天导出数据

针对你1000条聊天长文本数据的结构化需求，以下提供两种高效解决方案：公式法（无需代码）和脚本法（适合大规模数据），均基于你的目标字段（Tender、N、Nominal等）设计。

一、公式法（快速实现） #

假设原始数据在原始数据工作表的A列（A2开始为有效数据），在新工作表的A2输入以下公式，自动生成结构化数据：

步骤1：提取并配对Tender与所有n条目 #

=LET(
  raw, 原始数据!A2:A,
  getTender, LAMBDA(txt, IFERROR(REGEXEXTRACT(txt, "Tender: (.*?)\n"), "")),
  getNItems, LAMBDA(txt, IFERROR(SPLIT(REGEXREPLACE(txt, "^.*?\n(.*)$", "$1"), CHAR(10)), "")),
  pairData, MAP(raw, LAMBDA(row, IF(row="",, HSTACK(REPT(getTender(row), COUNTA(getNItems(row))), getNItems(row))))),
  flattened, FLATTEN(pairData),
  reshape, INDEX(flattened, SEQUENCE(COUNTA(flattened)/2, 2)),
  reshape
)

此公式会将每条原始数据中的所有n条目与对应的Tender配对，展开为两行一组的结构。

步骤2：拆分n条目标字段 #

在C2（N字段）、D2（Nominal字段）、E2（Price字段）分别输入：

# N字段
=REGEXEXTRACT(B2, "n(\d+)")

# Nominal字段
=REGEXEXTRACT(B2, "Nominal: (.*?)\s")

# Price字段（根据数据格式调整，示例匹配Price后的所有内容）
=REGEXEXTRACT(B2, "Price: (.*)")

下拉公式即可批量提取所有字段。

二、Google Apps Script法（高效处理大规模数据） #

若数据量超过1000条，脚本法效率更高，步骤如下：

1. 打开你的Google Sheet，点击「扩展程序」→「Apps Script」
2. 清空默认代码，粘贴以下脚本（根据你的工作表名称调整rawSheetName和targetSheetName）：

function cleanChatBusinessData() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const rawSheetName = "原始数据"; // 替换为你的原始数据工作表名
  const targetSheetName = "结构化数据"; // 替换为目标工作表名
  
  const rawSheet = ss.getSheetByName(rawSheetName);
  const targetSheet = ss.getSheetByName(targetSheetName) || ss.insertSheet(targetSheetName);
  
  const rawData = rawSheet.getDataRange().getValues();
  const outputRows = [];
  
  // 添加表头
  outputRows.push(["Tender", "N", "Nominal", "Price"]);
  
  // 遍历每条原始数据
  for (let i = 1; i < rawData.length; i++) {
    const text = rawData[i][0];
    if (!text) continue;
    
    // 提取Tender值
    const tenderMatch = text.match(/Tender: (.*?)\n/);
    const tender = tenderMatch ? tenderMatch[1].trim() : "";
    
    // 筛选所有以n开头的条目
    const nEntries = text.split("\n").filter(line => line.trim().startsWith("n"));
    
    // 拆分每个n条目的字段
    nEntries.forEach(entry => {
      const nMatch = entry.match(/n(\d+)/);
      const n = nMatch ? nMatch[1] : "";
      
      const nominalMatch = entry.match(/Nominal: (.*?)\s/);
      const nominal = nominalMatch ? nominalMatch[1].trim() : "";
      
      const priceMatch = entry.match(/Price: (.*)/);
      const price = priceMatch ? priceMatch[1].trim() : "";
      
      // 将结构化数据加入结果
      outputRows.push([tender, n, nominal, price]);
    });
  }
  
  // 写入目标表格
  targetSheet.clear();
  targetSheet.getRange(1, 1, outputRows.length, outputRows[0].length).setValues(outputRows);
  SpreadsheetApp.getUi().alert("数据清洗完成，已写入" + targetSheetName + "工作表");
}

3. 点击「运行」，授权脚本权限后即可自动完成清洗。

注意事项 #

• 正则表达式需根据你的实际数据格式微调：若字段分隔符、格式有变化，可修改match方法中的正则规则
• 公式法若出现错误，检查原始数据是否有不符合格式的条目，可添加IFERROR处理异常

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