How do I populate a dropdown menu with items from a Google Sheet?

So I've read plenty of articles about populating fields using URL parameters and I don't think that those will do quite what I want.  So I have a couple of forms, and the second form depends on the submitted results of the first.

I am managing distribution/inventory, and the first form is a database for distribution points.  It has business names, contact information, etc.  Very straightforward form setup.  

The second form is simply a form that allows people to enter where product was delivered and how many.  I am only tracking one item, but it's something we give away since this is for a nonprofit organization.  That said, we want to keep track of who is handing out more in a year, to help with keeping our distributors with a steady supply.  We also need a quick way to see how much we have left so we can set up reorders efficiently.  Since we are a nonprofit, different volunteers are frequently doing the deliveries and I'd like to keep track of who is doing the dropoffs.

So, the first field of the second form needs to contain a dropdown menu of the business names from the results of the first form.  If a URL parameter would work, I need it to take every entry from a whole column to populate the dropdown menu.  

The user would just simply enter the relevant information into the other fields.

Any tips?

Hello zombienrh,

I am not sure if I have understood your question correctly.

Do you want to prepopulate your dropdown question or you want to populate the items of dropdown from the google spreadsheet?

Please be noted that is is not possible to dynamically populate dropdown options from any external data source. You will have to manually input all the options of the dropdown question of your form.

I feel the best solution for your requirement is to prepopulate the dropdown of your second form with the value from the first form field. You can take advantage of our "Field Prepopulator" to build your pre-population URL.

If a URL parameter would work, I need it to take every entry from a whole column to populate the dropdown menu. 

It seems I have not understood this part of your question. If you mean to say that you want to populate the content of the dropdown with the value of past submissions or from the google spreadsheet, it is not possible to achieve. The values of dropdown option needs to input manually.

Hope this helps.

Do get back to us if you meant to ask something else.

Thank you!

This is possible, but only if you know a bit of javascript. While on the sheet you want to take info from, go to Tools/Script Editor.

To access the information of the script, you can use the following code:

   var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("sample_worksheet_name")

This will create a Sheet object, as described here: https://developers.google.com/apps-script/reference/spreadsheet/sheet

To access your form, you can use this code: 

var f = FormApp.openById("id") //get your id from the long alphanumeric string in your form url

This will create a Form object, as described here: https://developers.google.com/apps-script/reference/forms/form

You can make this script run at a button press, or on sheet edit. I'm using the former. Once you have these objects, you can loop through ranges to add choices. Heres a quick example:

 var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("names")

 var f = FormApp.openById("redacted")

 var items = f.getItems(FormApp.ItemType.LIST) //returns all dropdown items

 var typeRange = s.getRange("B20:C45").getValues() //range with columns of numbers and strings

  var typeItem = items[1].asListItem() //returns the second item as a dropdown

  var typeValues = []

  for (var i = 0; i < 26; i++) { //loop through types

    if (!typeRange[i][0]) {

      break

    } else {

      name = "(" + typeRange[i][0] + ") " + typeRange[i][1]

      typeValues.push(name)

    }

  }

  typeItem.setChoiceValues(typeValues)

Sorry for necroing this thread. Maybe it'll help people in the future.

@Colin G.

Thank you for sharing this with us and our users.

I'm sure that this will be helpful to some of our users.