Build Google App Script to Combine Similar Rows in

Question

Many people are asking why I want to do this. I want to do this so that when I do my mail merge (this sends students their overdue book lists from the library), I don't send a student an email more than once. I never use this data more than once, I only use it to send a quick message, I never manipulate or work with the data so I don't care if it's hard to work with! I hope this makes sense! Thank you for your feedback thus far.

1. Google Sheet starts like this:

2. I want it to look like this:

3. I have started some script, which I am sure you will all laugh at (I know very little of programming). However, It'd be awesome to be able to do this. Basically, combine the rows that have an identical entry in column 1, by putting the values for the columns for those rows together into one row. The numbers can be added, and the data separated by a comma or a line break.

4. This is what I have so far....

   function myFunction() {
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getActiveSheet();
       var last = sheet.getLastRow();
   
       //find identical entries in column 1//
   
       for(i in data){
           var row = data[i];
           var duplicate = false;
           for(j in newData){
               if(row.join() == newData[j].join()){
                   duplicate = true;
               }
           }
           if(!duplicate){
               newData.push(row);
           }
       }
   
       //add information from rows of identical entries into one row using a comma//
   
       //delete empty rows// 
   }
   

Answer 1

It's a bit of a kluge but it runs with my fake data. Hopefully you can get it running with yours. It usually takes me some tweaking to get something like this a little more streamlined. I have a few comments in there to explain some of the key items.

  function rowMerge() {
  var firstRow = 2;
  var firstCol = 1;
  var sht = SpreadsheetApp.getActiveSheet();
  sht.getRange(firstRow, firstCol, sht.getLastRow() - firstRow + 1, sht.getLastColumn() - firstCol + 1).sort(1);
  sht.appendRow(['***','***','Control-z one more time','***','***']); //need a throwaway row to get last data element out since I'm moving out element[i-1]
  var datR = sht.getDataRange();
  var lastRow = datR.getLastRow();
  var lastCol = datR.getLastColumn();
  var datA = datR.getValues();
  sht.getRange(2,1,lastRow - firstRow + 1,lastCol - firstCol + 1).clearContent().setWrap(true);
  var datoutA = [];
  var k=1;
  var n = 0;
  for(var i = 0;i < datA.length; i++)
  {
    if(i > 1)
    {
      if(datA[i][0] == datA[i-1][0])
      {
        k++;  //k is the number of consecutive matching values
      }
      else
      {
        datoutA[n] = [];
        if(k == 1)
        {
          // if k = 1 the datA[i-1] row gets copied into output array
          for(var c = 0;c < datA[i-1].length; c++)
          {
            datoutA[n][c]=datA[i-1][c];
          }
        }
        else
        {
          //i-1 to i-k rows get merged and copied into output array
          var firstTime = true;
          for(var a = 1;a <= k;a++)//input rows
          {            
            for(var b = 0;b < datA[i].length -1;b++)//input columns
            {
                if(a > 1 || b > 0) //no delimiter for first row or first column 
                {
                  datoutA[n][b] += ', ';
                }
               if(firstTime || b == 0)// straight assignment for first row and running sum after that same with first column because we only want one of them because they're all the same.
               {
                 datoutA[n][b] = datA[i - a][b];
               }
               else
               {
                 datoutA[n][b] += datA[i - a][b];
               }
            }
            if(firstTime)//first assignment then running sums for last column
            {
              datoutA[n][datA[i].length - 1] = Number(datA[i - a][datA[i].length-1]);
            }
            else
            {
              datoutA[n][datA[i].length - 1] += Number(datA[i - a][datA[i].length-1]);
            }

            firstTime=false;
          }
          var end = 'is near';
        }
        k=1; //consecutive counter
        n++; //datoutA index
      }

    }

  } 
  var datoutR = sht.getRange(2, 1, datoutA.length , datoutA[0].length);
  datoutR.setValues(datoutA);
  var colwidth = 250;
  sht.setColumnWidth(2, colwidth);
  sht.setColumnWidth(3, colwidth);
  sht.setColumnWidth(4, colwidth);
}

Answer 2

There actually are many reasons why one would want to combine similar rows.

I for one, have a form that allows users to fill out information about properties. New info comes in everyday, so the forms will be reused for new entries.

All properties have a unique identifier, but the first form entry is useful to give the owner's name and address, along with other information.

The next form entry does not need to populate, (and re-type) all that info, but it does need to add other new details to the first entry.

Lastly, I want to report based on unique entries, with other info combined to read with the property.

Part of the answer for me is a custom function created by Hyde, called JoinRows.

Search for that.

Good luck!