Mapping Array Of Objects with Unknown Keys or Lengths

Question

I've got some data that I'm trying to output to Excel. My normal functions which work correctly with "normal" data are giving me issues, specifically, I'm seeing undefined for returned arrays in my MappedArrays variable and seem to be losing one row of data.

I tried to simplify the problem to make an MCVE, this may not even be the issue, but it is something I'd like my main function to be able to handle correctly.

Lets say I've got a bunch of Objects with unknown number of keys and not each object will have each key.

I've currently got a function to make sure each Object has each key (this is needed to paste the data in excel, the value can be null).

I have another function to convert the array of objects to array of mapped arrays where all the values should be in position by index.

In my test data, I've got a "Header_E": "Test", line in one Object which is currently being output in what looks to be it's index in the Object, it should be correlated to the Index of the header/column.

Header_A    Header_B    Header_C    Header_D    Header_E    Header_F
2AVal       2BVal               
3AVal       3BVal               
4AVal       4BVal       4DVal           
                        Test

How can I ensure that all objects in an Array of Objects have all keys and then map that ArrOfObjs to an Array with the data indexed accordingly to Object Key

Here is my sample data:

async function CommandsFunc(event) {
    try {
        await Excel.run(async (context) => {
            //Start Func.

            var ws = context.workbook.worksheets.getActiveWorksheet();

            var Obj_One_A = {
                "Header_A": "2AVal",
                "Header_B": "2BVal",
            }

            var Obj_Two_A = {
                "Header_A": "3AVal",
                "Header_B": "3BVal",
            }

            var Obj_One_B = {
                "Header_A": "4AVal",
                "Header_B": "4BVal",
                "Header_D": "4DVal",
            }

            var Obj_Two_B = {
                "Header_A": "",
                "Header_C": "",
                "Header_E": "Test",
                "Header_F": "",
            }

            var Arr_Of_Items = [Obj_One_A, Obj_Two_A]

            //await Do_Arr_Of_Objs_Or_Arrs_To_Rng(context, ws, Arr_Of_Items)

            var HeaderArr = ["Header_A", "Header_B"]
            var MappedArrays = await Get_Mapped_Over_Array_Of_Objects(Arr_Of_Items, HeaderArr)
            MappedArrays.unshift(HeaderArr)

            var rng = ws.getRangeByIndexes(0, 0, MappedArrays.length, MappedArrays[0].length)

            rng.select()
            await context.sync();
            rng.values = MappedArrays
            await context.sync();       

            var Arr_Of_Items = [Obj_One_B, Obj_Two_B]
            var All_Headers_Arr = ["Header_A", "Header_B", "Header_C", "Header_D", "Header_E", "Header_F"]
            var MappedArrays = await Get_Mapped_Over_Array_Of_Objects(Arr_Of_Items, All_Headers_Arr)

            var Used_Rng = ws.getUsedRange(true)
            Used_Rng.load('rowCount')
            await context.sync()
            var rng = ws.getRangeByIndexes(Used_Rng.rowCount, 0, MappedArrays.length, All_Headers_Arr.length)

            //Set Headers
            for (let ai = 0; ai < All_Headers_Arr.length; ai++) {
                ws.getUsedRange(true).getRow(0).getCell(0, ai).values = All_Headers_Arr[ai]
            }

            rng.select()
            await context.sync();
            rng.values = MappedArrays
            await context.sync();

            //await Do_Arr_Of_Objs_Or_Arrs_To_Rng(context, ws, Arr_Of_Items)

            //End Func
            await context.sync();
        });
    } catch (error) {
        console.log(error)        
    }
    try { event.completed() } catch (error) { }
}

Helper Funcs:

async function Get_Mapped_Over_Array_Of_Objects(ArrayOfObjs, MapArr) {
    ArrayOfObjs = await Do_All_Arr_Of_Objs_Same_Keys(ArrayOfObjs, MapArr)
    let MappedArrays = []
    for (let i = 0; i < ArrayOfObjs.length; i++) {
        var obj = ArrayOfObjs[i]
        var arr = Object.keys(obj).map((k) => obj[k])
        MappedArrays.push(arr)
    }
    return MappedArrays;
}

async function Do_All_Arr_Of_Objs_Same_Keys(Arr_Of_Objs, Keys_Arr) {
    for (let oi = 0; oi < Arr_Of_Objs.length; oi++) {
        var obj = Arr_Of_Objs[oi]
        var Obj_Keys = Object.keys(obj)
        if (Keys_Arr.length != Obj_Keys.length) {
            var temp_arr = []
            temp_arr = Keys_Arr
            for (let ki = 0; ki < Obj_Keys.length; ki++) {
                temp_arr = await Do_Remove_Arr_By_Val(temp_arr, Obj_Keys[ki])
            }
            for (let ai = 0; ai < temp_arr.length; ai++) {
                obj[temp_arr[ai]] = ""
            }
        }
    }
    return Arr_Of_Objs
}

function Do_Remove_Arr_By_Val(arr, val) {
    var filteredArray = arr.filter(function (e) { return e !== val })
    return filteredArray
}

Nick · Accepted Answer · 2022-04-23 07:46:24Z

1

Your problem is arising from this line:

var arr = Object.keys(obj).map((k) => obj[k])

This is pushing values into arr in the order the keys are defined in the object, which is not necessarily the same for each object. You'll notice that in the third line of your sample output you have the value for Header_D in the Header_C column, which is a manifestation of the same problem.

Since you know that each object has the same set of keys when you get to this function, you should iterate that constant list of keys (All_Headers_Arr) instead of Object.keys(obj). This will ensure that all values of arr are consistently indexed.

Having said that, I think there is also potential for simplifying your code along the lines of this snippet.

var Obj_One_A = { "Header_A": "2AVal", "Header_B": "2BVal" }

var Obj_Two_A = { "Header_A": "3AVal", "Header_B": "3BVal" }

var Obj_One_B = { "Header_A": "4AVal", "Header_B": "4BVal", "Header_D": "4DVal" }

var Obj_Two_B = { "Header_A": "", "Header_C": "", "Header_E": "Test", "Header_F": "" }

var Arr_Of_Items = [Obj_One_A, Obj_Two_A, Obj_One_B, Obj_Two_B]

// get a list of unique headers
let Headers = new Set()
Arr_Of_Items
  .forEach(o => Object.keys(o)
    .forEach(k => Headers.add(k))
  )

// convert to an array
All_Headers = Array.from(Headers.values()).sort()

// make sure each object has all the headers, assigning null values to any missing headers
const MappedArrays = Arr_Of_Items
  .map(o => All_Headers
    .map(h => o.hasOwnProperty(h) ? o[h] : null)
  )

console.log(MappedArrays)

answered Apr 23, 2022 at 7:46

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

FreeSoftwareServers Over a year ago

Thank you, I love the map function for putting JSON into a table, but I need to understand it more. The great and bad w/ JS is there are functions online for everything that I can piece together. TBH, I struggle to read that code, but the more I use it the more I learn. This helped me figure out my other code straight away, my main issue was selecting the range wrong, but also, this needed to be fixed.

FreeSoftwareServers Over a year ago

I dont mean to say the code isnt written well, I just need to learn how to read the arrow functions and maps syntax. I'm also not yet understanding ?. Anyway, google can get me all that information!

Nick Over a year ago

@FreeSoftwareServers yeah, the arrow function syntax does take a bit of getting used to, and can sometimes make code a little hard to understand but overall I think it's a huge leap forward in writing concise code. As for ?, that's the ternary operator. Anyway, I'm glad this helped you out.

Collectives™ on Stack Overflow

Mapping Array Of Objects with Unknown Keys or Lengths

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related