Sort a range or array based on two columns that contain the date and time

Question

Currently I'm trying to create a Google Apps Script for Google Sheets which will allow adding weekly recurring events, batchwise, for upcoming events. My colleagues will then make minor changes to these added events (e.g. make date and time corrections, change the contact person, add materials neccessary for the event and so forth).

So far, I have written the following script:

function CopyWeeklyEventRows() {
  var ss = SpreadsheetApp.getActiveSheet();
  var repeatingWeeks = ss.getRange(5,1).getValue(); // gets how many weeks it should repeat
  var startDate = ss.getRange(6, 1).getValue(); // gets the start date
  var startWeekday = startDate.getDay(); // gives the weekday of the start date
  var regWeek = ss.getRange(9, 2, 4, 7).getValues(); // gets the regular week data
  var regWeekdays = new Array(regWeek.length); // creates an array to store the weekdays of the regWeek
  var ArrayStartDate = new Array(startDate); // helps to store the We

  for (var i = 0; i < regWeek.length; i++){ // calculates the difference between startWeekday and each regWeekdays
    regWeekdays[i] = regWeek[i][1].getDay() - startWeekday;
    Logger.log(regWeekdays[i]);
    // Add 7 to move to the next week and avoid negative values
    if (regWeekdays[i] < 0) {
      regWeekdays[i] = regWeekdays[i] + 7;
    }
    // Add days according to difference between startWeekday and each regWeekdays
    regWeek[i][0] = new Date(ArrayStartDate[0].getTime() + regWeekdays[i]*3600000*24);
  }
  // I'm struggling with this line. The array regWeek is not sorted:
  //regWeek.sort([{ column: 1, ascending: true }]);
  ss.getRange(ss.getLastRow() + 1, 2, 4, 7).setValues(regWeek); // copies weekly events after the last row
}

It allows to add one week of recurring events to the overview section of the spreadsheet based on a start date. If the start date is a Tuesday, the regular week is added starting from a Tuesday. However, the rows are not sorted according to the dates: .

How can the rows be sorted by ascending date (followed by time) before adding them to the overview?

My search for similar questions revealed Google Script sort 2D Array by any column which is the closest hit I've found. The same error message is shown when running my script with the sort line. I don't understand the difference between Range and array yet which might help to solve the issue.

To give you a broader picture, here's what I'm currently working on:

• I've noticed that the format will not necessarily remain when adding new recurring events. So far I haven't found the rule and formatted by hand in a second step.
• A drawback is currently that the weekly recurring events section is fixed. I've tried to find the last filled entry and use it to set the range of regWeek, but got stuck.
• Use the column A to exclude recurring events from the addition process using a dropdown.
• Allow my colleagues to add an event to the recurring events using a dropdown (e.g. A26). This event should then be added with sorting to the right day of the week and start time. The sorting will come in handy.

Thanks in advance for your input regarding the sorting as well as suggestions on how to improve the code in general.

A demo version of the spreadsheet

UpdateV01:

Here the code lines which copy and sort (first by date, then by time)

ss.getRange(ss.getLastRow()+1,2,4,7).setValues(regWeek); // copies weekly events after the last row
ss.getRange(ss.getLastRow()-3,2,4,7).sort([{column: 2, ascending: true}, {column: 4, ascending: true}]); // sorts only the copied weekly events chronologically

As @tehhowch pointed out, this is slow. Better to sort BEFORE writing. I will implement this method and post it here.

UpdateV02:

regWeek.sort(function (r1, r2) {
// sorts ascending on the third column, which is index 2
return r1[2] - r2[2];
});
regWeek.sort(function (r1, r2) {
// r1 and r2 are elements in the regWeek array, i.e.
// they are each a row array if regWeek is an array of arrays:
// Sort ascending on the first column, which is index 0:
// if r1[0] = 1, r2[0] = 2, then 1 - 2 is -1, so r1 sorts before r2
return r1[0] - r2[0];
});

UpdateV03:

Here an attempt to repeat the recurring events over several weeks. Don't know yet how to include the push for the whole "week".

// Repeat week for "A5" times and add to start/end date
for (var j = 0; j < repeatingWeeks; j++){
  for (var i = 0; i < numFilledRows; i++){
  regWeekRepeated[i+j*6][0] = new Date(regWeek[i][0].getTime() + j*7*3600000*24); // <-This line leads to an error message
  regWeekRepeated[i+j*6][3] = new Date(regWeek[i][3].getTime() + j*7*3600000*24);
  }
}

My question was answered and I was able to make the code work as intended.

Answer 1

You could sort the "Weekly Events" range before you set the regWeek variable. Then the range would be in the order you want before you process it. Or you could sort the whole "Overview" range after setting the data. Here's a quick function you can call to sort the range by multiple columns. You can of course tweak it to sort the "Weekly Events" range instead of the "Overview" range.

function sortRng() {
  var ss = SpreadsheetApp.getActiveSheet();
  var firstRow = 22; var firstCol = 1;
  var numRows = ss.getLastRow() - firstRow + 1; 
  var numCols = ss.getLastColumn();
  var overviewRng = ss.getRange(firstRow, firstCol, numRows, numCols);
  Logger.log(overviewRng.getA1Notation());
  overviewRng.sort([{column: 2, ascending: true}, {column: 4, ascending: true}]);
}

As for getting the number of filled rows in the Weekly Events section, you need to search a column that will always have data if any row has data (like the start date column b), loop through the values and the first time it finds a blank, return that number. That will give you the number of rows that it needs to copy. Warning: if you don't have at least one blank value in column B between the Weekly Events section and the Overview section, you will probably get unwanted results.

function getNumFilledRows() {
  var ss = SpreadsheetApp.getActiveSheet();
  var eventFirstRow = 9; var numFilledRows = 0;
  var colToCheck = 'B';//the StartDate col which should always have data if the row is filled
  var vals = ss.getRange(colToCheck + eventFirstRow + ":" + colToCheck).getValues();
  for (i = 0; i < vals.length; i++) {
    if (vals[i][0] == '') {
      numFilledRows = i;
      break;
    }
  }
  Logger.log(numFilledRows);
  return numFilledRows;
}

EDIT: If you just want to sort the array in javascript before writing, and you want to sort by Start Date first, then by Time of day, you could make a temporary array, and add a column to each row that is date and time combined. array.sort() sorts dates alphabetically, so you would need to convert that date to an integer. Then you could sort the array by the new column, then delete the new column from each row. I included a function that does this below. It could be a lot more compact but I thought it might be more legible like this.

function sortDates() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var vals = ss.getActiveSheet().getRange('B22:H34').getDisplayValues(); //get display values because getValues returns time as weird date 1899 and wrong time.
  var theDate = new Date(); var newArray = []; var theHour = ''; var theMinutes = '';
  var theTime = '';
  //Create a new array that inserts date and time as the first column in each row
  vals.forEach(function(aRow) {
    theTime = aRow[2];//hardcoded - assumes time is the third column that you grabbed
    //get the hours (before colon) as a number
    theHour = Number(theTime.substring(0,theTime.indexOf(':'))); 
    //get the minutes(after colon) as a number
    theMinutes = Number(theTime.substring(theTime.indexOf(':')+1)); 
    theDate = new Date(aRow[0]);//hardcoded - assumes date is the first column you grabbed.
    theDate.setHours(theHour);
    theDate.setMinutes(theMinutes);
    aRow.unshift(theDate.getTime()); //Add the date and time as integer to the first item in the aRow array for sorting purposes.
    newArray.push(aRow);
  });
  //Sort the newArray based on the first item of each row (date and time as number)
  newArray.sort((function(index){
    return function(a, b){
        return (a[index] === b[index] ? 0 : (a[index] < b[index] ? -1 : 1));
    };})(0));
  //Remove the first column of each row (date and time combined) that we added in the first step
  newArray.forEach(function(aRow) {
    aRow.shift();
  });
  Logger.log(newArray); 
} 

Answer 2

Given your comment - you want to sort the written chunk - you have two methods available. One is to sort written data after writing, by using the Spreadsheet service's Range#sort(sortObject) method. The other is to sort the data before writing, using the JavaScript Array#sort(sortFunction()) method.

Currently, your sort code //regWeek.sort([{ column: 1, ascending: true }]); is attempting to sort a JavaScript array, using the sorting object expected by the Spreadsheet service. Thus, you can simply chain this .sort(...) call to your write call, as Range#setValues() returns the same Range, allowing repeated Range method calling (e.g. to set values, then apply formatting, etc.).

This looks like:

ss.getRange(ss.getLastRow() + 1, 2, regWeek.length, regWeek[0].length)
   .setValues(regWeek)
   /* other "chainable" Range methods you want to apply to
       the cells you just wrote to. */
   .sort([{column: 1, ascending: true}, ...]);

Here I have updated the range you access to reference the data you are attempting to write - regWeek - so that it is always the correct size to hold the data. I've also visually broken apart the one-liner so you can better see the "chaining" that is happening between Spreadsheet service calls.

The other method - sorting before writing - will be faster, especially as the size and complexity of the sort increases. The idea behind sorting a range is you need to use a function that returns a negative value when the first index's value should come before the second's, a positive value when the first index's value should come after the second's, and a zero value if they are equivalent. This means a function that returns a boolean is NOT going to sort as one thinks, since false and 0 are equivalent in Javascript, while true and 1 are also equivalent.

Your sort looks like this, assuming regWeek is an array of arrays and you are sorting on numeric values (or at least values which will cast to numbers, like Dates).

regWeek.sort(function (r1, r2) {
  // r1 and r2 are elements in the regWeek array, i.e.
  // they are each a row array if regWeek is an array of arrays:
  // Sort ascending on the first column, which is index 0:
  // if r1[0] = 1, r2[0] = 2, then 1 - 2 is -1, so r1 sorts before r2
  return r1[0] - r2[0];
});

I strongly recommend reviewing the Array#sort documentation.