G Sheets: Group/Separate Rows based on column value, Adding Rows with value or conditional border format

Question

So I have everything working with multiple scripts and one of them is a script to automatically sort the sheet based on Column C (Date).

Here is the sample sheet: https://docs.google.com/spreadsheets/d/1nP4kZEikx1li8JNwCjsEc3ooadr0fiboglUizUcUdAQ/edit?usp=sharing

Basically the idea is for people to be able to add rows in the bottom, add the information and as soon as the date is set for it to move where it belongs. The problem is that I need a space between the different days but to manually add an empty row can't work because the auto sort sends it to the bottom because the date cell is blank.

Is there anyway to group each date with a sub-header row for each day with a OnOpen trigger? Basically something that recognizes the different values from Column C and adds one row with each value or literally any text with color formatting? Since I have the auto sort script it doesn't even matter if they get added at the end of the sheet, since all the other cells besides column D are empty it should just send it at the top of each date.

I did come across this solution https://stackoverflow.com/a/55944980/13895051 but I can't seem to make it work.

This is the auto sort script I'm using in case it matters

SHEET_NAME = "North Tonawanda";
SORT_DATA_RANGE = "A:S";
SORT_ORDER = [
{column: 3, ascending: true},  // 3 = column number, sorting by descending order
{column: 4, ascending: true} // 1 = column number, sort by ascending order 
];

 function onEdit(e){
 multiSortColumns();
 }
 function multiSortColumns(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName(SHEET_NAME);
 var range = sheet.getRange(SORT_DATA_RANGE);
 range.sort(SORT_ORDER);
 ss.toast('Sort complete.');
}

I've only worked with a couple scripts in the past so I have no idea what I'm doing and pivot tables are not really an option. I would appreciate the help! Thanks You!

Answer 1

I figured it out. I ended up creating a menu to trigger everything. I was getting frustrated so I created a menu item for the sort and to speed up the process I recorded a macro adding a row to the top and changing the date cell to "Add Date". So anytime someone adds a row with a new date they can add a row to the top, adding the date, hitting sort and everything goes where it belongs.

It's not an eloquent solution but at the end it worked out better. This way if multiple rows need to be edited they're not all jumping around.

In a previous comment I mention I tried the solution from https://stackoverflow.com/a/55944980/13895051 and said it didn't work. It was a simple fix. I had to change the format of the date column to plain text. Below is the finished product sorting by 2 columns and adding border between groups of rows.

Thanks for the help and patience!

SHEET_NAME = "North Tonawanda";
SORT_DATA_RANGE = "A:S";
SORT_ORDER = [
{column: 3, ascending: true},  // 3 = column number, sorting by descending order
{column: 4, ascending: true} // 1 = column number, sort by ascending order 
];

function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE);
  range.sort(SORT_ORDER);
  ss.toast('Sort complete.');
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).setBorder(null, 
null, null, null, false, false);
    var values = sheet.getRange(3, 3, sheet.getLastRow() - 1, 1).getValues();
    var rangeList = values.reduce(function(ar, e, i) {
      if (i > 0 && values[i - 1][0] != e[0] && e[0] != "") {
        ar.push("A" + (i + 2) + ":S" + (i + 2));
      }
      return ar;
    }, [])
    rangeList.push(sheet.getRange(sheet.getLastRow(), 1, 1, 
sheet.getLastColumn()).getA1Notation());
sheet.getRangeList(rangeList).setBorder(null, null, true, null, false, false, 
"black", SpreadsheetApp.BorderStyle.SOLID_THICK);
  }