The previous "answer" that I have been referred to does not solve this problem. I tried that technique before I posted here. That technique did not work and I received the same error as the one I'm receiving now.

I am trying to create a Google Apps Script that will allow me to use previous data from a Form and "resubmit" the data into a Form. The use case is that I will have teachers who change Form questions after they have received submissions from sections of their class. The change will cause the previous data to no longer be included in the Form response summary page or the individual responses. Instead the data is hidden from view and only accessible through downloading the csv of the responses or the data found in the linked Sheet (provided the teacher linked the Sheet before editing the Form). The current work around is to use the Form's Pre-Filled-URL feature and then create a URL for each row of data through the use of formulas in the Sheet. The teacher must then manually click on each link to enter the data back into the Form. This is not only time consuming but many of the teachers cannot create a formula that will create the individual url links.

To solve this, I'm creating a script that I can use with these teachers when this occurs. I have everything figured out except for the grid and time items. Here's a stripped down version of the script that applies only to a checkbox_grid question.

The data is in one column (B1:B) like this:

In the script, I've been able to create an array for each cell that looks like:

array[0] = [['C1'].['C2']]
array[1] = [['C1'],['']]
array[2] = [[''],['C2']]
array[3] = [['C1'].['C2']]

The following error is being returned:

Exception: Wrong number of responses: 2. Expected exactly: 1.

Also, while researching this further, I found the following in the developers site:

For GridItem questions, this returns a String[] array in which the answer at index n corresponds to the question at row n 1 in the grid. If a respondent did not answer a question in the grid, that answer is returned as ''.

For CheckboxGridItem questions, this returns a String[][] array in which the answers at row index n corresponds to the question at row n 1 in the checkbox grid. If a respondent did not answer a question in the grid, that answer is returned as ''.

What is the correct way to structure the array so that GAS will accept it?

Code found in sample file is here:

//Global Variables for Spreadsheet
const ss = SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName('Data');
var lastRow = dataSheet.getLastRow();
var lastCol = dataSheet.getLastColumn();
var sheetHeader = dataSheet.getRange('1:1');
var sheetTitles = sheetHeader.getValues();
var error = 'None';
var cellValue = [];

//Global Variables for Form
var form = FormApp.openByUrl(ss.getFormUrl());
var formResponses = form.getResponses();
var formCreate = form.createResponse();
var formItems = form.getItems();

//sendValues() calls the setResponses() to set the values of each form response.
//    The function then submits the forms and clears the responses.
//    Flush is used to make sure the order of the code is retained.
//------------------ New Variables -------------------------------
// r = row

function sendValues() {
  for (r = 2; r <= lastRow; r  ) {
    setResponses(r);
    //formCreate.submit();
    console.log('submitted');
    formCreate = form.createResponse();
    SpreadsheetApp.flush();
  }
}

//setResponses(r) sets the response for each cell of the data sheet.
//calls gridResponse(cell,i) if the question type is either a checkbox grid or multiple choice grid question.

//----------------------- New Variables ---------------------------
// c = column
// i = item response
// ssHeaderValue = Values of the Row 1 in the Data Sheet (dataSheet)
// cell = Value of each cell in dataSheet
// cellValue = Converts commas in cell variable to ;
// formItem = each item from all of the Form items
// fHeaderValue = Value of each "header" (Title) of each Form value
// itemAs = item set to correct type
// itemResponse = created response

function setResponses(r) {
  for (c = 2; c <= lastCol; c  ) {
    for (i = 0; i < formItems.length; i  ) {
      var ssHeaderValue = dataSheet.getRange(1, c).getValue();
      var itemType = formItems[i].getType();
      var cell = dataSheet.getRange(r, c).getValue();

      gridResponse(cell, i);

      var formItem = formItems[i];
      var fHeaderValue = formItem.getTitle();
      var itemResponse = formItem
        .asCheckboxGridItem()
        .createResponse(cellValue);
    }
    //ERROR HERE: formCreate.withItemResponse(itemResponse);
  }
}

//checkboxGridResponse(cell,i) makes an array of cellValue that can be used in CHECKBOX_GRID item.

//--------------------- New variables ------------------------------
// z = loop counter
// gridColumns = number of possible responses in the Form
// cellValueLength = number of responses in the cell data
// cellColumns = cell data split into separate values
// cellValue = value to be returned
// arr = temporary array for making cellValue into a 2D array

function gridResponse(cell, i) {
  var gridColumns = formItems[i].asCheckboxGridItem().getColumns();
  var cellValueLength = cell.split(',').length;
  var cellColumns = cell.split(',');

  for (z = 0; z < cellValueLength; z  ) {
    console.log(
      'cellColumns '  
        z  
        ' = '  
        cellColumns[z]  
        '; gridColumns '  
        z  
        ' = '  
        gridColumns[z]
    );
    var arr = [gridColumns[z] == cellColumns[z] ? gridColumns[z] : null];
    cellValue.push(arr);
  }

  console.log(cellValue);
  console.log('cellValue[0][0] = '   cellValue[0][0]);
  console.log('cellValue[0][1] = '   cellValue[0][1]);
  console.log('cellValue [1][0] = '   cellValue[1][0]);
  return cellValue;
}

CodePudding user response：

The structure of a response for a CheckboxGridItem is a 2D array:

• Outer array should contain row array
• Inner array should contain column elements
• The elements themselves should be the column labels

This sample script for a 1 item form submits a 2x2 CheckboxGridItem array choosing "Column 1" for both rows.

function myFunc16() {
  const form = FormApp.getActiveForm();
  const newRes = form
    .getItems()[0]
    .asCheckboxGridItem()
    .createResponse([['Column 1'], ['Column 1']]);
  form.createResponse().withItemResponse(newRes).submit();
}

To submit another response choosing

the array should be:

[
    null,         //Row1
    ['Column 1']  //Row2
]

Similarly, for

Array:

[
    ['Column 2'],
     null
]

If you insert a array

[
    ['Column 2']
]

for a 2x2 CheckboxGridItem, the error,

Exception: Wrong number of responses: 1. Expected exactly: 2.

is thrown. It means the array.length doesn't match the number of rows in the grid.

The structure of array can be found by manually submitting the 1 checkbox grid item form and examining the response programmatically using:

//Examine third response [2]
//First item's response [0]
console.log(form.getResponses()[2].getItemResponses()[0].getResponse())