How to custom sort text in Google Sheet App Scripts?-CodePudding

I have minimal experience with coding so any help is sincerely appreciated! I am trying to sort data in Google Sheets. How can I sort column 3 by the specific order listed below?

  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ws = ss.getSheetByName("Residency Info")
  const range = ws.getRange('A3:BX63')
  range.sort([
    {column: 3, sortBy:('Interview Scheduled','Waitlisted','Supplemental Submitted','Supplemental Questions','Application Submitted','Interview Complete','Withdrawn','Rejected')}, 
    {column: 4, ascending:true}, 
    {column: 10, ascending:true}, 
    {column: 2, ascending:true}]
    );
}

function onEdit(e) {
  const row = e.range.getRow()
  if(row >= 3)
  autoSort()
}

CodePudding user response：

Put this formula in row 3 of a free column:

=arrayformula( 
  switch( 
    C3:C, 
    "Interview Scheduled", "01 ", 
    "Waitlisted", "02 ", 
    "Supplemental Submitted", "03 ", 
    "Supplemental Questions", "04 ", 
    "Application Submitted", "05 ", 
    "Interview Complete", "06 ", 
    "Withdrawn", "07 ", 
    "Rejected", "08 ", 
    "" 
  ) & C3:C 
)

Then modify your script to sort by the new column. Assuming the new column is N, use this:

  range.sort([14, 4, 10, 2]);

You can hide the helper column if it gets in the way.