I'm looking for a way to 'pre fill' a google form with specific data from a google spreadsheet. The form will have the same 'standard' questions for everyone, but the data in the first two question will be 'prefilled' with unique data from an existing google spreadsheet. The data will be unique based on their email address in the existing spreadsheet.
SOURCE SPREADSHEET EXAMPLE
Col 1 Col 2 Col 3
email name birthday
@mike Mike Jones May 9th 1975
@jim Jim Smith April 19th 1985
FORM EXAMPLE ONE
Question 1 - prefilled with data (Mike Jones) from a google spreadsheet.
Question 2 - prefilled with data (May 9th 1975) from a google spreadsheet.
Question 3 - blank (awaiting user response)
Question 4 - blank (awaiting user response)
FORM EXAMPLE TWO
Question 1 - prefilled with data (Jim Smith) from a google spreadsheet.
Question 2 - prefilled with data (April 19th 1985) from a google spreadsheet.
Question 3 - blank (awaiting user response)
Question 4 - blank (awaiting user response)
Does anyone know if this can be done? If yes, any help or direction will be GREATLY appreciated.
Thank you in advance!
Todd
You can create a pre-filled form URL from within the Form Editor, as described in the documentation for Drive Forms. You'll end up with a URL like this, for example:
https://docs.google.com/forms/d/--form-id--/viewform?entry.726721210=Mike+Jones&entry.787184751=1975-05-09&entry.1381372492&entry.960923899
In this example, question 1, "Name", has an ID of 726721210
, while question 2, "Birthday" is 787184751
. Questions 3 and 4 are blank.
You could generate the pre-filled URL by adapting the one provided through the UI to be a template, like this:
function buildUrls() {
var template = "https://docs.google.com/forms/d/--form-id--/viewform?entry.726721210=##Name##&entry.787184751=##Birthday##&entry.1381372492&entry.960923899";
var ss = SpreadsheetApp.getActive().getSheetByName("Sheet1"); // Email, Name, Birthday
var data = ss.getDataRange().getValues();
// Skip headers, then build URLs for each row in Sheet1.
for (var i = 1; i < data.length; i++ ) {
var url = template.replace('##Name##',escape(data[i][1]))
.replace('##Birthday##',data[i][2].yyyymmdd()); // see yyyymmdd below
Logger.log(url); // You could do something more useful here.
}
};
This is effective enough - you could email the pre-filled URL to each person, and they'd have some questions already filled in.
Instead of creating our template using brute force, we can piece it together programmatically. This will have the advantage that we can re-use the code without needing to remember to change the template.
Each question in a form is an item. For this example, let's assume the form has only 4 questions, as you've described them. Item [0]
is "Name", [1]
is "Birthday", and so on.
We can create a form response, which we won't submit - instead, we'll partially complete the form, only to get the pre-filled form URL. Since the Forms API understands the data types of each item, we can avoid manipulating the string format of dates and other types, which simplifies our code somewhat.
(EDIT: There's a more general version of this in How to prefill Google form checkboxes?)
/**
* Use Form API to generate pre-filled form URLs
*/
function betterBuildUrls() {
var ss = SpreadsheetApp.getActive();
var sheet = ss.getSheetByName("Sheet1");
var data = ss.getDataRange().getValues(); // Data for pre-fill
var formUrl = ss.getFormUrl(); // Use form attached to sheet
var form = FormApp.openByUrl(formUrl);
var items = form.getItems();
// Skip headers, then build URLs for each row in Sheet1.
for (var i = 1; i < data.length; i++ ) {
// Create a form response object, and prefill it
var formResponse = form.createResponse();
// Prefill Name
var formItem = items[0].asTextItem();
var response = formItem.createResponse(data[i][1]);
formResponse.withItemResponse(response);
// Prefill Birthday
formItem = items[1].asDateItem();
response = formItem.createResponse(data[i][2]);
formResponse.withItemResponse(response);
// Get prefilled form URL
var url = formResponse.toPrefilledUrl();
Logger.log(url); // You could do something more useful here.
}
};
Any date item in the pre-filled form URL is expected to be in this format: yyyy-mm-dd
. This helper function extends the Date object with a new method to handle the conversion.
When reading dates from a spreadsheet, you'll end up with a javascript Date object, as long as the format of the data is recognizable as a date. (Your example is not recognizable, so instead of May 9th 1975
you could use 5/9/1975
.)
// From http://blog.justin.kelly.org.au/simple-javascript-function-to-format-the-date-as-yyyy-mm-dd/
Date.prototype.yyyymmdd = function() {
var yyyy = this.getFullYear().toString();
var mm = (this.getMonth()+1).toString(); // getMonth() is zero-based
var dd = this.getDate().toString();
return yyyy + '-' + (mm[1]?mm:"0"+mm[0]) + '-' + (dd[1]?dd:"0"+dd[0]);
};