Script Group By Custom Function

Question:

( by rajvivek )

Thank u for your input!

I really appreciate your help in creating the script. However, before i use this solution at my original data, here are a couple of issues :

1) This is totally my fault but in order to simplify the formula, i have taken the liberty to take some part away from the formula (with the intention that i would add that part later myself).

So what I wrote the formula above as :  =transpose(filter(A$2:A$100, B$2:B$100=D2, C$2:C$100=E2)) actually originally is =transpose(sort(unique(filter(A$2:A$100, B$2:B$100=D2, C$2:C$100=E2))

And since i do not know how to deal with script, i do not know how to add these two parameters in the script you suggested.

2) The second reason is personal; i prefer formulas rather than script simply because scripts seem to me more complex than formulas. Formula gives me scope to understand them, and use them in many varied way, but with script i just get stuck. But that is maybe just me.

I have read many posts here at google docs (see one reference below which several other references) and made an impression that such result could be achieved through the help of MMULT, SIGN and other . In fact, i tried myself many ways but so far got no luck. Anyway, if it could not be achieved through formula than i would go for the script as u suggested.

Thanks!

Solution:

Have a look at the following screenshot:

I have the following formula in Cell G2:

=k(A2:C)

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

Have a look at the following code:

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

function k() {
  if(arguments.length < 1 || arguments.length > 1)
    return "Please input only 1 argument";
  var data = arguments[0].sort(mySortFunction);
  var k=0,flag=false,returnArray=new Array();
  
  for(var i=0;i<data.length;i++) {
    for(var j=0;j<returnArray.length;j++) {
      if( data[i][1]==returnArray[j][0] && data[i][2]==returnArray[j][1]) {
        var tempflag = true;
        for(var x=2;x<returnArray[j].length;x++)
          if(returnArray[j][x]==data[i][0])
            tempflag=false;
        if(tempflag) {
          returnArray[j].push([data[i][0]]);
          returnArray[j][2]++;
        }
        flag=true;
      }
    }
    if(data[i][0]!="" && flag==false) {
      returnArray[k] = new Array();
      returnArray[k].push( data[i][1],data[i][2],1,data[i][0] );
      k++;
    }
    flag=false;
  }
  return returnArray.sort(mySortFunction);
}

var mySortFunction = function(a,b) {
  try{x=a[0].toLowerCase();
      y=b[0].toLowerCase();}
  catch(e){x=a[0];y=b[0];}
  return (x>y)?1:(x<y)?-1:0
};

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

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