Is it possible to ‘prefill’ a google form using data from a google spreadsheet?

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

buildUrls()

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.

betterBuildUrls()

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.
  }
};

---

yymmdd Function

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]);
};