Tuesday, July 16, 2013

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:
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,

1 comment:

  1. This is fantastic!! Thank you so much for your help. At a later time, if we wanted to add the student's grade level next to their name, how would you suggest that we change the script to reflect this.

    Sheet 2 would then read:
    Phone--Mother's Name--Father's Name--Student's Grade--Student's Name

    Thank you again for your help and any further direction would be greatly appreciated.

    ReplyDelete