I have a private google form I need to fill out every day, so I would like to write a script to authenticate me using my google credentials, and then fill out some of the form programatically, is there an API for that? What is a recommended approach for this, without having edit access to the form?
Google-apps-script – Fill out google form using an api
google-apps-scriptgoogle-forms
Related Solutions
Since you asked about "an alternate way to solve this problem", I suggest using a required checkbox question with only one option: namely, the text of terms and conditions. This is more in line with the industry practice which requires a user to explicitly check a box next to T&C. The full text of this option will be included in the response, and can be emailed to the user by a script running on form submission.
One drawback is that options do not support line breaks. But one can separate sections with some ASCII art: I used 58 =
signs below. This sort of formatting, replacing line/paragraph breaks with ===, can be done automatically if the document is long; the result is then copy-pasted into an option. The email-sending script can reformat the text for email, say, by replacement str.replace(/={2,}/g, '\n')
.
Using a part of Stack Exchange terms of service for illustration:
I don't know what the maximal length of option text it, but it can be long (multiple screens to scroll).
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
I know this answer is super-late, but I just found your question via google since I had the same thought. The answer is query parameters. According to this article about using merge tags in Mailchimp to auto-populate google forms, if you pass query parameters after the form URL in the format
form.url?key=value
, e.g.https://docs.google.com/forms/path/to/stub?<entry.###########>=<url-encoded prefill value>
. After generating the form, inspect the field you want to prefill. Itsname
attribute will be in the format<entry.###########>
, where.###########>
is some long number. Fill in<url-encoded prefill value>
with whatever you want to go into that form, as long as it is properly URL-encoded. Hope that helps anyone else with the same question as us.