google apps script

function customSortTwoColumns() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const values = range.getValues();

  // separate header row (first row)
  const header = values.shift(); // remove first row from sorting

  // sort remaining rows
  values.sort((a, b) => {
    // first column: sort lexicographically
    const col1Comparison = a[0].toString().localeCompare(b[0].toString());
    if (col1Comparison !== 0) return col1Comparison;

    // second column: sort numerically (ignoring text after space)
    const numA = parseFloat(a[1].toString().split(" ")[0]) || 0;
    const numB = parseFloat(b[1].toString().split(" ")[0]) || 0;
    return numA - numB;
  });

  // add the header row back and write the sorted data to the sheet
  values.unshift(header); // add the header row back to the top
  range.setValues(values);
}