javascript - 合并应用程序脚本中的重复行

Question

当前工作表

必填表

我有一个组合重复行的插件，但我希望应用程序脚本运行 onEdit 触发器。

标签： javascriptgoogle-apps-scriptgoogle-sheets

Answer 1

这是脚本：

const HDR = 1 // header height
const GROUP_COL = 1 // in which column is the grouping value

function onEdit(e){      
  // we are interested only if value is not empty and the content of colum GROUP_COL has been changed
  if(!e.value || e.value===e.oldValue || e.range.columnStart!==GROUP_COL || e.range.rowStart<=HDR){
    return
  }

  const sh = SpreadsheetApp.getActiveSheet()
  const values = sh.getDataRange().getValues().slice(HDR-1)
  const existingRows = values.map((r,i)=>({ row:HDR+i, cells:r, found:r[GROUP_COL-1]==e.value })).filter(x=>x.found)
  console.log(existingRows)
  if(existingRows.length===0){
    return
  }

  const destRange = sh.getRange(e.range.rowStart,1,1,existingRows[0].cells.length)
  const destRow = destRange.getValues()[0]

  // merge content into the new row
  existingRows.forEach(er=>{
    er.cells.forEach((c,i)=>{
      if(i===GROUP_COL-1 || er.row===e.range.rowStart){
        return
      }
      destRow[i] = (destRow[i] + "\n" + er.cells[i]).trim()
    })
  })

  destRange.setValues([destRow])

  // delete extra rows
  existingRows.forEach((er,i)=>{
    if(er.row!==e.range.rowStart){
      sh.deleteRow(er.row-i)
    }
  })
}