Constructing an array for Google Forms .createResponse in a GRID item type when null values are pres-CodePudding

I am getting values from a sheet and needing to convert the values into an array that will then be accepted by .createResponse() of Google Forms for a GRID item type. In the spreadsheet, the values for cell[0] = NYC, cell[1] = LA, and cell[2] = Dallas. The following function will work when there is data in each of the cells. However, if one of the cells is blank, then the function converts the blank entries into null. I am using this same technique of converting blanks to null for CHECKBOX_GRID items in other functions and this same technique has been stated as the solution for getting GRID items to be formatted correctly. However, the function errors out at the var itemResponse line with the error of "Exception: Invalid response submitted to grid question." I know that this means the data in the response array does not match the possible choices in the GRID item type. Anyone know why this function is not working properly?

function stackExp(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  var form = FormApp.openByUrl(ss.getFormUrl());
  var countMC = form.getItems()[0].asGridItem().getRows().length;
  var arr = ss.getSheetByName(dataSheetName).getRange(4, 2, 1, countMC).getValues()[0].map(e => e.trim());

  for(j = 0; j < arr.length; j  ){
    if(arr[j] == '' || arr[j] == ""){
      arr[j] = null;
    }
  }
  
  var itemResponse = form.getItems()[0].asGridItem().createResponse(arr); //Errors here
  form.createResponse().withItemResponse(itemResponse).submit();
}

CodePudding user response：

I believe your current situation and your goal are as follows.

You have a Google Form including a GridItem to the 1st item.
The GridItem has 3 rows Pizza,Burgers,Tacos and 4 columns which are Chicago,NY,LA,Dallas in order.
You want to retrieve the values from the cells "B4:D4" of dataSheetName sheet, and want to use the values as the response of this item.

Issue:

For example, when this form item is manually submitted including an unchecked button using a script, ["NY", null, "Chicago"] is obtained. But, when ["NY", null, "Chicago"] is used to the submitted value using a script, an error like Exception: Invalid response submitted to grid question..
- This situation has already been reported as a bug to the Google issue tracker. Ref
On March 16, 2022 (yesterday), Google Forms API has released as the alpha version. But in the current stage, the form response cannot be submitted using Forms API. I thought that this method might be able to be added in the future update.

Workaround:

In the current workaround for achieving your goal, I would like to propose the following workaround. In this workaround, the values are directly submitted to the Form using UrlFetchApp.

function sample() {
  const ids = ["entry.###", "entry.###", "entry.###"]; // Please set each entry ID.
  var dataSheetName = "Sheet1"; // Please set your sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const form = FormApp.openByUrl(ss.getFormUrl());
  const countMC = form.getItems()[0].asGridItem().getRows().length;
  const arr = ss.getSheetByName(dataSheetName).getRange(4, 2, 1, countMC).getValues()[0].map((e, i) => e ? `${ids[i]}=${e.trim()}` : null).filter(e => e);
  const url = `https://docs.google.com/forms/d/${form.getId()}/formResponse`;
  const endpoint = `${url}?${arr.join("&")}`;
  const res = UrlFetchApp.fetch(endpoint, { method: "post" });
  console.log(res.getContentText())
}

In order to retrieve const ids = ["entry.###", "entry.###", "entry.###"];, you can use the following script. Before you use this script, please submit the Form by 3 checked button. By this, 3 entry IDs can be retrieved.

    function sample2() {
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const form = FormApp.openByUrl(ss.getFormUrl());
      const prefilledUrl = form.getResponses().pop().toPrefilledUrl();
      console.log(prefilledUrl)
    }

Issue:

Workaround:

References: