How can a custom sorting script be prevented from eating header row?

Question

I modified the example code provided here to come up with the script below. While it does the job, I have been unable to find a way to prevent it from deleting the header (first) row of the sheet.

A somewhat tangential aside: the following two lines of code at the end of the cited script were removed to sort the sheet without deleting its contents and replacing them with an unformatted array:

sheet.clear();
sheet.getRange(1,1,rows,cols).setValues(vf);

I tried skipping it in all the ranges but either get errors about mismatches between ranges and data or other problems.

function status_sort(){

  var ss=SpreadsheetApp.getActive();

  var sheet = ss.getSheetByName('current');

  var rows = sheet.getLastRow();

  var cols = sheet.getLastColumn(); 

  var columnToSortBy = 4;

  var rg = sheet.getRange(2, columnToSortBy, sheet.getLastRow(), 1);

  var v1 = rg.getValues();

  var sortObj = {'black_box':1, 'purple_box':2, 'green_box':3, 'yellow_box':4, 'red_box':5,'blue_box':6};

  var col = [];

  for(var i = 0; i < v1.length; i++){

    col.push([sortObj[v1[i]]]);
  }

  sheet.getRange(2, cols + 1, rows, 1).setValues(col);

 sheet.getDataRange().sort([{column:sheet.getLastColumn(),ascending:true}]);

  var vf = sheet.getDataRange().getValues();

    for(var i = 0; i < vf.length; i++){

      vf[i].splice(vf[i].length,1);

    }

}

I'm not hip to javascript norms, but I think it might be considered bad style to include braces (like that last one) whose mate is not also included. Also, it seems to me that the only lines that should be indented here are the guts of the for loops (or perhaps the whole thing, if it's in a function, and then they would be indented further). Sorry to be a pedant, but questions with bad code style tend to not get as much attention. — MatrixManAtYrService
Consider taking a stroll through: google.github.io/styleguide/javascriptguide.xml — MatrixManAtYrService
Part of the problem with the style was that I left out the function head (now included) by mistake. I appreciate the pedantic feedback—wearing a pedant hat when writing code seems like good practice! Thank you for pointing me to the JS guide! — informavore

MatrixManAtYrService MatrixManAtYrService · Accepted Answer · 2019-05-30T23:24:29

It looks like v1 contains your data (no headers) and you put keys into col which gets appended as an additional column for sorting purposes only, then you sort the whole thing by the keys. If I'm reading it right your headers ought to be somewhere in the sorted data, since I don't think getDataRange is smart enough to ignore your headers.

I wonder what would happen if you replaced:

sheet.getDataRange().sort([{column:sheet.getLastColumn(),ascending:true}]);

with:

sortThis = sheet.getRange(2, 1, rows - 1, cols + 1)
sortThis.sort([{column:sheet.getLastColumn(),ascending:true}]);

// getRange parameters:
// 2        : skip a row for the headers
// 1        : start with the leftmost column
// rows - 1 : get one fewer than the original row count (to exclude the headers)
// cols     : get one more than the original column count (to include the extra)

As an aside, I think that the loop at the end grabs everything in the sheet and then removes the last column (which was only injected for sorting purposes), and the code that you removed replaces the sheet with the result. If you're not going to call setValues(vf) then you may as well omit any other code that reference vf too.

Based on https://stackoverflow.com/a/42224466/1054322 I wonder if you might want to use getDisplayValues() instead. I didn't see a setDisplayValues() in the API reference, so I'm going to guess that your formatting is getting lost on read, and not write, and further that setValues() will not also clobber your formatting. Not sure if this will interfere with the sorting though, since the displayValues might wrap the data and therefore sort differently.

How can a custom sorting script be prevented from eating header row?

1 Answers