I am getting values from a sheet and needing to convert the values into a 2D array that will then be accepted by .createResponse() of Google Forms. In the spreadsheet, the values for cell[0] = Paris, NYC and cell[1] = LA. I have tried this function:

function testFunction1() {
  const ss = SpreadsheetApp.getActiveSheet();
  const form = FormApp.openByUrl(ss.getFormUrl());
  var arr = [];
  for(i = 0; i<2; i  ){
    var cell = ss.getRange(2,i 2).getValue();
    cell = cell.split(',');
    arr.push(cell);
  }
  var newRes = form
    .getItems()[0]
    .asCheckboxGridItem()
    .createResponse(arr);
  form.createResponse().withItemResponse(newRes).submit();
}

When I split each cell, I get: split cell[0] = ['Paris', 'NYC'] split cell[1] = ['LA']

After pushing the items into arr, I get: arr = [ ['Paris', 'NYC'], ['LA'] ]

This looks like a 2D array but then .createResponse(arr) gives the error of "Exception: Invalid response submitted to checkbox grid question."

If I manually enter [ ['Paris', 'NYC'], ['LA'] ] into the .createResponse like this:

function testFunction2() {
  const ss = SpreadsheetApp.getActiveSheet();
  const form = FormApp.openByUrl(ss.getFormUrl());
  var newRes = form
    .getItems()[0]
    .asCheckboxGridItem()
    .createResponse([ ['Paris','NYC'], ['LA'] ]);
  form.createResponse().withItemResponse(newRes).submit();
}

then there is no error and the response is added to the Form correctly.

I have also tried:

function testFunction1() {
  const ss = SpreadsheetApp.getActiveSheet();
  const form = FormApp.openByUrl(ss.getFormUrl());
  var arr = [[]];
  for(i = 0; i<2; i  ){
    var cell = ss.getRange(2,i 2).getValue();
    cell = cell.split(',');
    arr[0][i] = cell;
    console.log(cell);
  }
  var cell = arr;
  console.log(cell[0]);
  var newRes = form
    .getItems()[0]
    .asCheckboxGridItem()
    .createResponse(cell[0]);
  form.createResponse().withItemResponse(newRes).submit();
}

This throws the error of "Exception: Invalid response submitted to checkbox grid question." at the .createResponse(cell[0]).

Also if the .createResponse(cell[0]) is changed to .createResponse(cell), then the following error is received, "Exception: The parameters (number[]) don't match the method signature for FormApp.CheckboxGridItem.createResponse."

I also used typeof of the manually typed item, cell values, and resulting arrays and they all come up as object. What am I doing wrong?

Adam

CodePudding user response：

From the situation that .createResponse([ ['Paris','NYC'], ['LA'] ]); works and cell[0] = Paris, NYC and cell[1] = LA in your question, I'm worried that each value of arr might have a space. This is just my guess. So in your script, how about the following modification?

From:

cell = cell.split(',');

To:

cell = cell.split(',').map(e => e.trim());

or,

From:

var arr = [];
for(i = 0; i<2; i  ){
  var cell = ss.getRange(2,i 2).getValue();
  cell = cell.split(',');
  arr.push(cell);
}

To:

var arr = ss.getRange(2, 2, 1, 2).getValues()[0].map(c => c.split(",").map(e => e.trim()));

References:

• map()
• trim()

CodePudding user response：

This works for MULTIPLE_CHOICE

function testFunction1() {
  const ss = SpreadsheetApp.getActiveSheet();
  const form = FormApp.openByUrl('________your form url__________');
  var values = ss.getRange(2, 2, 1, 2).getValues().flat();
  var type = form.getItems()[0].getType()
  if (type == 'MULTIPLE_CHOICE') {
    var newRes = form
      .getItems()[0]
      .asMultipleChoiceItem()
      .setChoiceValues(values);
  }
}