Google Spreadsheet Script to group by first three columns and transposing remaining columns

Question:

( by Lancertech SJDLSchool )

Image 1

Image 2

So here is the situation and I would greatly appreciate any assistance.  We use Forms to register families into our data base.  Parents sign their children up submitting a form for each child.  This produces a spreadsheet with multiple lines of the same family.  We would like to bring the data together so that for each parent their children appear in individual columns.  In other words, is there a formula that I can use to have the data from image 1 above appear in a spreadsheet like image 2.  As you can see rows 2 and 3 in image 1 contain the same family, but the difference is the child's name.

Hopes this makes at least a little sense.  Thank you for any help you can give!

Solution:

Have a look at the following screenshot of Sheet1:

Have a look at the following screenshot of Sheet2:

I have the following formula in Cell A2 of Sheet2:

=kishan('Sheet1'!A2:D)

the above formula is a custom function that I have written and inserted in Script.


Have a look at the following code:

///////////////////////////////////////

function kishan()
{
  if (arguments.length < 1)
    return "#NA Please input range to sort";
  else if (arguments.length > 1)
    return "#NA Input Can't be more than one arguement";
  
  var values = arguments[0];
  var uniquevalues = get3Cols(values).getUnique();
  
  for(var i=0;i<values.length-1;i++)
  {    
    for(var j=0;j<uniquevalues.length-1;j++)
    {
      if( values[i][0] == uniquevalues[j][0] 
         && values[i][1] == uniquevalues[j][1] 
         && values[i][2] == uniquevalues[j][2] )
      {
        uniquevalues[j].push(values[i][3]);
      }
    }    
  }
  
  return uniquevalues;
}

Array.prototype.getUnique = function(){
  var u = {}, a = [];
  for(var i = 0, l = this.length; i < l; ++i){
    if(u.hasOwnProperty(this[i])) {
      continue;
    }
    a.push(this[i]);
    u[this[i]] = 1;
  }
  return a;
}

function get3Cols(matrix)
{
  var columns = [];
  for(var i=0; i<matrix.length; i++){
    columns.push( [ matrix[i][0] , matrix[i][1] , matrix[i][2] ] );
  }
  return columns;
}

///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet or you can use its functionality in the Google Apps Script.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive