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.
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();
by
var data = JobList.getDataRange().offset(1,0);
would do it.
Best Answer
Your formula is correct except you should be using straight double quotes (
" "
) instead of curly double (“ ”
) quotes.This means your formula should be:
Straight
" "
VS curly“ ”
Please read more about quotes on straight and curly quotes.
Curly quotes are also reffered as smart quotes.
You most likely created the formula in a program like Microsoft Word or an app like Google Docs and then copied it on to the cell.
When creating a formula, create it within a cell by double clicking on it.
I did not edit your post so you can compare the difference.