Google Sheets to Forms - My options list in sheets overwrites the same position on the form response

Question

Newcomer here. First app script try!

What I want to do is quite common. To populate answers in a specific form question with options in google sheets. I've followed a few tutorials on how to do it. And got to the part where the script recognizes the cell information, and sends to the specific form.

The problem is that it keeps overwriting response 1. I can see the form updating it response with all the options I have on my sheet. And I can't understand why! I've tried setChoices, setChoiceValues, createChoice. The ones that work keep doing the same thing.

Since I'm here, might as well ask another thing, my next step is to filter the options on my sheet with date and another cell when it has a specific value. Example: This sheet is constantly updated with a calendar, and when the job is completed, I have a column that I mark as "completed". I want to filter the so that the options that I send to the form are only with rows that have 2 different conditionals: 1- to show only rows created until today (they can't complete the job before it's schedule), 2- Only cells from that specific row that it is not "completed".

This is my code.

  var ssID = ""; //spreadsheetId
  var ss = SpreadsheetApp.openById(ssID).getSheetByName(""); // sheet name
  var lr = ss.getLastRow();
  var formId = ""; //form
  var form = FormApp.openById(formId);
  var id = form.getItemById(2117941847);

function getDataFromSheets() {
 
  var options = ss.getRange(2,2, lr, 1)
                  .getValues();
  
    for(var i = 0;i<options.length;i++){

    var option = options[i];
  
  id.asListItem().setChoiceValues(option);
Logger.log(option)
  }
}

score 0 · Answer 1 · answered Aug 03 '21 at 23:58

function getDataFromSheets() {
  var ssID = ""; //spreadsheetId
  var ss = SpreadsheetApp.openById(ssID).getSheetByName("");
  var lr = ss.getLastRow();
  var formId = ""; //form
  var form = FormApp.openById(formId);
  var id = form.getItemById("2117941847");
  var options = ss.getRange(2, 2, lr - 1, 1).getValues().flat();
  id.asListItem().setChoiceValues(options);
}

Google Sheets to Forms - My options list in sheets overwrites the same position on the form response

1 Answers1