google-apps-scriptgoogle-sheetsgoogle-forms

How to prefill Google form checkboxes?


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

  • 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.

    screenshot

    /**
     * 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);
        }
      }
    };