Delete Rows from 2D Array Based on Matches Javascript Apps Script

Question

The below arrays are just examples of how I need it to function. I've created a for loop to append elements to a matching row, and now I'm trying to work out removing the previous row.

example array:

[sally, yes, brown, 5],
[david, yes, blue, 8],
[sally, yes, green, 3],
[sally, no, yellow, 7],
[david, no, green, 3],
[ben, no, purple, 12]

desired output:

[sally, yes, brown, 5, yes, green, 3, no, yellow, 7],
[david, yes, blue, 8, no, green, 3],
[ben, no, purple, 12]

So, what I want the code to do is this:

1. Checking if an element appears in the first column more than once, but doesn't have the same value from column 3.
2. When the above is true, copy the 2nd, 3rd, and 4th elements from that row(j) to the row where the element first appeared.
3. Remove the other rows where the element appears.

My current code for this loop is below. Not sure why it's wrong, but it's not getting the above result.

for (i = 0; i <data.length; i++){
  for (j=0; j<data.length; j++){

    var count = 0;
    if(data[i][0] == data[j][0]){

    count++;

    if (data [i][2] != data[j][2]){

      data[i].push(data[j][1], data[j][2], data[j][3], data[j][4])
      
      if (count > 1){

        data.splice(i-1 ,1);

      }
      }
      }
    }
    }
  

I might be going about this the wrong way, so if there is another method to solve it that you suggest instead of this for loop, that would be greatly helpful. Would it work better/ be easier to compare two separate arrays instead of comparing the array to itself?

Additionally, after solving this portion of the code, I will need to add empty elements to all rows that are shorter than the longest. In the case of the above example dummy data, the longest is 10 columns long, so I would need to add empty spaces in varying amounts to any row shorter than that. The reason is that I am pasting it to a spreadsheet afterwards but can't do that with .setValues method unless the length is the same for every row. If anyone could help with that as well, that would be appreciated also.

Answer 1

I believe your goal as follows.

• You want to achieve the following conversion.

  • From

      [
        ["sally", "yes", "brown", 5],
        ["david", "yes", "blue", 8],
        ["sally", "yes", "green", 3],
        ["sally", "no", "yellow", 7],
        ["david", "no", "green", 3],
        ["ben", "no", "purple", 12]
      ]
    

  • To

      [
        ["sally","yes","brown",5,"yes","green",3,"no","yellow",7],
        ["david","yes","blue",8,"no","green",3],
        ["ben","no","purple",12]
      ]
    

  • And, you want to retrieve the following value as the final value for putting to Spreadsheet using setValues.

      [
        ["sally","yes","brown",5,"yes","green",3,"no","yellow",7],
        ["david","yes","blue",8,"no","green",3,"","",""],
        ["ben","no","purple",12,"","","","","",""]
      ]
    

Sample script:

In this sample script, in order to obtain the 1st result array in your question, Map object is used. And, using the obtained array, the final value for putting to Spreadsheet with serValues is created.

const data = [
  ["sally", "yes", "brown", 5],
  ["david", "yes", "blue", 8],
  ["sally", "yes", "green", 3],
  ["sally", "no", "yellow", 7],
  ["david", "no", "green", 3],
  ["ben", "no", "purple", 12]
];
const ar = [...data.reduce((m, [a, ...b]) => m.set(a, m.has(a) ? m.get(a).concat(b) : b), new Map())].map(([a, b]) => [a, ...b]);
console.log(JSON.stringify(ar));

const maxLength = Math.max(...ar.map(e => e.length));
const res = ar.map(e => e.length < maxLength ? e.concat(Array(maxLength - e.length).fill("")) : e);
console.log(JSON.stringify(res));

• In this sample script,
  • ar is the array you want in your question.
  • res is the final value you want to use with setValues.

References:

• Map
• reduce()
• map()

Answer 2

function myfunc101() {
  const data = [['sally', 'yes', 'brown', 5], ['david', 'yes', 'blue', 8], ['sally', 'yes', 'green', 3], ['sally', 'no', 'yellow', 7], ['david', 'no', 'green', 3], ['ben', 'no', 'purple', 12]];
  let oA = [];
  let oObj = { pA: [] };
  data.forEach((R, i) => {
    if (!oObj.hasOwnProperty(R[0])) {
      oObj[R[0]] = [];//create new property array
      oObj[R[0]].push(R);//push entire row in it
      oObj.pA.push(R[0]);//add property to property list
    } else {
      let found = false;//looking to see if colors match any of the current colors in the property array
      oObj[R[0]].forEach(r => {
        if (r[2] == R[2]) {
          found = true;
        }
      });
      if (!found) {
        oObj[R[0]].push(R);//if colors don't match then add it to the property Array
      }
    }
  });
  //creating output array from oObj
  oObj.pA.forEach((p, i) => {//loop through each property in the list
    let row=[];
    oObj[p].forEach((r, j) => {//loop through property array
      if (j == 0) {
        row.push(...r);//add the entire first row to row
      } else {
        row = row.concat(r.slice(1));//add the last 3 elements for the remaining items on the property array
        //row.push(...r.slice(1));//this works too
      }
    });
    oA.push(row);//push results in output array
  });
  Logger.log(JSON.stringify(oA));
  return oA;
}

Execution log
7:21:03 PM  Notice  Execution started
7:21:05 PM  Info    [["sally","yes","brown",5,"yes","green",3,"no","yellow",7],["david","yes","blue",8,"no","green",3],["ben","no","purple",12]]
7:21:04 PM  Notice  Execution completed