I've been looking script for this googlesheet.

enter image description here

What I want to do is that I want the data (applicants) be arranged automatically when I enter them to the spreadsheet according to their status. For example, if a candidate's status is hired or when I changed his status into "hired", I want the spreadsheet to automatically place the candidate/entry in the last order along with the details (eg. contacts, notes, etc). And if the status of the applicant is for "FLW UP", I want the spreadsheet to arrange or move the applicant to the first rows so that it's easier to see who needs to be worked on first in the order. I hope someone can give me a script for this. Thank you!

CodePudding user response：

Order by validation list

function onEdit(e){
  e.source.toast('Sorting')
  const sh = e.range.getSheet();
  if(sh.getName() =="Change Sheet Name" && e.range.columnStart == 2 && e.range.rowStart > 1 ) {
    const so = ["HIRED","TBD","UNRESPONSIVE",""];//This is the validation list for column B
    let vs = sh.getRange(2,1,sh.getLastRow() - 1, sh.getLastColumn()).getValues().sort((a,b) => {
      return so.indexOf(a[1]) - so.indexOf(b[1]);
    })
    sh.getRange(2,1,sh.getLastRow() - 1, sh.getLastColumn()).setValues(vs);
  }
  e.source.toast('Completed');
}

CodePudding user response：

Well, I had put something together recently that accomplishes this, as I haven't found any real answers. Unfortunately, it's not as easy as it sounds.

The code below will work to sort your data, while preserving: values, formulas, bandings, font styles, background colors, notes and (experimentally) validation rules.

Try:

function onEdit(e) {

  const targetSheet = `YOUR_SHEET_NAME`
  const sortBy = [`AppearsFirst`, `...`, `...`, `AppearsLast`]

  if (e.source.getActiveSheet().getName() === targetSheet) {

    if (e.range.rowStart >= 2 && e.range.columnStart === 2 && e.range.columnEnd === 2) {

      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(targetSheet)
      const range = sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn())
      const sortOrder = { ...sortBy }    

      const INIT = {
        Values: range.getValues(),
        Formulas: range.getFormulas(),
        Bandings: range.getBandings(),
        FontStyles: range.getFontStyles(),
        BackgroundColors: range.getBackgrounds().map(row => row.map(cell => (cell === `#ffffff`) ? `` : cell)),
        Notes: range.getNotes(),
        ValidationRules: range.getDataValidations()
      }

      const DATA = INIT.Values.map((i, index) => {
        return {
          RowData: i,
          SortIndex: parseInt(Object.keys(sortOrder).find(key => sortOrder[key] === i[columnNumber-1])),
          Formula: INIT.Formulas[index],
          FontStyle: INIT.FontStyles[index],
          BackgroundColor: INIT.BackgroundColors[index],
          Notes: INIT.Notes[index],
          ValidationRules: INIT.ValidationRules[index]
        }
      }).sort((a, b) => (a.SortIndex - b.SortIndex))

      const SORTED = {
        Values: DATA.map((row, rowIndex) => row.RowData.map((col, colIndex) => row.Formula[colIndex] || col)),
        FontStyles: DATA.map(i => i.FontStyle),
        BackgroundColors: DATA.map(i => i.BackgroundColor),
        Notes: Data.map(i => i.Notes),
        ValidationRules: DATA.map(i => i.ValidationRules)
      }

      range.setValues(SORTED.Values)
           .setDataValidations(SORTED.ValidationRules)
           .setNotes(SORTED.Notes)
           .setFontStyles(SORTED.FontStyles)
           .setBackgroundColors(SORTED.BackgroundColors)

    }

  }

}

Be sure to fill in these variables listed at the top with the appropriate values:

const targetSheet = `YOUR_SHEET_NAME`
const sortBy = [`AppearsFirst`, `...`, `...`, `AppearsLast`]
// Example: [`Red`, `Orange`, `Yellow`, `Green`, `Blue`, `Purple`]

I've set the function to trigger when anything in B2:B is added based on the sheet example you posted.

Please let me know if this works for you, or any errors/bugs you may see.