Google-sheets – way in Google Sheets to lock a row from sorting, but, not be frozen

Ultimately, you need to do what you already predicted: adding a leading zero. The TEXT function will make that happen like this:

=TEXT(A2, "00")

I've created a formula that takes on the complete column, filter for empty cells, brings together the MONTH and DAY and SORTs the lot.

Formula

=SORT(ARRAYFORMULA(TEXT(MONTH(FILTER(A2:A;A2:A<>""));"00") & " - " 
 & TEXT(DAY(FILTER(A2:A;A2:A<>""));"00"));1;1)

Explained

Here's a break-down description of the formula:

1. The FILTER function will filter for all rows that have something.
2. The MONTH and DAY functions will extract the respective values from the date.
3. The TEXT function will convert the value from point 2 into a pre-formatted STRING.
4. The ARRAYFORMULA function will apply all the above to the complete column (skipping the header).
5. The SORT function will sort the result, given by the ARRAYFORMULA.

The SORT function allows for sorting. If you do that via a column sort, the entry of the ARRAYFORMULA is taken into account as well and gets re-positioned, causing mayhem.

Screenshot

Example

See example file I've created: sorting dates as text

Well, if don't see the solution, you can write it :)

I created a Python application that can be deployed in Google Cloud (and will work within free quota unless under heavy load).

If you deploy it to your-foo-app.appspot.com (which is really simple), and your published spreadsheet is https://docs.google.com/spreadsheets/d/Bar12345/pubhtml?gid=67890, than your-foo-app.appspot.com/Bar12345/67890 will provide link to the scrollable-with-frozen-rows version of published sheet, and your-foo-app.appspot.com/Bar12345/ will provide the whole spreadsheet with improvised switching tabs. Works in modern browsers.

Here is the working version of the app. Just don't use it in production, because it may get overloaded; deploy your own copy instead.