I want to convert an array of nested objects into a 2d array that I want to use as part of an apps script batchUpdate in Google Sheets.

I am using an object array as input, in apps script as below:

    [{a:"val1", b:{x:"val2",y:"val3",z:"val4"}, c:{s:"val5",t:"val6",r:"val7"},
    {a:"val8", b:{x:"val9",z:"val10"},c:{t:"val11",r:"val12"},
    {a:"val13",b:{y:"val14"}, c:{s:"val15",t:"val16",z:"val17"}]

so not all nested objects have all keys.

Since the number of objects is quite big, I am looking for an efficient way to process and create a 2D array where the first column holds the unique keys, un-nested, and the sub-keys grouped together, while each further column holds the values of each object (where there is a key-value pair). Like the table below:

I am seeking to avoid nested for-loops to populate the array as it would be inefficient.

Any ideas on how to do that fast and concisely?

TIA

CodePudding user response：

If I understand correctly, you have an array of objects.. each object is a column and its properties are expected to have an expanded id (when flattened) as the composition of parent ids till root element.

So that the resulting object is a mapping of full expanded ids and their list of values as an array with slots position mapped to the corresponding column.

It's easier to show than to explain...

so here's there's the fillBag function that will iterate over the passed object properties (recursively) while filling the passed bag object that will hold the final result:

//this function is fully commented in the live snippet
function fillBag(prefix, colIndex, obj, bag){ 
    Object.entries(obj).forEach(
    ([key, value])=>{              
        const index = `${prefix}${key}`;              
        if(typeof(value) === 'string' || value instanceof String){                
            const values = (index in bag) ? bag[index] : bag[index] = [];
            values.length = colIndex;
            values.push(value);                                
        }else{        
            fillBag(index, colIndex, value, bag);
        }
    });
}

I used your same input array but I had to make some corrections because you had some syntax errors missing closing }.

This is the final object returned:

{
  "a": [ "val1", "val8", "val13" ],
  "bx": [ "val2", "val9", undefined],
  "by": [ "val3", undefined, "val14" ],
  "bz": [ "val4", "val10", undefined ]
  "cs": [ "val5", undefined, "val15" ],
  "ct": [ "val6", "val11", "val16" ],
  "cr": [ "val7", "val12", undefined ],
  "cz": [ undefined, undefined, "val17" ]
}

if you needed an array of arrays instead of an object mapping unique keys to array values, you can also use Object.entries on top of that:

Object.entries($bag) //...[ key, [values] ]

Warning it now takes into account the fact that later columns may stack up and if a given index doesn't show up anymore, the array of values for that index wwould not contain undefined values for the indexes following the last column where the property name did its last appearance. To correct that behaviour, the resulting bag get visited one last time after the whole object was processed, and ALL the arrays resized with the size of the biggest one (number of total columns).

function fillTrailingEmptyColumns(obj, cols){
  Object.values(obj).forEach( values =>{
    values.length = cols;
  });  
}

Sparse arrays and setting .length property

It's worth to say that since the values are stored in an array for each key, the array will contain undefined values for columns in which the given index didn't have any value.

It could be interesting to further read about sparse arrays and empty slot.

Most people will find very surprising that you can actually set the Array.length property

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Indexed_collections#sparse_arrays

const data = [
  {
    a:"val1",
    b: {
      x:"val2",
      y:"val3",
      z:"val4"
    },
    c: {
      s:"val5",
      t:"val6",
      r:"val7"
    }
  },
  {
    a:"val8",
    b:{
      x:"val9",
      z:"val10"
    },
    c:{
      t:"val11",
      r:"val12"
    }
  },
  {    
    a:"val13",
    b:{
      y:"val14"
    },
    c:{
      s:"val15",
      t:"val16",
      z:"val17"
    }
  }
]

const bag = {};

//fills the bag with key->values
data.forEach( (obj, i) => fillBag('', i, obj, bag));

//this is an information I could grab from data.length as well
const cols = findMaxColumns(bag);

//fills the trailing missing columns in bag for each key
fillTrailingEmptyColumns(bag, cols);

console.log(bag);

//returns the maximum number of columns descrived in the obj
function findMaxColumns(obj){
  return Object.values(obj).reduce(
    (max, value)=>{
      if(value.length > max)
        return value.length;
      else
        return max;        
    },
    0
  );
}

function fillTrailingEmptyColumns(obj, cols){
  Object.values(obj).forEach( values =>{
    values.length = cols;
  });  
}

//fills the bag object with values coming from obj at the given prefix and colIndex
function fillBag(prefix, colIndex, obj, bag){

  //for each entry in obj (an entry is key-value property pair)
  Object.entries(obj).forEach(
    ([key, value])=>{      
      //current index based on prefix
      const index = `${prefix}${key}`;
      
      //if the current property value is a string
      if(typeof(value) === 'string' || value instanceof String){        
        //if composed key exists in bag
        if(index in bag){          
          let values = bag[index];      
          //change the size of values in the array based on the current column
          values.length = colIndex;
          //push the current value in the array of values for this key
          values.push(value);
        }
        //else if composed key doesn't exist yet in bag
        else{
          //create a new array filled with nulls until now          
          let values = [];
          //change the size of values in the array based on the current column
          values.length = colIndex;
          //push the current value in the array of values for this key
          values.push(value);
          //sets the key in the bag for its first time
          bag[index] = values;
        }
      }
      //else if the obj is not a string (thus a nested properties bag)
      else{
        //recursively call fillBag with the current composed index and passing the current value
        fillBag(index, colIndex, value, bag);
      }
    });
    
}