How to put the optional values of Checkbox and Multiple Choice in the prefilled form in Google Apps Script?

Question

I'm using Google Forms as a tool to automate my workflow and generate documents from spreadsheet data.
To avoid typing all data twice, I'm trying to generate a form link with prefilled responses.
I can do it with this code (thanks to @Mogsdad previous response) and it works well, except when I have an optional "other" field in Checkbox and Multiple Choice optional "other" field. In this case, I'm not getting this optional value, which is just what I want, so the user won't have to retype it.

The code I'm using is this:

var ss = SpreadsheetApp.getActive(); 
var sheet = SpreadsheetApp.getActiveSheet();
var formUrl = ss.getFormUrl(); // Use a form attached to sheet 
var form = FormApp.openByUrl(formUrl); 
var items = form.getItems();
var cell_content = "Some string value in the spreadhseet"; 
 for (var i = 0; i < items.length; i++ ) {    
       var id_item = items[i].getId();
       var type_item = items[i].getType();
var item_of_form = form.getItemById(id_item);
if (type_item == "MULTIPLE_CHOICE"){
          formItem = item_of_form.asMultipleChoiceItem(); 
          if (formItem.hasOtherOption()){
            formItem.showOtherOption(true);                                   
          }
          var response = formItem.createResponse([cell_content]);
          formResponse.withItemResponse(response);    
     }
var url = formResponse.toPrefilledUrl();
Logger.log(url);

All works fine (with Text and Paragraph responses and even with Checkbox and Multiple Choice (except with its optional value).

How can I put this optional values of checkbox and Multiple Choice in the prefilled form?

In your attempt to provide a [mcve], I would recommend that you provide a complete executable function that does not contain any undefined variables. — Cooper, Jul 28 '19 at 18:08

score 1 · Accepted Answer · answered Jul 30 '19 at 09:01

There does not seem to be a way to directly state that you want to set the response to "other". However, you can do it manually, and it shows how the pre-filled URL is crafted. I wrote some code (below) that will grab a list of items in a sheet (range B2:B4) which are supposed to be the responses to be pre-filled in "other.

The URLs will be logged, but you can use them how you please. Also, please keep in mind that this is only for the "other" question, the list where you get your data will pre-fill "other" regardless of what it says.

Try the following code:

function myFunction() {
  var ss = SpreadsheetApp.getActive();

  var formUrl = ss.getFormUrl(); // Use a form attached to sheet 
  var form = FormApp.openByUrl(formUrl); 
  var items = form.getItems();
  var formItem = items[0].asMultipleChoiceItem(); // The question 

  var id = formItem.getId().toString()
  var otherResponse = ss.getRange("B2:B4").getValues();

  for (var i = 0; i < otherResponse.length; i++){
    var string = "__other_option__&entry." + id + ".other_option_response=" + otherResponse[i];
    var other = formItem.createResponse(string);

    var respWithOther = decodeURIComponent(form.createResponse().withItemResponse(other).toPrefilledUrl());
    var firstId = respWithOther.substring(117, 127);

    var finalUrl = respWithOther.substring(0, 151) + firstId + respWithOther.substring(161, respWithOther.length);
    Logger.log(finalUrl);
  }
}

How to put the optional values of Checkbox and Multiple Choice in the prefilled form in Google Apps Script?

1 Answers1