Can I specify a header row when sorting in a script?

Question

I have a spreadsheet that I'd like to sort by multiple columns many times as I work on the document. This is a multi-step process using the GUI, and seemed far too time-consuming. I have to check the box for a header row, set up the first sorting parameter, and then add another one. Phew! It gets old quick.

Sorting the sheet was no problem, but the header row was also sorted! I don't find any way to specify that I have a header row as a parameter to the sort() function, nor do I see any way to globally define a header row that will be automatically noticed by sort().

In lieu of that, I saved the header row, deleted the first row, sorted the sheet, inserted a new row at the very top, re-created the header row, and formatted the header row. Wow! It didn't take that long to write and it works well, but it's awfully complicated!

From the user perspective, the header row briefly disappears before popping back up. It's not too bad, but it's plainly obvious something is going on.

So finally I get around to my question: Have I overlooked the ability to acknowledge the presence of a header row when I'm sorting? Or does sorting a sheet with a header row present in a script also sort the header row, with no recourse?

If my method, or one like it, is required to avoid including the header row in the sort process, is there somewhere that I can notify the Google Apps team so they can consider adding this feature? Since specifying the existence of a header row exists in the GUI with a simple checkbox, I hope it's present or can be added when sorting within a script.

EDIT

My original code (with documentation/comments removed):

sheet.deleteRow(1);

rows.sort([<1st col>, <2nd col>]);

sheet.insertRowsBefore(1, 1);
for (var j = 0; j <= (numCols - 1); j++) {
  sheet.getRange(1, (j + 1)).setValue(firstRow[j]);
}

sheet.getRange(1, 1, 1, numCols).setFontWeight("bold");
sheet.getRange(1, 1, 2, numCols).setBorder(true, true, true, true, true, true);

What I've tried in place of the above code thanks for Thomas' suggestion:

sheet.setFrozenRows(1);
sheet.sort(<2nd col>);
sheet.sort(<1st col>);
sheet.setFrozenRows(0);

Unfortunately, this just sorts by column, not by row. Replacing the 2 sheet.sort(); calls with the rows.short(); line in the first code block doesn't work. Trying this results in the initial problem I reported in my comment where the header row is sorted along with the other data, even though the first row is frozen before sorting.

Also, unless another line of code is added below this I get a perpetual "Working..." notification at the top of the spreadsheet. It doesn't seem to affect anything, however.

In all cases, var sheet = SpreadsheetApp.getActiveSheet();, var rows = sheet.getDataRange();, and firstRow is an array of the header row data.

Answer 1

In all cases, var sheet = SpreadsheetApp.getActiveSheet();, var rows = sheet.getDataRange();, and firstRow is an array of the header row data.

Rather than using getDataRange(), you can just get the range you want to sort (that is, exclude the header row):

var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

---

This next part is just an answer/observation to a "secondary" question:

After the script is finished, the header row was sorted along with all the other rows although the first row is frozen.

I think this is a bit of a limitation in GAS where some of these "spreadsheet tool" methods don't play nice with one another - this issue is not the same as yours but I think it's (kind of) related.

So with the "frozen header" workaround, I'd be interested to see if SpreadsheetApp.flush(); inserted just after sheet.setFrozenRows(1); makes it work.

Answer 2

You want to do this:

• Create a spreadsheet
• Put in some random Data
• Freeze the first row, http://support.google.com/docs/bin/answer.py?hl=en&answer=54813

and then call this script:

function myFunction() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().sort(1);
}

And there you go :)

With kind regards,

Thomas van Latum