Google Sheets App Script 匹配Category/Month/Year后更新或新增sumTransaction行求助

你可以替换原有tableToObject函数中// merge or add row注释后的缺失逻辑，完整可运行代码如下：

function tableToObject() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const transactionSheet = ss.getSheetByName('Transactions')
  const lastRow = transactionSheet.getLastRow()
  const lastColumn = transactionSheet.getLastColumn()
  const values = transactionSheet.getRange(1, 1, lastRow, lastColumn).getValues()
  const [headers, ...originalData] = values.map(([,b,,d,e,,,,,,,,,,,p,q,r,s]) => [b,d,e,p,q,r,s])
  const res = originalData.map(r => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {}))
  // GroupBy and Sum
  const transactionGroup = [...res.reduce((r, o) => {
    const key = o.Category + '_' + o.Month + '_' + o.Year
    const item = r.get(key) || Object.assign({}, o, {
      Amount: 0,
    })
    item.Amount += o.Amount
    item.Key = key
    return r.set(key, item)
  }, new Map).values()]
  const sumSheet = ss.getSheetByName('sumTransaction') // 注意修正原表名拼写错误
  const sumLastRow = sumSheet.getLastRow()
  // 构建sum表现有数据的映射：key为三个字段拼接值，value为{rowIndex: 行号, currentAmount: 现有金额}
  const sumExistingMap = new Map()
  if(sumLastRow > 1) { // 排除表头行
    const sumValues = sumSheet.getRange(2, 1, sumLastRow - 1, 6).getValues()
    sumValues.forEach((row, idx) => {
      const [category, month, year, , amount] = row
      const key = `${category}_${month}_${year}`
      sumExistingMap.set(key, {
        rowIndex: idx + 2, // 行号从2开始算，因为跳过表头
        currentAmount: amount
      })
    })
  }
  const toAppendRows = []
  const sumHeader = ['Category', 'Month', 'Year', 'Group', 'Amount', 'Debit/Credit']
  // 遍历分组后的交易数据，更新或插入
  transactionGroup.forEach(item => {
    const key = item.Key
    if(sumExistingMap.has(key)) {
      // 匹配成功，更新金额
      const {rowIndex, currentAmount} = sumExistingMap.get(key)
      sumSheet.getRange(rowIndex, 5).setValue(currentAmount + item.Amount)
    } else {
      // 匹配失败，收集待插入行
      const row = sumHeader.map(field => {
        if(field === 'Debit/Credit') return item.Debit || ''
        return item[field]
      })
      toAppendRows.push(row)
    }
  })
  // 批量插入新行
  if(toAppendRows.length > 0) {
    sumSheet.getRange(sumLastRow + 1, 1, toAppendRows.length, toAppendRows[0].length).setValues(toAppendRows)
  }
}
function getBudget(){
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const sumSheet = ss.getSheetByName('sumTransaction') // 修正表名拼写错误
  const lastRow = sumSheet.getLastRow()
  const lastColumn = sumSheet.getLastColumn()
  const values = sumSheet.getRange(1, 1, lastRow, lastColumn).getValues()
  const [headers, ...originalData] = values.map(([a,b,c,d,e,f]) => [a,b,c,d,e,f])
  const res = originalData.map(r => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {}))
  return res
}

• 原代码中sumTransacation表名拼写错误，已修正为sumTransaction，请确保你的工作表名称和代码中保持一致
• 脚本默认sumTransaction表的列顺序为：Category、Month、Year、Group、Amount、Debit/Credit，和你提供的样例结构完全匹配，如果列序有改动需要同步调整代码中对应的字段位置
• 采用批量插入新行的写法，相比逐行插入性能更好，适合数据量较大的场景

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