Populate Google Form With Short Question from Google Sheets (App Script)

Question

I want to create a google form that is used for my customers to inform me whenever they want to buy something from me.

The google form is simple, it is filled with short questions of list of on sale item that ask about the quantity of item that they want to buy. It is so that whenever my customers want to buy anything they will just have to fill or answer the questions with quantity that they want to buy for each item.

Problem is, the items constantly change and I always update it in a specific google sheet. It is very troubling that I have to update my google form every time the item list is changed.

Is there any way to auto-populate my google form question from my google sheet (perhaps with app script)?

The question is always "short answered" question type with qty bigger than 0 as data validation. It is not a multiple choice, not a dropdown or anything else.

The number of types of goods sold is also vary from around 20 to 40 items. So the number questions in google form can also change due to this fact.

For instance, this is a snapshot of the short question. Very brief and simple question.

Answer 1

This answer addresses the main requirements for updating textItems (Short Answer) in an existing Form.

ASSUMPTION: sale items begins with a FormApp.ItemType.PAGE_BREAK.
This is important because it creates a point where sale items can be deleted/created

PROCESS

• delete existing Sales items using deleteItems()
• create new sale items (with validation) from spreadsheet data using createNewSaleItems

---

function deleteItems() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var formUrl =  ss.getFormUrl()
  var form = FormApp.openByUrl(formUrl)
  // list page breaks
  var items = form.getItems()
  var index = items.findIndex(item => item.getType() === FormApp.ItemType.PAGE_BREAK);
  // Logger.log(items) // DEBUG
  // Logger.log("DEBUG: Total number of items: "+items.length)
  // Logger.log("DEBUG: index = "+index)
  
  // Last index = Item.length-1
  // index = zero-based, 
  // loop through form from bottom to top
  if (index !== -1) {  
    for (let i=(items.length-1);i>index;i--){
      var item = items[i]
      var title = item.getTitle()
      var type = item.getType()
      var id = item.getId()
      // Logger.log("DEBUG: i:"+i+", Title:"+title+", type:"+type+", ID:"+id+", Index:"+item.getIndex())
      form.deleteItem(i);
    } 
  }
}

function createNewSaleItems() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var newItemSheetName = "NewSaleItems"
  var sheet = ss.getSheetByName(newItemSheetName)

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

  // get sales items values
  var aLastRow = sheet.getRange("A2:A").getValues().filter(String).length;
  var itemNames = sheet.getRange(2,1,aLastRow,1).getValues().flat()
  // Logger.log("DEBUG: Item Names: Range = "+sheet.getRange(2,1,aLastRow,1).getA1Notation())
  // Logger.log(itemNames) // DEBUG
  // Logger.log(itemNames.length) // DEBUG

  // define Text validation
  var textValidation = FormApp.createTextValidation()
    .setHelpText('Quantity not greater than zero')
    .requireNumberGreaterThan(0)
    .build();

  for (var i=0;i<itemNames.length;i++){
    // Adds a text item with the title = spreadsheet Value
    var item = form.addTextItem()
    item.setTitle(itemNames[i]).setValidation(textValidation)
    // Logger.log("DEBUG: Added New Item - i: "+i+", Name: "+itemNames[i])
  }
}

---

BEFORE

AFTER

SPREADSHEET: ADD NEW ITEMS