sorting columns independently at array level

Question

I have a spreadsheet with several columns that I need to sort individually. I wrote the script below that works but is a bit slow since it handles each column in turn with getValues() and setValues(). I'd like to find a way to do the whole sorting at an array level for more efficiency but I don't know how... any suggestion ?

Here is the relevant part of the code I use now :

...
  sh3.getRange(1,1,1,newData[0].length).setFontWeight('bold');// newData is an array corresponding to the whole sheet
  for(col=1;col<newData[0].length;++col){
    var val = sh3.getRange(2,col,getLastRowInCol(col),1).getValues().sort();// each column have a different height
    sh3.getRange(2,col,getLastRowInCol(col),1).setValues(val)
    }
}

function getLastRowInCol(col){
  var values = sh3.getRange(2,col,sh3.getLastRow(),1).getValues();// skip header and find last non empty row in column
  for(n=0;n<values.length;++n){
    if(values[n]==''){break}
  }
  return n
}

Note : I know there is a Library by Romain Vialard that does the job (sorting columns in 2D arrays) but I'm interrested on how to do it 'manually' for personal JS skills improvement ;-) and also I need to sort every column independently without needing to update the sheet for every column.

Re: function getLastRowInCol(col) -- will return the first cell that precedes a blank cell. If there is a blank in the list it will not continue beyond. Better to start at the bottom and work your way up looking for the first cell with data. — ScampMichael
– ScampMichael, Commented Jan 12, 2013 at 16:41
will return the first cell that precedes a blank cell. I know but since I start on row 2 that happend to be good ;-) and your right about startin from the end but I have a lot more 'short columns than 'long one' . — Serge insas
– Serge insas, Commented Jan 12, 2013 at 18:08

ScampMichael · Accepted Answer · 2013-01-12 19:39:38Z

1

How about:

function sortColumns() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var startCol = 1;
  var dataRange = sheet.getRange(startRow, startCol, sheet.getLastRow() - startRow + 1, sheet.getLastColumn() - startCol + 1);
  var data = dataRange.getValues();

  // transpose data so each column item will be listed in an single array
  // for each column so that it can be sorted with array.sort()
  var rowToCol = [];
  for (var i = 0; i < data[0].length; i++) {
    rowToCol.push([]);
    for (var j = 0; j < data.length; j++) {
      // replace empty string with undefined as undefined sorts last
      rowToCol[i].push(data[j][i]==""?undefined:data[j][i]);
    }
    rowToCol[i].sort();
    // default sort, as above, is alphabetic ascending. For other methods
    // search for Javascript array sort functions
  }

  // transpose sorted items back to their original shape
  var result = [];
  for (var i = 0; i < rowToCol[0].length; i++) {
    result.push([]);
    for (var j = 0; j < rowToCol.length; j++) {
      result[i].push(rowToCol[j][i]==undefined?"":rowToCol[j][i]);
    }
  }
  dataRange.setValues(result);
};


function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Sort Columns",
    functionName : "sortColumns"
  }];
  sheet.addMenu("Script Center Menu", entries);
};

edited Jan 12, 2013 at 19:39

answered Jan 12, 2013 at 16:29

ScampMichael

3,7382 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Bryan P Over a year ago

You may want this to handle 0 values too.

ScampMichael Over a year ago

+BryanP You are correct. This can only be used for simple alpha sort with out modifying the sort with a function. To sort numerically or case insensitive there are array sort functions online that can be found to augment the above.

Serge insas Over a year ago

Thx Michael, while trying I didn't think about adding the sort where you put it... your code is cristal clear ;-) thanks

Serge insas Over a year ago

Performance increase : about 20 to 1 ... not bad @ScampMichael ^^

ScampMichael Over a year ago

We have both learned a lot since entering the program. If you keep it up at this pace for another ten years, Watch out Larry Page.

|

Collectives™ on Stack Overflow

sorting columns independently at array level

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related