CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-forms
user3085360
user3085360

Reputation: 21

Editing checkbox choices in an existing Google form

I am trying to create a survey using Google forms and spreadsheet that has dynamic checkbox answers, such that if participant 1 fills out an answer in the 'other' text box, then that answer is then available as a checkbox for subsequent responders.

I am using the following code:

//a function to update Question 2's answer options
function updateChoices() {

    //open the existing survey form  
    var form = FormApp.openById('1DEcjGr6x9KrlxapgIkFxreW1F-2Vlj_yDDzLQUhcmgk');

    //retrieve existing Question 2
    var items = form.getItems();
    var question2 = items[1];

    //retrieve previously submitted responses from response spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    SpreadsheetApp.setActiveSheet(ss.getSheets()[1]);
    var numRows = ss.getLastRow()-1;
    var values = SpreadsheetApp.getActiveSheet().getRange(2,16,numRows,16).getValues();

    //declare a 1D array for existing survey answers
    var answersSoFar = new Array(numRows);

    //Pass the 2D array into the 1D array.
    for (i=0; i < numRows; i++){
    answersSoFar[i] = values[i][0];     
    }

    //update the choices for the question
    question2.setChoiceValues(answersSoFar);

}

I get the following error message:

TypeError: Cannot find function setChoiceValues in object Item. (line 57, file "Code")

even though the setChoiceValues method is in listed in the class item (line 57 is the last line of code).

I have also tried setting the choices individually using setChoices and createChoice in a for loop.

Upvotes: 2

Views: 3240

Answers (1)

Mogsdad
Mogsdad

Reputation: 45710

Class Item is a generic object that needs to be cast as a specific Item Type before item-specific methods can be invoked. See the preamble in the documentation for Class Item. You could simply do this:

question2.asCheckboxItem().setChoiceValues(answersSoFar);
          ^^^^^^^^^^^^^^^^

Spoiler alert: you've got some other issues in your function. If you want to be left alone to figure them out, stop reading now!

There's no need to call SpreadsheetApp.setActiveSheet(), as it has no benefit for this particular script.

Using ss.getSheets()[1] to get a handle on a specific sheet is unreliable, as the index is dependent on the ordering of sheets, which can change. If you need a specific sheet, it's better to refer to it by name. (Or by ID if you're using a method that takes a gridId parameter.)

What is the purpose of the values 16 in getRange(2,16,numRows,16).getValues();? That says the range starts at P2 and extends numRows down and 16 columns to the right. Otherwise, you appear to be intending to generate a list of all the previous responses to question 2, which should be in column 3, or C (allowing for a timestamp and question 1 in A..B).

This line, var answersSoFar = new Array(numRows);, creates an array with a single element in it, a Number, which is equal to the number of rows in the sheet. It does not create an array with numRows elements in it. Reference: JavaScript Arrays.

Looping over rows to retrieve cell values is effective, but can get ugly and hard to maintain. Consider that all the responses to a question would appear in the same row if we transposed the 2-d Array... recall high-school matrix mathematics. The transpose function from Google Spreadsheet Script - How to Transpose / Rotate Multi-dimensional Array? will help us out here.

Code

//a function to update Question 2's answer options
function updateChoices() {
  //open the existing survey form  
  var form = FormApp.openById('1oaL64gVahL1wpIBeLJeRKclOHnso8Ai8sOVW9akxNhs');

  //retrieve existing Question 2
  var items = form.getItems();
  //var type = items[1].getType() == FormApp.ItemType.CHECKBOX;  // If you want to check, uncomment this
  var question2 = items[1].asCheckboxItem();  // Specify item type

  //retrieve previously submitted responses from response spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 1");

  // Get all the answers to question 2, so far...
  var values = sheet.getDataRange().getValues().slice(1);  // All responses, headers sliced off
  var answersSoFar = transpose(values)[2];  // Question 2 responses

  //update the choices for the question
  question2.setChoiceValues(answersSoFar);
}

// See https://stackoverflow.com/a/16705104/1677912
function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

Upvotes: 3

Related Questions