Mapping Array Of Objects with Unknown Keys or Lengths-CodePudding

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
}

CodePudding user response：

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)