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
}
