0

Using the solution provided here, I am trying to pre-fill and submit a Google Form. It works until it gets to a checkbox question - for example:

Question on form: Has this topic been discussed with someone? Possible answers: John, Harry, Alice

The answer I am attempting to pre-fill is "Alice" - but I receive the error message below:

Exception: The parameters (String) don't match the method signature for FormApp.CheckboxItem.createResponse. See code below. Any help is appreciated...

function readSpreadsheet() {
  // Adapted from https://stackoverflow.com/questions/65906603/is-it-possible-to-create-google-form-responses-with-data-from-google-sheets

var formid = 'formidgoeshere';
var ssid = 'ssidgoeshere';
var sheetname = 'answers'

  var sheet = SpreadsheetApp.openById(ssid).getSheetByName(sheetname);
  var range = sheet.getDataRange();
  var numRows = range.getNumRows();
  var values = range.getValues();
  var form = FormApp.openById(formid)


  var items = form.getItems();
  for (var i = 1; i < numRows; i++) {
    var value = values[i];
    Logger.log(value)
    var formResponse = form.createResponse();
    var k = 0;
    for (var j = 0; j < items.length; j++) {
      var item;
      switch (items[j].getType()) {
        case FormApp.ItemType.CHECKBOX:
          item = items[j].asCheckboxItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break;  
        case FormApp.ItemType.LIST:
          item = items[j].asListItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break; 
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item = items[j].asMultipleChoiceItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break; 
        case FormApp.ItemType.PARAGRAPH_TEXT:
          item = items[j].asParagraphTextItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break; 
        case FormApp.ItemType.TEXT:
          item = items[j].asTextItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break;         
        case FormApp.ItemType.CHECKBOX:
          item = items[j].asCheckboxItem();
          Logger.log("Item number " + j + " is titled: " + item.getTitle() );
          formResponse.withItemResponse(item.createResponse(value[k++]));
          break;          
        default:
          Logger.log("#" + (i + 1) + ":Do nothing for item " + j + " of type " + items[j].getType());
          continue;
      } 
      if(j==0){
        Logger.log("This item is the FORM NUMBER");        
      };
    }
    formResponse.submit();
  }
}
JohnTyler
  • 1
  • 1

1 Answers1

0

Try this:

function dataFromGSheets() {
  const ss = SpreadsheetApp.getActive();
  const sheet = ss.getSheetByName("Sheet1");
  const [header, ...data] = sheet.getDataRange().getDisplayValues();
  const choices = {};
  header.forEach((title, index) => {
   choices[title] = data.map(row => row[index]).filter(e => e) });
  return choices;
}

function populateGForms() {
  const Google_Form_ID = gobj.globals.editformid2;
  const googleForm = FormApp.openById(Google_Form_ID);
  const items = googleForm.getItems();
  const choices = getDataFromGoogleSheets();
  items.forEach(function (item) {
    const itemTitle = item.getTitle();
    if (itemTitle in choices) {
      const itemType = item.getType();
      switch (itemType) {
        case FormApp.ItemType.CHECKBOX:
          item.asCheckboxItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        case FormApp.ItemType.LIST:
          item.asListItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        case FormApp.ItemType.MULTIPLE_CHOICE:
          item.asMultipleChoiceItem().setChoiceValues([...new Set(choices[itemTitle])]);
          break;
        default:
          Logger.log("Ignore question", itemTitle);
      }
    }
  });
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Hi Cooper, it doesn't work for me - how would you incorporate form types ItemType.TEXT and PARAGRAPH_TEXT ? – JohnTyler Nov 15 '21 at 22:06