Google-sheets – A private copy of a Google Sheet for each person allowed to access the master spreadsheet

How to automatically pull data between different Google Spreadsheets:

There is a function called ImportRange that allows you to pull data out of one sheet and into another. This can be really useful, if for example you have a spreadsheet that you are using to collaborate with others, and then somewhere along the line you want another person to be able to see some of the data in the sheet but not all of it. e.g. if you are using this to track student grades, you could have a master sheet that you and other tutors can see all of, you could then create a separate sheet for each student, and pull through only the data that refers to them (you then share that sheet with the student) and they have a live constantly updating record of what they have achieved etc.

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.