"sort" function results in all of my data going to the bottom of the sheet

Question

I am trying to use a form to create a list of jobs for our student employees, which exists as an editable Google sheet. I would like to be able to have any staff member add jobs that need to be taken, by using the form, and students could view a sheet for any openings and put their names down for any they wanted.

With help from this site, and some modifications, I have a system that works to place the info from the forms into a list. Where my problems start coming in, is I want to be able to order the sheet by status and then by date.

Here is the previous thread.

Essentially, the form gathers the following data :

+---+------------+-------+-------+-----+---------+-------+----------+------+--------+--------+--------+
|   |     A      |   B   |   C   |  D  |    E    |   F   |     G    |   H  |    I   |    J   |    K   |
+---+------------+-------+-------+-----+---------+-------+----------+------+--------+--------+--------+
| 1 | Timestamp  | Date  | Start | End | service | Event | Building | Room | #Cat 1 | #Cat 2 | #Cat 3 |

Timestamp|Date|Start Time|End Time|Service|Event|Building|Room|#Category 1|#Category 2|#Category 3|
Where that Categories are different classifications of worker, with a quantity for each. Upon running the following script, the data goes into a second sheet: "Job List" with the following fields:

  +---+-----------+-------+----------+-------+-----+---------+-------+----------+------+-------+--------+----------+
  |   |     A     |   B   |     C    |   D   |  E  |    F    |   G   |     H    |   I  |   J   |   K    |     L    |
  +---+-----------+-------+----------+-------+-----+---------+-------+----------+------+-------+--------+----------+
  | 1 | Techname  | Date  | Category | Start | End | service | Event | Building | Room | Notes | Status | DateSort |

Tech name (where students manually add their name if they want a job)|Date|Type|Start|End|Service|Event|Building|Room|Notes|Status*|DATESORT**|

*Status is an array formula in Cell K1, which auto populates the column as "filled" "unfilled" or "urgent" using the formula =ARRAYFORMULA(If(Row(A1:A)=1,"STATUS",IF(B1:B,If(ISBLANK(A1:A),If(NOT(ISBLANK(B1:B)),IF(B1:B=TODAY(),"Urgent","Unfilled"),""),"Filled"),"")))

**DateSort is in L1 and simply coverts the date to a value: =ARRAYFORMULA(If(Row(A1:A)=1,"DATESort",IF(ISBLANK(B1:B),"",DATEVALUE(B1:B))))

Here is my script:

function processJobs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var output = [];
  for (var i = 1; i < values.length; i++) {
    for (var j = 0; j < 11; j++) {
      output = output.concat(repeat(values[i], values[0][j+9], values[i][j+9]));
    }
  }
  outputSheet = ss.getSheetByName("Job List");  
  outputSheet.getRange(2, 2, output.length, output[0].length).setValues(output);
 //runs function to order sheets by Status then date (as datevaule)
 OrderSheet();
}

// Adds as many duplicate rows as needed for each job type
function repeat(row, category, quantity) {
  var arr = [];
  for (var i = 0; i < quantity; i++) {
    arr.push([row[1], category].concat(row.slice(2,8)));    
  }
  return arr
}

// Invoked to order the "Job List" from earliest to latest by date of shift
function OrderSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var JobList = ss.getSheetByName("Job List");
  var data = JobList.getDataRange();
  data.sort([10,11]);

You will notice that I have a separate line for each person that would need to be on a shift, so if category 3 needed three people, there would be 3 different identical rows. The sheet works fine up until the sort. The rows are in fact sorted (first by status and then by date), however they are all moved to the bottom of the sheet (row1001 and up). I also notice that if there were student names entered in the "name" column, these values get erased when the script runs.

Any suggestions on rectifying these issues? I think that it may have something to do with the array formulae, that is causing all rows to be seen as having data.

Ultimately, when this is finished, I hope to add a script that will check the sheet every morning, and if a status is "Urgent" (meaning it is unfilled as of the day of the assignment), an email will be sent out to alert us of the vacancy.

Answer 1

Cells containing the empty string "" appear empty but are not (isblank returns FALSE for them). They get sorted ahead of nonempty strings, which is not what you want. To avoid this, replace

if(isblank(B1:B), "", datevalue(B1:B))

by the simpler

iferror(datevalue(B1:B))

The command iferror returns its (optional) second argument if there is an error evaluating the first argument; otherwise it leaves the cell blank. Blank cells are sorted to the bottom.

---

A potential drawback of the above is that misformatted dates are simply ignored, while you may want to see and fix them. This is something you can address separately; e.g., have a column with

if(isblank(B1:B), 0, if(iserror(datevalue(B1:B), 1, 0))

and sum over it to get the total number of nonempty but invalid date cells.

---

Another potential issue is that you are sorting a range containing arrayformula. The sort may move the formula to another row, creating a mess. It's safer to exclude the header row from sorting. Replacing

var data = JobList.getDataRange();

by

var data = JobList.getDataRange().offset(1,0);

would do it.