Question 284406 in Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Looking to sort Google Sheet data by the Position of a Person

So, the basis of what I am attempting to do is have the sheet "Employee Hours" order the rows dependant on the persons position in "Column D" – So, for example if an individuals rank is changed to Assistant Manager, Senior Staff, they will be ordered correctly.

Either works, either formula’s or google script

Example: https://docs.google.com/spreadsheets/d/1wuL8rJd0zaGQROF5rhB_S9Xiv6eo8x6uOCd64gWcexM/edit?usp=sharing

I have attempted some forumlas, and tried looking up some scripts but nothing to help.

Tags:

2

Answers


  1. 0

    On a new sheet create a staff seniority table, something like this:

    Title Seniority
    President 1
    Manager 3
    Undermanager 4
    Vice President 2
    Staff 5
    Gopher 6

    Then create a named range over the data rows and call it Seniority

    On your staff listing page add a new column called Seniority and populate it with a lookup from this range:

    Job Title Seniority Name
    Manager =vlookup(A1,Seniority,2, false) Joe Bloggs
    President =vlookup(A2,Seniority,2, false) John Doe

    You can now sort the sheet (or just some range) by the Seniority column, and the Job Titles will appear in the correct order.
    You can hide the Seniority column for cosmetic purposes if it suits you.

    This can be automated by some script that fits with your other code. I leave that as an exercise for you.

    Login or Signup to reply.
  2. 0

    Sorting based upon position

    gs:

    function sortBasedUponPosition() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");//data sheet
  const osh = ss.getSheetByName("Sheet1");//output sheet
  const dsr = 11;//data start row
  const dsc = 4;//data start col
  const vs = sh.getRange(dsr, 4, sh.getLastRow() - dsr + 1, sh.getLastRow() - dsc + 1).getValues();
  const so = vs.reduce((a, r, i) => ( a.pA = [...new Set([...a.pA, r[0]])], a), { pA: [],so:function(){
    let obj = this.pA.reduce((ac,pos,idx) => (ac[pos] = idx, ac),{})
  return obj;} }).so();//function inside of object
  Logger.log(JSON.stringify(so));
  let end = "is near";
  vs.sort((a,b) => so[a[0]]-so[b[0]]);//the  actual sorting
  Logger.log(JSON.stringify(vs));//sorted
  osh.clearContents();
  osh.getRange(1,1,vs.length,vs[0].length).setValues(vs);//displayed on another sheet
}

    Sort Object:

    {"Manager":0,"Assistant Manager":1,"Director Of Operations":2,"Senior Staff":3,"Trial Senior Staff":4,"Staff":5,"Trial Phase":6}

    Data:

    D E F G H I J K L M N
    10 Employee Rank Employee Name Monday Tuesday Wednesday Thursday Friday Saturday Sunday Total Hours Pay
    11 Manager Juno Meeh 0 $0
    12 Assistant Manager Britt Nicole 0 $0
    13 Director Of Operations Darby Way 0 $0
    14 Senior Staff Sheeza Bati 0 $0
    15 Senior Staff Maelynn Kowaldi 0 $0
    16 Senior Staff Oli Sax 0 $0
    17 Trial Senior Staff Itachi Okamoto 0 $0
    18 Staff Barry Mosley 0 $0
    19 Staff Enzo Zepelli 0 $0
    20 Staff Joey Gambino 0 $0
    21 Staff Johnny Gambino 0 $0
    22 Staff Maddie Madison 0 $0
    23 Staff Madisyn Hope 0 $0
    24 Staff Nickoli Litchnovsky 0 $0
    25 Staff Penelope Dexter 0 $0
    26 Staff Tony Bacitari 0 $0
    27 Staff Yurei Gambino 0 $0
    28 Staff Cleetus Johnson 0 $0
    29 Staff Jojo Rodriguez 0 $0
    30 Staff Liv Laufluv 0 $0
    31 Staff Walter Dillard 0 $0
    32 Trial Phase William Ormond 0 $0
    33 Trial Phase Veronica Blake 0 $0
    34 Trial Phase Kate Smith 0 $0
    35 Trial Phase Sierra Foster 0 $0
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search