0

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();
}
FreeSoftwareServers
  • 2,271
  • 1
  • 33
  • 57
  • 1
    The objects in `transactions` don’t have a `HeaderRowArr` property. Did you mean `item[HeaderRowArr[1]]`? – Sebastian Simon Jan 23 '22 at 21:17
  • Literally 1 character fix, thanks @SebastianSimon , I realize its a dup, but I wish you could answer regardless, don't see the value in blocking. – FreeSoftwareServers Jan 23 '22 at 21:24
  • This also worked and looks cleaner, but lots to learn.. `item[HeaderRowArr[1]]` – FreeSoftwareServers Jan 23 '22 at 21:35
  • Why should this be reopened? Does the linked question not answer your question? Doesn’t it include everything you need to know? – Sebastian Simon Jan 23 '22 at 21:41
  • @SebastianSimon I didn't say reopen, but more I meant that it wouldn't hurt to have a specific answer and then link to the more generic question which was really describes the answer. But, as it's closed, we can't get the answer. Often Q's are answered and then marked duplicate and the answer on the original thread is helpful on its own. – FreeSoftwareServers Jan 23 '22 at 21:46

0 Answers0