Sorting time ascending in Apps Script

Question

I have a Google Sheets document with the following information starting in the 3rd row: names in column A, times in column B and groups in column C. I am trying to sort all of this information ascending by time, but am running in to a problem. The issue I have is that when there are colons in the time, sometimes it isn't sorted properly. For example, 10:05.63 is put before 9:49.28.

This is what I have so far:

   // Getting the range to sort (from A3 to the end of the used range)
   var range = sheet.getRange(3, 1, sheet.getLastRow() - 2, sheet.getLastColumn());
      
   // Sorting the range ascending by column B
   range.sort({column: 2, ascending: true});

For example, this input:

name one     |9:49.28     |group1
name two     |10:05.63    |group2
name three   |9:52.57     |group3

Should be replaced with:

name one     |9:49.28     |group1
name three   |9:52.57     |group3
name two     |10:05.63    |group2

Answer 1

After some troubleshooting, I ended up with this function. The problem was that the data was being read as a string instead of an actual time value and I was looking to format the time in a way that wasn't built in to Google Sheets. The function converts all of the following formats: mm:ss.ms, m:ss.ms, ss.ms, s.ms, ss, s, mm:ss, m:s (with the milliseconds rounded to one or two decimal places) to a time value that Google Sheets can read and then formats the time to appear as before. So no changes will be seen, but all the data can now actually be read as a time value.

function sortSheets() {
  // Get the current spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data = ss.getSheetByName("data");

  // Get the range to convert
  var range = data.getRange(1, 1, data.getLastRow(), data.getLastColumn();
    var values = range.getValues();

    // Define regex for various time formats
    var regex1 = /^\d{1,2}:\d{2}\.\d{1,2}$/; // m:ss.ms or mm:ss.ms
    var regex2 = /^\d{1,2}:\d{2}$/; // m:ss or mm:ss
    var regex3 = /^\d{1,2}\.\d{1,2}$/; // ss.ms or s.ms
    var regex4 = /^\d{1,2}$/; // ss or s

    // Convert values that follow the time formats to mm:ss.ms
    for (var i = 0; i < values.length; i++) {
      for (var j = 0; j < values[i].length; j++) {
        var value = values[i][j];

        if (regex1.test(value)) {
          // Ensure it has exactly two decimal places
          var parts = value.split(':');
          var secondsParts = parts[1].split('.');
          if (secondsParts[1].length === 1) {
            secondsParts[1] += '0';
          }
          values[i][j] = '00:' + parts[0] + ':' + secondsParts.join('.');
        } else if (regex2.test(value)) {
          values[i][j] = '00:' + value + '.00';
        } else if (regex3.test(value)) {
          values[i][j] = '00:00:' + value;
        } else if (regex4.test(value)) {
          values[i][j] = '00:00:' + value + '.00';
        }
      }
    }

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

    // Define the custom number format
    var customFormat = '[m]:ss.00'; // Elapsed minutes, seconds with leading 0, and hundredths of a second
    // Format times back to what they were
    range.setNumberFormat(customFormat);
  }