CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
Jamie Gagne
Jamie Gagne

Reputation: 23

Create response with grid item is not working, what responses is it expecting?

I'm using a google form to keep track of progress on a jobsite, it updates a spreadsheet and then a 'Summary' sheet gets the latest information and colors the bg of certain cells depending on the progress. In the beginning of each row on the 'Summary' sheet I have a URL that links to the appropriate form. The URL is updated with each new entry to a prefilled URL so that once the form opens up, the data from the last entry is already entered. It was working great until I switched over to 'grid' style form question. I get an error when the prefilled URL function attempts to create a response from the grid item.

PS: Many thanks to u/Mogsdad for the 'evenBetterBuildUrl' function.

///////............Rebuild URL for Next Update...........
function evenBetterBuildUrls(ss, logSheet, curRoomData) {
  var lastRow = logSheet.getLastRow() - 1;
  var lastClmn = logSheet.getLastColumn(); 

  var data =  curRoomData;
  var headers = logSheet.getRange(1, 1, 1, lastClmn).getValues();

  var formUrl = ss.getFormUrl();             
  var form = FormApp.openByUrl(formUrl);

  var items = form.getItems();
  var urlCol = lastClmn;

var response = form.createResponse();
for (var i=0; i<items.length; i++) {

  var ques = items[i].getTitle();           // Get text of question for item
  if (ques == 'Notes:') {
    var quesCol = headers[0].indexOf(ques, i);
  } else {
    var quesCol = headers[0].indexOf(ques);      // Get col index that contains this question
  }
  var resp = ques ? data[quesCol] : "";
  var type = items[i].getType().toString();


  switch (items[i].getType()) {
    case FormApp.ItemType.TEXT:
      var item = items[i].asTextItem();
      break;
    case FormApp.ItemType.PARAGRAPH_TEXT: 
      item = items[i].asParagraphTextItem();
      resp = '';
      break;
    case FormApp.ItemType.LIST:
      item = items[i].asListItem();
      break;
    case FormApp.ItemType.MULTIPLE_CHOICE:
      item = items[i].asMultipleChoiceItem();
      break;
    case FormApp.ItemType.CHECKBOX:
      item = items[i].asCheckboxItem();
      // In a form submission event, resp is an array, containing CSV strings. Join into 1 string.
      // In spreadsheet, just CSV string. Convert to array of separate choices, ready for createResponse().
      if (typeof resp !== 'string')
        resp = resp.join(',');      // Convert array to CSV
      resp = resp.split(/ *, */);   // Convert CSV to array
      break;
    case FormApp.ItemType.DATE:
      var item = items[i].asDateItem();
      resp = new Date( resp );
      break;
    case FormApp.ItemType.DATETIME:
      item = items[i].asDateTimeItem();
      resp = new Date( resp );
      break;
    case FormApp.ItemType.SCALE:
      var item = items[i].asScaleItem();
      resp = parseInt(resp);
      break;
    case FormApp.ItemType.GRID:
      var item = items[i].asGridItem();                  

      if (typeof resp !== 'string') {
        resp = resp.join(',');      // Convert array to CSV
      }
      resp = resp.split(/ *, */);   // Convert CSV to array
      break;
    default:
      item = null;  // Not handling DURATION, IMAGE, PAGE_BREAK, SECTION_HEADER, TIME
      break;
  }
  // Add this answer to our pre-filled URL
  if (item) {
    var respItem = item.createResponse(resp);
    response.withItemResponse(respItem);
  }
  // else if we have any other type of response, we'll skip it
  else Logger.log("Skipping i="+i+", question="+ques+" type:"+type);
}
// Generate the pre-filled URL for this row
var editResponseUrl = response.toPrefilledUrl();
// If there is a "Prefilled URL" column, update it
if (urlCol >= 0) {
  var urlRange = logSheet.getRange(lastRow+1,urlCol).setValue(editResponseUrl);
}
  return editResponseUrl;

};

Upvotes: 1

Views: 1916

Answers (1)

user4758812
user4758812

Reputation: 26

I had similiar problem, i cant implement it in your code, but i wrote this, and it's working. GridItem expect string array with defined values, when some value is empty, it has to be null value. The code is example how i used it. Empty cell in spreadsheet is empty string type but you need null. Hope it helps.

var formItem = items[18].asGridItem();
var respArray = [row[28]!=="" ? row[28] : null, row[29]!=="" ? row[29] : null];
var response = formItem.createResponse(respArray);      
formResponse.withItemResponse(response);

Upvotes: 1

Related Questions