title: Sort a range or array based on two columns that contain the date and time
body: <p>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).</p>
  
  <p>So far, I have written the following script:</p>
  
  <pre><code>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 &lt; 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] &lt; 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
  }
  </code></pre>
  
  <p>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. <a href="https://i.stack.imgur.com/s7sXO.png" rel="nofollow noreferrer">However, the rows are not sorted according to the dates</a>: 
  <img src="https://i.stack.imgur.com/s7sXO.png" alt="dates are not sorted">.</p>
  
  <p><strong>How can the rows be sorted by ascending date (followed by time) before adding them to the overview?</strong></p>
  
  <p>My search for similar questions revealed <a href="https://stackoverflow.com/questions/26300610/google-script-sort-2d-array-by-any-column">Google Script sort 2D Array by any column</a> which is the closest hit I've found. The same error message is shown when running my script with the <code>sort</code> line. I don't understand the difference between <code>Range</code> and <code>array</code> yet which might help to solve the issue.</p>
  
  <p>To give you a broader picture, here's what I'm currently working on:</p>
  
  <ul>
  <li>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.</li>
  <li>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 <code>regWeek</code>, but got stuck.</li>
  <li>Use the column A to exclude recurring events from the addition
  process using a dropdown.</li>
  <li>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.</li>
  </ul>
  
  <p>Thanks in advance for your input regarding the sorting as well as suggestions on how to improve the code in general.</p>
  
  <p><a href="https://docs.google.com/spreadsheets/d/1NP7pPfcXiQGOjNFjVV0DeHZY0_fH1kExO_-FVQuAHsg/edit?usp=sharing" rel="nofollow noreferrer">A demo version of the spreadsheet</a></p>
  
  <p><strong>UpdateV01:</strong></p>
  
  <p>Here the code lines which copy and sort (first by date, then by time) </p>
  
  <pre><code>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
  </code></pre>
  
  <p>As @tehhowch pointed out, this is slow. Better to sort BEFORE writing.
  I will implement this method and post it here.</p>
  
  <p><strong>UpdateV02:</strong></p>
  
  <pre><code>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];
  });
  </code></pre>
  
  <p><strong>UpdateV03:</strong></p>
  
  <p>Here an attempt to repeat the recurring events over several weeks. Don't know yet how to include the push for the whole "week".</p>
  
  <pre><code>// Repeat week for "A5" times and add to start/end date
  for (var j = 0; j &lt; repeatingWeeks; j++){
    for (var i = 0; i &lt; numFilledRows; i++){
    regWeekRepeated[i+j*6][0] = new Date(regWeek[i][0].getTime() + j*7*3600000*24); // &lt;-This line leads to an error message
    regWeekRepeated[i+j*6][3] = new Date(regWeek[i][3].getTime() + j*7*3600000*24);
    }
  }
  </code></pre>
  
  <p>My question was answered and I was able to make the code work as intended.</p>