Is it possible to "fill out" a Google form using data from a google spreadsheet?

Question

I am looking for a way to “pre-populate” 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 questions will be “populated” with unique data from the existing google spreadsheet. The data will be unique based on their email address in the existing spreadsheet.

EXAMPLE OF SOURCES OF SOURCES

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 - pre-populated with data (Mike Jones) from google spreadsheet.

Question 2 - populated with data (May 9, 1975) from the google spreadsheet.

Question 3 - empty (waiting for user response)

Question 4 - empty (waiting for user response)

---

FORM EXAMPLE TWO

Question 1 - pre-populated with data (Jim Smith) from google spreadsheet.

Question 2 - pre-populated with data (April 19, 1985) from google spreadsheet.

Question 3 - empty (waiting for user response)

Question 4 - empty (waiting for user response)

---

Does anyone know if this can be done? If so, any help or referral will be STRONGLY appreciated.

Thank you in advance! Todd

Answer 1

You can create a pre-populated form URL from the form editor, as described in the documentation for Disk Forms . You will end up with a URL, 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 the identifier 726721210 , and question 2 - “Birthday” - 787184751 . Questions 3 and 4 are empty.

You can create a pre-populated URL by adapting the one provided through the user interface to be a template, for example:

 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 quite effective - you can email a pre-filled URL to each person and they will already have questions.

betterBuildUrls ()

Instead of creating our template using brute force, we can combine it together programmatically. This will have the advantage that we can reuse the code without thinking about changing the template.

Each question in the form is an element. For this example, suppose a form has only 4 questions, as you described them. Element [0] - "Name", [1] - "Birthday", etc.

We can create a form response that we will not send - instead, we will partially fill out the form, only to get the pre-filled form URL. Since the Forms API understands the data types of each element, we can avoid manipulating the string format of dates and other types, which simplifies our code somewhat.

(EDIT: Is there a more general version of this in the checkboxes of a Google form on a form? )

 /** * 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 element 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 transformation processing method.

When reading dates from a spreadsheet, you will get a Date javascript object if the data format is recognized as a date. (Your example is not recognized, so instead of May 9th 1975 you can 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]); };