Google App Script Key Value Pair by Header-CodePudding

I am trying to create key value pairs using the header column and although I'm able to get the values my forEach is grouping all values by date or description as opposed to each item in each row show in ideal out put.

There might be a much easier way of doing this than my approach so open to suggestions if there is tried and true approach for app script, which I'm just learning here.

function tableToObject () {
const ss = SpreadsheetApp.getActiveSpreadsheet()
const transactionSheet = ss.getSheetByName('Transactions')

const lastRow = transactionSheet.getLastRow()
const lastColumn = transactionSheet.getLastColumn()

const originalData = transactionSheet.getRange(1, 1, lastRow - 1,lastColumn).getValues()

Logger.log(originalData)

const headers = originalData[0] // Extract headers
Logger.log(headers)

const body = originalData.slice(1)

const keys = {}
let i = 0

headers.forEach(function (c) {
keys[c] = [] 
body.map(r => keys[c].push(r[i]))
i  })
Logger.log(keys)

}

Ideal Output

[{
  "date": "1/4/2019",
  "category": "Source A",
  "amount": "100",
  "month": "January",
  "year": "2019",
  "group": "COGS",
  "debit/credit": "Credit",
},  {
      "date": "1/4/2019",
      "category": "Source A",
      "amount": "100",
      "month": "January",
      "year": "2019",
      "group": "COGS",
      "debit/credit": "Credit",
    }
]

CodePudding user response：

Modification points:

About const originalData = transactionSheet.getRange(1, 1, lastRow - 1,lastColumn).getValues(), in this case, the last row is not retrieved.
In your forEach, each column value is put in an array. In this case, from your expected output value, I thought that each column value is required to be the object with the header key and the value.

When these points are reflected to your script, it becomes as follows.

Modified script:

function tableToObject() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const transactionSheet = ss.getSheetByName('Transactions');
  const lastRow = transactionSheet.getLastRow();
  const lastColumn = transactionSheet.getLastColumn();
  const [headers, ...originalData] = transactionSheet.getRange(1, 1, lastRow, lastColumn).getValues();
  const res = originalData.map(r => headers.reduce((o, h, j) => Object.assign(o, {[h]: r[j]}), {}));
  console.log(res)
}

If you don't want to retrieve the last row, please modify lastRow to lastRow - 1.

Modification points:

Modified script:

References: