求助完善Google Sheet脚本：实现单元格复制插入与数据拆分

原始数据格式 #

A    B     C       D       E       F       G       H
1 Name Phone Item(1) Cost(1) Item(2) Cost(2) Item(3) Cost(3) ...最多到30列
2 John 55555 Hat     5       Shoes   10      Tie     25
3 Sue  88888 Dress   50
4 Joe  99999 Tie     25      Hat     5

目标格式 #

A    B     C       D       E       F       G       H
1 Name Phone Item(1) Cost(1) Item(2) Cost(2) Item(3) Cost(3) ...最多到30列
2 John 55555 Hat     5
3            Shoes   10      
4            Tie     25
5 Sue  88888 Dress   50
6 Joe  99999 Tie     25      
7            Hat     5

我的思路 #

• 查找包含“Item”的单元格，确定Item列的起始位置
• 扫描每一行数据，若后续Item列有非空值，复制该Item及其对应Cost
• 在当前行下方插入新行，将复制的内容粘贴到新行的Item(1)和Cost(1)列

现有代码（待完善） #

// Find the word "Item"
let ranges = SpreadsheetApp.getActive() 
   .createTextFinder("Item")
   .matchEntireCell(false)
   .matchCase(false)
   .matchFormulaText(false)
   .ignoreDiacritics(true)
   .findAll(); 

ranges.forEach(
  function (range) {
    {Scan all the cells in the column and do this <== 这里需要帮助!}
         ranges.getRange('J5').activate();
         ranges.getActiveSheet().insertRowsAfter(ranges.getActiveRange().getRow(), 1);
         ranges.getActiveRange()
             .offset(0, 0, 1, ranges.getActiveRange().getNumColumns()) 
             .activate();
         ranges.getRange('G5:H5').activate();
         ranges.getRange('J6:K6')
             .copyTo(ranges.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

完善后的脚本 #

核心注意点：必须从最后一行往前处理，否则插入新行后会打乱后续行的索引。同时通过表头定位所有Item列的位置，批量处理每组Item-Cost数据。

function splitItemCostRows() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();
  const headerRow = values[0];
  
  // 筛选出所有包含"Item"的列索引（数组为0-based）
  const itemColumns = headerRow.map((cell, idx) => cell.includes("Item") ? idx : null).filter(idx => idx !== null);
  if (itemColumns.length === 0) return;

  // 从最后一行开始向上遍历，避免插入行影响未处理的行
  for (let rowIdx = values.length - 1; rowIdx >= 1; rowIdx--) {
    const currentRow = values[rowIdx];
    let insertCount = 0;

    // 遍历除第一组外的所有Item-Cost列对
    for (let colIdx = 1; colIdx < itemColumns.length; colIdx++) {
      const itemCol = itemColumns[colIdx];
      const costCol = itemCol + 1;
      const itemValue = currentRow[itemCol];
      const costValue = currentRow[costCol];

      // 仅当Item和Cost都非空时执行拆分
      if (itemValue && costValue) {
        const targetRow = rowIdx + insertCount + 1;
        // 插入新行
        sheet.insertRowAfter(targetRow - 1);
        // 填充新行：Name/Phone留空，Item/Cost放到第一组列位置
        sheet.getRange(targetRow, 1).setValue("");
        sheet.getRange(targetRow, 2).setValue("");
        sheet.getRange(targetRow, itemColumns[0] + 1).setValue(itemValue);
        sheet.getRange(targetRow, itemColumns[0] + 2).setValue(costValue);
        // 清空原位置的Item和Cost数据
        sheet.getRange(rowIdx + 1, itemCol + 1).setValue("");
        sheet.getRange(rowIdx + 1, costCol + 1).setValue("");
        insertCount++;
      }
    }
  }
}

关键细节说明 #

1. 反向遍历行：从表格最后一行开始处理，确保插入新行不会改变未处理行的索引位置，避免数据混乱。
2. 列定位逻辑：通过表头自动识别所有Item列，适配最多30列的扩展需求，无需硬编码列位置。
3. 1-based与0-base转换：Google Sheets的单元格Range是1-based索引，而数组是0-based，代码中做了对应转换避免索引错误。
4. 非空判断：仅处理Item和Cost都有值的列对，避免空行插入。

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