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.

`Is there any way to auto-populate my google form question from my google sheet (perhaps with app script)?` Yes and no. **Yes**, you can update the form from your spreadsheet via app script. **No**, it does not "auto-populate". You need to run the script and either create/alter/delete new/existing questions and answers. My approach would probably be 1) delete all the existing short answer questions, and then 2) create new "standardised format" questions for all the the items in the spreadsheet. How "sophisticated/complex" is your form? Do you use sections, etc. — Tedinoz, Apr 30 '23 at 23:54
Would you please include a snapshot of a "product" question? Do all "product" questions have a common format? — Tedinoz, May 01 '23 at 00:45
The form is not complex, I can even say it is very simple. It is just Product Names as question, and short answer to fill with the qty. I edited the question and give a snapshot to it @Tedinoz — Randy Adikara, May 01 '23 at 09:17

score 0 · Answer 1 · answered May 07 '23 at 02:43

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

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

1 Answers1