2

I am trying to use a form to create a list of jobs for our student employees, which exists as an editable Google sheet. The thought, being any of the staff could 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.

Normally, I would just give them access to the results sheet.

However, there are 3 different categories of student employees, and some jobs may require any combination of numbers of students in each category. On the form I have a simple dropdown of amounts for each type: # Cat1, # cat 2, # cat3.

What I would like to do is actually create a new Row with all of the same data from the form (Date, Start time, End time, Job Type, Location), as many times as needed for each Category.

Example:

Date    |**Category*|**Qty*| Start    |End      |Job Type |Location
10/10/15|Cat 3      |3     | 10:00 AM |11:00 AM |Set-up   |Library
10/10/15|Cat 3      |3     | 10:00 AM |11:00 AM |Set-up   |Library
10/10/15|Cat 3      |3     | 10:00 AM |11:00 AM |Set-up   |Library
10/10/15|Cat 2      |1     | 10:00 AM |11:00 AM |Set-up   |Library
10/10/15|Cat 1      |2     | 10:00 AM |11:00 AM |Set-up   |Library
10/10/15|Cat 1      |2     | 10:00 AM |11:00 AM |Set-up   |Library

I would then like to add a column for student name, in which they could add their name to a job they were taking.

I plan to execute a script that will then check each morning for any unfilled jobs for that day, and generate an email to the Staff notifying us of the vacancy.

So what would I need to do to generate this new output?

1

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.

  • Thanks! This was incredibly useful, and very much what I was hoping to achieve! I was able to alter/ expand it without much trouble at all. One brief question, as I'm somewhat new to a lot of this, what is the significance of [0] in the function'output[0].length? – drumtechjp Oct 27 '15 at 13:23
  • output is a double array, which is how the content of sheet ranges is presented in Apps Script. E.g., [[1,2,3], [4,5,6]] means two rows, with 1,2,3 in the first, and 4,5,6 in the second. I need to know the height and width of this range. The length of [[1,2,3], [4,5,6]] is 2, indicating two rows. Then I grab the 1st element (indexed 0), which is [1,2,3] and find its length: it is 3, indicating 3 columns. It's just convenient to look at the first row of a rectangular array when counting its width. – user79865 Oct 27 '15 at 13:37
  • Thanks again, that makes a lot of sense! As I've gotten a chance to tailor it to my needs a little more. a few questions have come up. I have shifted the output over to start at (2,2), as I have a header row, and want to use the first column for student names. An issue I have noticed is every time I run the script, it erases the name column (A). Also, My last 2 columns (K and L) have Array formulae in their first cell: In K: =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"),""))) – drumtechjp Oct 29 '15 at 21:35
  • In L: =ARRAYFORMULA(If(Row(A1:A)=1,"DATESort",IF(ISBLANK(B1:B),"",DATEVALUE(B1:B)))) All of my headers are as follows: Name* | Date | Type | Start Time | End Time | Service | Event | Building | Room | Notes* | Status^ | DateSort^| * = added by user directly into sheet. ^ = calculated via first row array formula. I decided to remove the quantity from final output. I think as a result of the array formulae, it sees the entire sheet as the data range. in trying to obtain a lastRow() it always seems to return 1001. My other assumption was that it involved the conditional formatting. – drumtechjp Oct 29 '15 at 21:46
  • the conditional formatting simply changed the color of a row based on the value of "Status", but upon removing this, I was still getting all 1001 rows. Trying to run my sort seems to sort the data correctly, but it places results from the bottom of the sheet upwards. Is there a fairly simple way to determine how many rows are being created, and use that number as a dimension for my sort range? Thanks for all of the help! – drumtechjp Oct 29 '15 at 21:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.