How to prefill Google form checkboxes?

Question

I have looked at the question "Is it possible to 'prefill' a google form using data from a google spreadsheet?" and the code provided in the answer (thanks Mogsdad) works well for text type Google form questions. My question is: Is it possible to prefill a checkbox type Google form question?

For example, if I have an existing spreadsheet with an entry for "Names" and one of the entries is "Fred, Barney" would it be possible, via coding, to have a form prefill with the checkboxes ticked for "Fred" and "Barney" under a "Names" checkbox type Google form question?

Thanks, Greig

Solution 1

The basic pattern for each response can be repeated for most other types. For example, this works for multiple choice:

            item = items[i].asMultipleChoiceItem();
            var respItem = item.createResponse(resp);

However, a checkbox can be tricky, as it may have one item, multiple items, and even "other" responses. When the response is recorded to your spreadsheet, it will appear as a comma-separated string; when received in a form submission event (e.g. in a trigger function), we get an array (... where all responses are in the first item in the array, in a comma-separated string). The createResponse() method for a checkboxItem expects an array of valid choices... so we can provide that with a little javascript magic:

            item = items[i].asCheckboxItem();
            // Response is a CSV string, need array
            var respArray = resp.split(/ *, */);
            var respItem = item.createResponse(respArray);

EDIT: Google has a bug with CheckboxItems and MultipleChoiceItems, when used with "Other" options enabled. Those "other" options are allowed, but get rendered incorrectly in the pre-filled URL, and as a result they don't appear in the displayed form. Please see and star Issue 4454.

Here's an updated version of the function from Is it possible to 'prefill' a google form using data from a google spreadsheet?, updated to handle lists, multiple choice, and checkbox responses. This version is more general, it can adapt to the headings in your spreadsheet. BONUS: if you add a column labeled "Prefilled URL", the script will write its generated URLs there.

/**
 * Use Form API to generate pre-filled form URLs
 * 
 * https://stackoverflow.com/a/26395487/1677912
 */
function evenBetterBuildUrls() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Form Responses 1");
  var data = ss.getDataRange().getValues();  // Data for pre-fill
  var headers = data[0];                     // Sheet headers == form titles (questions)

  var formUrl = ss.getFormUrl();             // Use form attached to sheet
  var form = FormApp.openByUrl(formUrl);
  var items = form.getItems();
  var urlCol = headers.indexOf("Prefilled URL");   // If there is a column labeled this way, we'll update it

  // Skip headers, then build URLs for each row in Sheet1.
  for (var row = 1; row < data.length; row++ ) {
    Logger.log("Generating pre-filled URL from spreadsheet for row="+row);
    // build a response from spreadsheet info.
    var response = form.createResponse();
    for (var i=0; i<items.length; i++) {
      var ques = items[i].getTitle();           // Get text of question for item
      var quesCol = headers.indexOf(ques);      // Get col index that contains this question
      var resp = ques ? data[row][quesCol] : "";
      var type = items[i].getType().toString();
      Logger.log("Question='"+ques+"', resp='"+resp+"' type:"+type);
      // Need to treat every type of answer as its specific type.
      switch (items[i].getType()) {
        case FormApp.ItemType.TEXT:
          var item = items[i].asTextItem();
          break;
        case FormApp.ItemType.PARAGRAPH_TEXT: 
          item = items[i].asParagraphTextItem();
          break;
        case FormApp.ItemType.LIST:
          item = items[i].asListItem();
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item = items[i].asMultipleChoiceItem();
          break;
        case FormApp.ItemType.CHECKBOX:
          item = items[i].asCheckboxItem();
          // In a form submission event, resp is an array, containing CSV strings. Join into 1 string.
          // In spreadsheet, just CSV string. Convert to array of separate choices, ready for createResponse().
          if (typeof resp !== 'string')
            resp = resp.join(',');      // Convert array to CSV
          resp = resp.split(/ *, */);   // Convert CSV to array
          break;
        case FormApp.ItemType.DATE:
          item = items[i].asDateItem();
          resp = new Date( resp );
          resp.setDate(resp.getDate()+1);
          break;
        case FormApp.ItemType.DATETIME:
          item = items[i].asDateTimeItem();
          resp = new Date( resp );
          break;
        default:
          item = null;  // Not handling DURATION, GRID, IMAGE, PAGE_BREAK, SCALE, SECTION_HEADER, TIME
          break;
      }
      // Add this answer to our pre-filled URL
      if (item) {
      // Checking if there is any value
        if(resp[0].length != 0){
          var respItem = item.createResponse(resp);
          response.withItemResponse(respItem);
        }
      }
      // else if we have any other type of response, we'll skip it
      else Logger.log("Skipping i="+i+", question="+ques+" type:"+type);
    }
    // Generate the pre-filled URL for this row
    var editResponseUrl = response.toPrefilledUrl();
    // If there is a "Prefilled URL" column, update it
    if (urlCol >= 0) {
      var urlRange = sheet.getRange(row+1,urlCol+1).setValue(editResponseUrl);
    }
  }
};

Solution 2

When you edit the form, fill in the values that you want to be pre-filled.

Then get the pre-filled URL

Comments

[Greig]

Thanks for your response Mogsdad. I have tried the code by running it as a script on the spreadsheet and got an error: "Invalid response submitted to item" applying to line 64 (var respItem = item.createResponse(resp);) which is two lines below the comment "// Add this answer to our pre-filled URL". Any thoughts?

[Greig]

I have tried this on a smaller spreadsheet similar to the example given by Mogsdad and it worked fine. It would seem the problem is in my existing form and spreadsheet. I have an entry in the csv cell that matches to a checkbox but no corresponding check box for it to select when it is going into the form.

[Mogsdad]

@Greig - the documentation for CheckboxItem.createResponse() explains this behavior; it "Throws an exception if any value does not match a valid choice for this item", unless your question includes "Other" options.

[Mogsdad]

Added note about a google bug with CheckboxItem and MultipleChoiceItem, related to "Other".

[Greig]

Is there a way of skipping blank entries?

[Mogsdad]

if (item && resp !== '')...

[Lea Cohen]

Could you include your script in your answer? It would make your answer much better.

[Kartik Domadiya]

Is it possible to make an HTTP call and get values from there instead of spreadsheet and fill up the form ?

[Greig]

@Kartik - are you referring to Pre-populate form answers?

[Greig]

@Mogsdad - I have dates in my pre-filled data and when looking at the logs the resp variable for a date is showing up in the format "Day MMM DD YYY HH:mm:ss Time Zone". I read elsewhere that the date should be in YYYY-MM-DD format. When I toggled out the 'resp = new Date( resp );' the log didn't change. Does the date format need adjusting in this code or am I just confused (as usual)? Thanks.

[Mogsdad]

@Greig: Slightly different topic; that other question is constructing a pre-filled URL as a string, while this one is using the Forms service to generate the same from JavaScript objects. There's no need to format the date here, it gets taken care of for you.

[Greig]

@Mogsdad - Thanks Mogsdad. Indeed it does. Regardless of how the date looked in the logs it worked just fine when it came to prefilling. (I wasn't seeing this as I had yet another issue with a null response in a checkbox field - which has nothing to do with the code provided).

[Rubén]

Mogsdad: I'm adapting the evenBetterBuildUrls() and in the process I found some changes that could improve it by making it more easy to adopt by newbies. I.E. A very minor change: 'var data = ss.getDataRange().getValues();' could (should?) be changed to 'var data = sheet.getDataRange().getValues();' I'll be bold and edit your code. Other changes that I'm doing is to add Duration, Time and Grid, adding a "switch" and some lines of code to add the capability of import the responses to the form. Also I'm translating the comments to Spanish. P.S. Obviously I will give you the proper attribution :)

[Rubén]

Ups, the change is too small and SO doesn't allow those.