Google Sheets – Sort Function Moves Data to Bottom

google sheetsgoogle-apps-scriptgoogle-forms

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 |

  +---+-----------+-------+----------+-------+-----+---------+-------+----------+------+-------+--------+----------+
  |   |     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 |

*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.

Best Answer

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();

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

would do it.

Related Solutions

Google Sheets – Rearrange Form Data in New Sheet

Since you are going to be using a script anyway (for email generation), might as well use one for the rearrangement.

Here's a simplified example of form input data:

+---+------------+-------+---------------+-------+-------+-------+
|   |     A      |   B   |       C       |   D   |   E   |   F   |
+---+------------+-------+---------------+-------+-------+-------+
| 1 | Date       | Job   | Details       | cat 1 | cat 2 | cat 3 |
| 2 | 10/2/2015  | Job 1 | some details  | 2     | 3     | 1     |
| 3 | 10/10/2015 | Job 2 | other details | 0     | 2     | 3     |
+---+------------+-------+---------------+-------+-------+-------+

This will be rearranged on another sheet, called "Job List" below, by this script (which can be triggered by form submission):

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 < 3; j++) {
      output = output.concat(repeat(values[i], values[0][j+3], values[i][j+3]));
    }
  }
  outputSheet = ss.getSheetByName("Job List");  
  outputSheet.getRange(2, 1, output.length, output[0].length).setValues(output);
}

function repeat(row, category, quantity) {
  var arr = [];
  for (var i = 0; i < quantity; i++) {
    arr.push([row[0], category, quantity].concat(row.slice(1,3)));
  }
  return arr;
}

Explanation: the line with values[i][j+3] refers to various category counts within the row; j+3 is set so that j=0,1,2 correspond to 3,4,5 zero-based column indices (which mean the columns D,E,F above). For each count, the function repeat is called, which produces the necessary number of repetitions.

The function repeat inserts the date, category name, quantity of workers for that category (not sure why, but you wanted it), and the rest of the job description.

The whole thing is then recorded in sheet "Job List", like so:

+----+------------+----------+----------+-------+---------------+
|    |     A      |    B     |    C     |   D   |       E       |
+----+------------+----------+----------+-------+---------------+
|  1 | Date       | Category | Quantity | Job   | Details       |
|  2 | 10/2/2015  | cat 1    | 2        | Job 1 | some details  |
|  3 | 10/2/2015  | cat 1    | 2        | Job 1 | some details  |
|  4 | 10/2/2015  | cat 2    | 3        | Job 1 | some details  |
|  5 | 10/2/2015  | cat 2    | 3        | Job 1 | some details  |
|  6 | 10/2/2015  | cat 2    | 3        | Job 1 | some details  |
|  7 | 10/2/2015  | cat 3    | 1        | Job 1 | some details  |
|  8 | 10/10/2015 | cat 2    | 2        | Job 2 | other details |
|  9 | 10/10/2015 | cat 2    | 2        | Job 2 | other details |
| 10 | 10/10/2015 | cat 3    | 3        | Job 2 | other details |
| 11 | 10/10/2015 | cat 3    | 3        | Job 2 | other details |
| 12 | 10/10/2015 | cat 3    | 3        | Job 2 | other details |
+----+------------+----------+----------+-------+---------------+

Column F can be used for student names.

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

You need onFormSubmit, not onEdit. Open the documentation of classes Spreadsheet, Sheet, and Range, and look up each of the methods used below. To give you a general idea of what is done here:

ss = active spreadsheet, sheet = active sheet
values = an array of values in the active sheet. Note that arrays in JavaScript are 0-indexed.
row = the row added to sheet when the onFormSubmit event happened. This one is 1-based, so the adjustment row-1 is used when addressing arrays.
values[row-1][1] - the value in column B of this row. [Row number][Column Number] in 0-based indexing.

The script isn't perfect. For one thing, it assumes the target has rows available. You may want to use insertRowAfter() to be safe. Or just create a lot of rows. The logic for other things you mentioned is not here either, but it's essentially the same.

function onFormSubmit(event) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var values = sheet.getDataRange().getValues();
  var row = event.range.getRow();
  if (values[row-1][1] == 'Luke') {   // column B in 0-based indexing 
    var targetSheet = ss.getSheetByName('for Luke');
    var lastRow = targetSheet.getLastRow();
    var targetRange = targetSheet.getRange(lastRow+1, 1, 1, values[0].length);
    targetRange.setValues([values[row-1]]);
  }   
}

Best Answer

Related Solutions

Google Sheets – Rearrange Form Data in New Sheet

Google Sheets – Automatically Copy and Paste Updated Cells from Forms

Related Topic