Merging two 2D arrays/datasets/tables with Google Apps Script

 

Ever needed to merge two similar datasets and automatically get rid of any duplicates? This article describes how to do that using the SsObjects Google Apps Script library.

And if you are just looking for a solution without taking a deep dive into the code:

• take a copy of Merge two 2D Arrays using SsObjects – Demo ,
• paste in your own data to Table1 and Table2,
• use the custom menu “Merge > Merge Table1 and Table2” to merge them into “Merged Tables”.

The way the SsObjects works is to get both datasets/tables as arrays, add them to the same object, then convert that object back to a single 2D array. At which time you can do what is necessary with it: further processing, or more typically writing back to a Google Spreadsheet.

Take for example the following table:

const table1 = [
  [email,             name     age],
// -----              ----     ---
  ["joe@email.com",   "Joe",   24],
  ["sarah@email.com", "Sarah", 28],
  ["paul@email.com",  "Paul",  18]
];

If “email” is specified as the ID for each key/value pair when SsObject is called with an empty object:

let object = {};
SsObjects.addArrayToObject({
  id:'email', 
  data: table1, 
  objects:object
});

the value of object is now:

{
  "joe@email.com":   {"name": "Joe",   "age": 24},
  "sarah@email.com": {"name": "Sarah", "age": 28},
  "paul@email.com":  {"name": "Paul",  "age": 18}
}

If we now wanted to merge another table with similar column headers, some duplicate data, but also some different values:

const table2 = [
  [email,             name,    age],
// -----              ----     ---
  ["sam@email.com",   "Sam",   30],
  ["sarah@email.com", "Sarah", 28],
  ["lisa@email.com",  "Lisa",  37]
];

Adding this to our object with SsObjects:

let object = {};
SsObjects.addArrayToObject({
  id:'email', 
  data: table2, 
  objects:object
});

sets object‘s value to:

{
  "joe@email.com":   {"name": "Joe",   "age": 24}
  "sarah@email.com": {"name": "Sarah", "age": 28}
  "paul@email.com":  {"name": "Paul",  "age": 18}
  "sam@email.com":   {"name": "Sam",   "age": 30}
  "lisa@email.com":  {"name": "Lisa",  "age": 37}
}

object can now be converted back to an array:

let mergedArray = ["email", "name", "age"];
SsObjects.addObjectsToArray({
  id:'email', 
  data: mergedArray, 
  objects:object
})

Giving a mergedTable of:

const mergedTable = [
  [email,             name,    age],
// -----              ----     ---
  ["joe@email.com",   "Joe",   24],
  ["sarah@email.com", "Sarah", 28],
  ["paul@email.com",  "Paul",  18],
  ["sam@email.com",   "Sam",   30],
  ["lisa@email.com",  "Lisa",  37]
];

Further examples can be found bound to the SsObject Test Sheet.

Full Code

const mergeTables = () => {  
  const table1 = [
    [email,             name     age],
  // -----              ----     ---
    ["joe@email.com",   "Joe",   24],
    ["sarah@email.com", "Sarah", 28],
    ["paul@email.com",  "Paul",  18]
  ];
  const table2 = [
    [email,             name,    age],
  // -----              ----     ---
    ["sam@email.com",   "Sam",   30],
    ["sarah@email.com", "Sarah", 28],
    ["lisa@email.com",  "Lisa",  37]
  ];
  let object = {}; 
  SsObjects.addArrayToObject({
    id:'email', 
    data: table1, 
    objects:object
  }); 
  SsObjects.addArrayToObject({
    id:'email', 
    data: table2, 
    objects:object
  }); 
  let mergedTable = ["email", "name", "age"]; 
  SsObjects.addObjectsToArray({
    id:'email', 
    data: mergedTable, 
    objects:object
  })
}

Merging GSheet Template

You can see a real example in this GSheet – Merge two 2D Arrays using SsObjects – Demo.

If you take a copy and paste in your own data it can be used as a merge tool for yourself.