Is there a way to create a Form that submits to a particular TAB of a Google Sheet?
Use Case:
We are using this for meeting pre-check-in. There's standard form. I'd like the results for each participant to go to their own TAB.
Google-sheets – How to send Google Form results to a particular sheet (“tab”) of a Spreadsheet
google sheetsgoogle-apps-scriptgoogle-forms
Related Solutions
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.
If you add one more parameter like this:
=HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSf7_ZUEXce6RTvHxzsIIMnUrOj2bcdIh9WSGG1V_0LHlM8QDg/viewform?entry.1079682270&entry.13269831&entry.106264669=="&A2&E2,"Reserve Now")
That will add your text.
You can also do an arrayformula:
=ARRAYFORMULA(IF(ISTEXT(A2:A),HYPERLINK("https://docs.google.com/forms/d/e/1FAIpQLSf7_ZUEXce6RTvHxzsIIMnUrOj2bcdIh9WSGG1V_0LHlM8QDg/viewform?entry.1079682270&entry.13269831&entry.106264669=="&A2:A&E2:E,"Reserve Now"),))
Best Answer
Short answer
Use Google Apps Script
Explanation
This isn't built-in function on Google Forms or Google Sheets, but we could create bounded script projects on forms and spreadsheets. Both have an onFormSubmit installable trigger that could be used to send the form submissions as soon as their occur to the desired tab.
Another alternative is to create a script to be triggered by other means, like from the Apps Script editor, just to mention one.
A third alternative could be to use an add-on, if there is already one that do this.