I have a two dimensional array which elements needs to be combined if two of the elements of the sub arrays are equal.

Example: I have this data:

I need this data:

The data is represented this way for example:

var event1 = [name, startDate1, endDate1];
var event2 = [name, startDate2, endDate2];
var event3 = [name, startDate1, endDate2];
var event4 = [name, startDate1, endDate1];
var event5 = [name, startDate1, endDate1];
var event6 = [name, startDate3, endDate2];
var allEvents = [event1, event2, event3, event4, event5, event6 ]

And I need to combine the elements with concatenated names if both start and end dates are equal.

Any help is much appreciated.

CodePudding user response：

I'll leave it up to you where to put the results but try this:

function test() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("Test");
    var unique = [];
    var data = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues();
    unique.push(data[1]);
    var append = true;
    for( var i=2; i<data.length; i   ) {
      append = true;
      for( var j=0; j<unique.length; j   ) {
        if( ( data[i][1] === unique[j][1] ) && ( data[i][2] === unique[j][2] ) ) {
          unique[j][0] = unique[j][0] ", " data[i][0];
          append = false;
          break;
        }
      }
      if( append ) unique.push(data[i]);
    }
    console.log(unique);
  }
  catch(err) {
    console.log(err);
  }
}

consol.log:

8:15:15 AM  Info    [ [ 'First, Fourth, Fifth', 'startDate1', 'endDate1' ],
  [ 'Second', 'startDate2', 'endDate2' ],
  [ 'Third', 'startDate1', 'endDate2' ],
  [ 'Sixth', 'startDate3', 'endDate2' ] ]