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

Question

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

Answer 1

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