What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

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

    Asked by zombienrh on April 29, 2014 at 09:11 PM

    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?

    items tracking product different nonprofit
  • Profile Image
    JotForm Support

    Answered by ashwin_d on April 29, 2014 at 10:55 PM

    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!

  • Profile Image

    Answered by Colin G.  on December 16, 2015 at 10:16 AM

    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.

  • Profile Image
    JotForm Support

    Answered by BJoanna on December 16, 2015 at 11:49 AM

    @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.