How to Sort a Column of Dates and Times in Google Sheets Using Apps Script

Sorting a column of dates and times in a specific format, like “Sun, Jun 2, 2024, 11:42 PM,” can be a useful feature when managing schedules, logs, or any time-sensitive data. Google Apps Script provides a powerful way to automate this process. In this blog post, we’ll walk through the steps to write an Apps Script code that sorts a column of dates and times in the desired format.

Let’s start with a table of sample data to illustrate the sorting process:

Event	Date and Time
Meeting A	Sun, Jun 2, 2024, 11:42 PM
Meeting B	Mon, Jun 3, 2024, 10:15 AM
Meeting C	Sat, Jun 1, 2024, 5:30 PM
Meeting D	Tue, Jun 4, 2024, 9:00 AM
Meeting E	Wed, Jun 5, 2024, 2:45 PM

Step-by-Step Guide

1. Set Up the Google Sheet

First, create a Google Sheet and populate it with the sample data. Ensure the dates and times are in the specified format.

2. Open the Script Editor

Go to Extensions > Apps Script to open the Google Apps Script editor.

3. Write the Script

Below is the script to sort the “Date and Time” column:

function sortDates() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A2:B6");
  var values = range.getValues();

  Logger.log("Original Values: " + JSON.stringify(values));

  // Convert date objects to formatted date strings
  values = values.map(function(row) {
    return [row[0], formatDate(row[1])];
  });

  // Sort the formatted date strings
  values.sort(function(a, b) {
    return parseDateString(a[1]) - parseDateString(b[1]);
  });

  Logger.log("Sorted Values: " + JSON.stringify(values));

  // Set the sorted values back to the range
  range.setValues(values);
}

function formatDate(date) {
  if (date instanceof Date) {
    // Format the date as "Sun, Jun 2, 2024, 11:42 PM"
    var options = {
      weekday: 'short',
      year: 'numeric',
      month: 'short',
      day: 'numeric',
      hour: 'numeric',
      minute: 'numeric',
      hour12: true
    };
    return date.toLocaleString('en-US', options).replace(',', '');
  }
  return date;
}

function parseDateString(dateString) {
  if (typeof dateString !== 'string') {
    Logger.log("Invalid dateString: " + dateString);
    return new Date(0);
  }

  var dateParts = dateString.split(' ');
  if (dateParts.length < 5) {
    Logger.log("Invalid format: " + dateString);
    return new Date(0);
  }

  var dayOfWeek = dateParts[0];
  var month = dateParts[1];
  var day = dateParts[2];
  var year = dateParts[3];
  var time = dateParts[4];
  var ampm = dateParts[5];

  // Combine date and time parts into a valid date string
  var dateStr = month + " " + day + ", " + year + " " + time + " " + ampm;
  Logger.log("Parsed Date String: " + dateStr);

  return new Date(dateStr);
}

Explanation

1. formatDate(date) Function: This function formats the Date object into a string in the desired format.
2. sortDates() Function: The script converts each date object to the formatted date string using formatDate() before sorting.
3. Validation in parseDateString(): This function checks for valid string format and logs any invalid formats encountered.

Running the Script

1. Save the script with a meaningful name, such as “SortDatesScript”.
2. In the Apps Script editor, click the run button (triangle icon) next to the sortDates function to execute the script.
3. Check your Google Sheet. The “Date and Time” column should now be sorted in ascending order.

Conclusion

By using Google Apps Script, you can automate the sorting of a column containing dates and times in a specific format. This approach is not only efficient but also saves time, especially when dealing with large datasets. Happy coding!

Additional Tips

• Automate the Script: You can set a trigger to run this script automatically at specific intervals or based on an event, such as when the spreadsheet is edited.
• Error Handling: Add error handling to manage any unexpected date formats or empty cells.
• Customization: Modify the parseDateString function to accommodate different date formats as needed.