I'm working w/ Excel Tables in JS and trying to find a way to set/manipulate the column order before pasting data into table vs after. There is a line w/ .map
that seems to be key, but I can't seem to use/pass variables inside it.
How can I access HeaderRowArr[1]
inside the map
? In the end, I'll want to iterate over the entire HeaderRowArr
and map each item, but I feel like if I can get this first part done, that should hopefully be clearer.
This line is the line I'm working on:
var newData = transactions.map(item => [item.MERCHANT, item.HeaderRowArr[1], item.CATEGORY, item.AMOUNT]);
But I get error: "Unable to get property '1' of undefined or null reference"
Full Snippet:
export async function helloworld(event) {
try {
await Excel.run(async (context) => {
//Start Func
var transactions = [
{
"BoxLabel": "AB12345678R001-005",
"MERCHANT": "The Phone Company",
"CATEGORY": "Communications",
"AMOUNT": "120"
},
{
"BoxLabel": "ZY12345678R001-005",
"MERCHANT": "Southridge Video",
"CATEGORY": "Entertainment",
"AMOUNT": "40"
}
];
var WSNameStr = "Workorder_Samples";
var ws = await Get_WorkSheet(context, false, WSNameStr)
var TBLNameStr = "ExpensesTable";
var rng = ws.getRangeByIndexes(0, 0, 1, Object.keys(transactions[0]).length)
var expensesTable = await Get_Table(context, false, ws, TBLNameStr, rng)
//expensesTable.getHeaderRowRange().values = [Object.keys(transactions[0])];
var HeaderRowArr = ["MERCHANT", "BoxLabel", "CATEGORY", "AMOUNT"]
expensesTable.getHeaderRowRange().values = [HeaderRowArr];
//var newData = transactions.map(item => [item.CATEGORY, item.BoxLabel, item.MERCHANT, item.AMOUNT]); //This controls body output order, doesn't track headers order
console.log(HeaderRowArr[1])
var newData = transactions.map(item => [item.MERCHANT, item.BoxLabel, item.CATEGORY, item.AMOUNT]);
//var newData = transactions.map(item => [item.MERCHANT, item.HeaderRowArr[1], item.CATEGORY, item.AMOUNT]);
expensesTable.rows.add(null, newData);
ws.activate();
//End Func
await context.sync();
});
} catch (error) {
console.error(error);
}
event.completed();
}