Google-sheets – Rearranging Google form data in a new sheet

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

Best Answer

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.

Related Topic