Can I have google form multiple choice options linked to google sheet?

Question

I am setting up a silent auction. As the event is for charity, I am reluctant to pay for any software, so I am attempting to build something suitable in google.

On Sheet 'Lots' in google sheets, I have a table in range A1:F11 including header rows. This range will have more rows as I receive donations of lots to auction off.

I also have a google form set up, which I would like to link to the data I input on the Lots sheet. I would like to iterate through each row of the Lots sheet and add the Lot name in column B to a MultipleChoiceItem on my form.

    function myFunction() {

    var form = FormApp.openByUrl(
     'FORM LINK'
     )
    var workbook  = SpreadsheetApp.openByUrl(
     'SHEET LINK')

    var sheet = book.getSheets()[0];

    itemArray = "VALUES FROM SHEET"

    var item = form.addMultipleChoiceItem();
    item.setTitle('Auction Lots')
    .setChoices([

    item.createChoice('ItemArray'),

     ])

     }

I've written the code above, but don't really know how to add my sheet data to the array. Further to this, I'd like to add the description, reserve price and current highest bid from columns C,D and E to the item listing but hopefully once I've cleared this first hurdle that will be less of a problem!

Is what I'm attempting even possible?

TIA!

Once the form is running you can't interact with it programmatically like you can with an HTML form. — Cooper
May I suggest that you start with a Google Form. Manually build a simple prototype; figure out what data you'll have and where it will go. Show where the Highest Bid would be displayed. Then, and only then, think about how to update from the spreadsheet. — Tedinoz
@Cooper I'm not saying this is so easy to do but... if the OP had an OnFormSubmit trigger that identified the item and the bid, couldn't a script then update the relevant fields for that item. Then when the form is next opened by a user, and that same item is selected, the form will reflect the revised highest bid? — Tedinoz
@Tedinoz Yes. Could do that. But does that guarantee an update before the next submission? I think it depends upon how fast they're coming in. I have to admit I don't use Google Forms that much. — Cooper
@Avg "But does that guarantee an update before the next submission?" This is the "$64 question", and the answer is no. As Cooper says, it depends on how fast bids are being received. Anyway, you asked "Is what I'm attempting even possible?" and the answer is yes. Have you had any experience coding for Google Forms? It's not all that hard once you get the hang of it, but in my own limited experience, it can take a while just to get the hang of it. — Tedinoz

Andres Duarte Andres Duarte · Accepted Answer · 2019-10-28T16:59:41

I created an example Form [1] and a Spreadsheet [2] based on your description of the process. I developed the code below to update the highest bid price in the Sheets when a user submits the form, only if the bid price in the response is higher than the current one. For this, I used the documentation on triggers [3], the Form [4] and Spreadsheet [5] services.

You need to run the createOnSubmitTrigger function once to create the trigger that will run the runOnFormSubmit function when a user sends a form response:

function runOnFormSubmit(e) {
  //Get Form and Sheet objects
  var form = FormApp.openByUrl('Form-URL')
  var workbook  = SpreadsheetApp.openByUrl('Spreadsheet-URL')
  var sheet = workbook.getSheets()[0];

  //Get the user's responses for the multiple choice and bid price questions 
  var formResponse = e.response.getItemResponses();
  var lotsResponse = formResponse[0].getResponse();
  var bidPrice = formResponse[1].getResponse();

  //Use the selected lot name and find its row in the sheet
  var selectedLotName = lotsResponse.split(', ')[0];
  var textFinder = sheet.getRange(2, 2, sheet.getLastRow()-1, 1).createTextFinder(selectedLotName).matchCase(true).matchEntireCell(true);
  var highestBidCell = textFinder.findNext().offset(0, 3);

  //If the user's bid price is higher that the the current highest bid, update the sheets
  var highestBid = highestBidCell.getValue();
  if(bidPrice > highestBid) {
    highestBidCell.setValue(bidPrice);    


    //Get Sheets info in array and remove header
    var itemsArray = sheet.getDataRange().getValues();
    itemsArray.shift();

    //Get multiple choice question
    var lotsQuestion = formResponse[0].getItem().asMultipleChoiceItem();
    var choicesArray = [];

    //Build choices array from updated sheets info
    for(var i=0; i<itemsArray.length; i++) {
      var lotName = itemsArray[i][1];
      var description = itemsArray[i][2];
      var reservePrice = itemsArray[i][3];
      var highestBid = itemsArray[i][4];

      var choiceString = lotName + ', ' + description + ', ' + reservePrice + ', ' + highestBid;
      var choice = lotsQuestion.createChoice(choiceString);
      choicesArray.push(choice);
    }

    //Set choices array to the multiple choice question, this will only be done when the bid price is higher that the the current highest bid
    lotsQuestion.setChoices(choicesArray);
  }
}

//Creates on form submit trigger
function createOnSubmitTrigger() {
  var form = FormApp.openByUrl('Form-URL');
  ScriptApp.newTrigger('runOnFormSubmit')
  .forForm(form)
  .onFormSubmit()
  .create();
}

To ensure that the script will run only once at the same time, you can use the Lock service [6].

[1] https://docs.google.com/forms/d/e/1FAIpQLSfP4skRoqQSY543wE7UbGMLMR7_glIvWpA-hl1k-kufudN64A/viewform?usp=sf_link

[2] https://docs.google.com/spreadsheets/d/1AaGeODbtm4vybQJqdoXL_3zB7hBwIYHZgcwTvtaaXN8/

[3] https://developers.google.com/apps-script/guides/triggers/events

[4] https://developers.google.com/apps-script/reference/forms/form-app

[5] https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app

[6] https://developers.google.com/apps-script/reference/lock/lock

Can I have google form multiple choice options linked to google sheet?

1 Answers