Dynamically update a Google Select from List form from a column in a spreadsheet

Question

I am trying to customize a Google form that will create a “ select from list ” question , with each parameter being displayed from the rows in a specific column.

I tried this code , but it did not work for me at all.

The code I have is below. I can create the desired form question with data from only one row in a specific column (in this case, the last row of the column).

If someone can help by showing me the right direction on how to assign each selected row to only one question option. Also, if this is some way to dynamically update question parameters every time the spreadsheet is updated.

var idColumn = 1; // ID of the selected column

//gets document ID from spreadsheet 
//(not 100% sure the role of all of these lines, have worked it out from other examples of code online)
function spreadSheetGetter() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var lr = rows.getLastRow();  

  var docId = sheet.getRange(lr,idColumn,1,1).getValue(); //gets the data from the last row in selected column

fillFormData(docId);
}


//fills for with document ID
function fillFormData(docId) {
var form = FormApp.openById('MY FORMS ID');
var item = form.addListItem();

item.setTitle('Select Existing Customer') //creates the choose from list question
     .setChoices([
         item.createChoice(docId), //data from row 1
         item.createChoice(docId) //data from row 2 etc...
       ])
}

Answer 1

:

var docId = sheet.getRange(lr,idColumn,1,1).getValue();

. , , getValues() ('s'), . numRows ( ) lr. , 2, . :

getRange(row, column, numRows, numColumns)

• row -
• -
• numRows -
• numColumns -

, :

var docId = sheet.getRange(2,idColumn,lr,1).getValues();

, . 2, 2.

, , , , setChoices(choices), . . , .

var thisValue = "";
var arrayOfItems = [];
var newItem = "";

for (var i=0;i<docId.length;i++) {
  thisValue = docId[i][0];
  newItem = "item.createChoice('" + thisValue + "')";
  arrayOfItems.push(newItem);
};

:

//fills item with document ID
function fillFormData(docId) {
  var form = FormApp.openById('MY FORMS ID');
  var item = form.addListItem();

  var thisValue = "";
  var arrayOfItems = [];
  var newItem = "";

  for (var i=0;i<docId.length;i++) {
    thisValue = docId[i][0];
    Logger.log('thisValue: ' + thisValue);
    newItem = item.createChoice(thisValue);
    arrayOfItems.push(newItem);
  };

  item.setTitle('Select Existing Customer') //creates the choose from list question
     .setChoices(arrayOfItems)
};

Answer 2

, , , . Google, FormRanger, " " . , , .

FormRanger. .

https://docs.google.com/document/d/1qidnsrTShKU9kPnYz4nubHs2cK9yWnQ2z_XBoqgdIhs/edit

Answer 3

, , , , , , , . , Allnatural ChrsF , (, , ), , .

, , :

//Here you set the options for the question on your form you want to dinamically change and which named range will populate it
var LIST_DATA = [{formFieldTitle:"Who your tutor", namedRange:"reg_opcoes"}]

//Inserts a menu entry that allows you to run the function to update the options of the form
function onOpen(e){
  var menuEntries = [];
  menuEntries.push({name: "Update Lists", functionName: "updateLists"});
  SpreadsheetApp.getActiveSpreadsheet().addMenu("List Updater", menuEntries)
}

//The update function itself, it will run for as many options as you set in the LIST_DATA list
function updateLists() {
  var form = FormApp.openById('1Pl3i5Qr8Kq5C2UbBUqA0iZCsmaHXum5eWR1RfIaEL6g'); //Insert your form ID here
  var items = form.getItems();
  for (var i = 0; i < items.length; i += 1){
    for (var j = 0; j < LIST_DATA.length; j+=1) {
      var item = items[i]
      if (item.getTitle() === LIST_DATA[j].formFieldTitle){ //Here the titles of the questions are called
        updateListChoices(item.asListItem(), LIST_DATA[j].namedRange); //Here the range that populates que question with options is called
        break;
      }
    }
  }
}

//Function that actually gets the values from the range you defined
function updateListChoices(item, sheetName){
  var data = (SpreadsheetApp.getActiveSpreadsheet()
              /* 
                 If you want, you might change the option here to .getDataRange() and work with defined ranges.
                 For that, yoiu should also place a .getSheetName() before it, so that'll know of what interval you're talking about
              */
              .getRange(sheetName) 
              .getValues());
  var choices = [];
  for (var i = 0; i < data.length; i+=1){
    choices.push(item.createChoice(data[i][0]));
  }
  item.setChoices(choices);
}

, , "updateLists" , , .

, . , .

Answer 4

, , :

var sheetId = '<YOUR_SHEET_ID>';
var sheetName = 'sheet_1';
var column = 'C';
var formId = '<YOUR_FORM_ID>';
var listItemId = 12345678;

function spreadSheetGetter() {
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var docId = sheet.getRange(sheetName+"!"+column+"2:"+column+numRows).getValues();
  fillFormData([].concat.apply([],docId));
}

function fillFormData(docId) {
  var form = FormApp.openById(formId);
  var item = form.getItemById(listItemId).asListItem();
  item.setChoiceValues(docId);
};

Sheets Form URL, ListItem :

var form = FormApp.getActiveForm();
var items = form.getItems();
for (var i in items) { 
  Logger.log(items[i].getTitle() + ': ' + items[i].getId());
}