Contrary to what many believe, it is actually possible.
When you edit your form, at the very bottom there's a checkbox where you can allow editing of submitted response:
If you use a newer version of the Google Forms, you can click on the Gear icon on the top right and see the following page:
Now, what you need to do is manually submit score for each student. After every submission, you will be offered a link to edit the submission. You need to manually copy and save each link to allow future editing.
If you want to automatically record the edit response link, you can do so via Google Apps Script.
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.
Best Answer
As far as I am aware, this is not possible, given Excel's incompatibility with Google Apps Script.
There is an alternate and only slightly less efficient solution, though: each time you want to analyse the results of your Form, simply download the connected Google Sheet as an Excel spreadsheet, open the downloaded spreadsheet in Excel, and do your analyses as per usual. To download the Google Sheet as an Excel file, go to File -> Download as -> Excel spreadsheet (.xlsx) in Google Sheets.