With Sheetgo Forms you can collect data efficiently by creating customizable forms that feed directly into your spreadsheets. You can even configure subquestions to gather more detailed information without overwhelming users.
Why are subquestions useful
Let's go over an example of how subquestions are useful.
If someone fills out an order for a "drink," it triggers a new set of questions to specify the type of drink ("Coke," "Water," or "Ginger Ale"). Which triggers another set of questions about the drink size ("Bottle" or "Can").
These sets of questions would have been different if, instead of "drink," the user had filled out a form for a "dessert". Without subquestions, this Form would need to have a lot more visible fields, making it harder to fill out.
Configuring subquestions
To set up this configuration, you will need a spreadsheet with 2 tabs:
Form Questions - this holds the form questions, as explained in How to create a new form.
Parameters - this tab has the parameters your form will use to populate the subquestions.
Let’s use the restaurant example to illustrate this feature.
Add primary question to "Forms questions" (tab 1)
Create a new column for your primary question, add the field title as a column header.
Format your primary question with data validation, add a list of values for the primary question.
As seen above, our primary question is "Order Item Type," with data validation that includes the values "Drinks," "Sandwich," "Burger," and "Dessert."
This setup creates the initial list of questions in your form. Now let's configure the subquestions in the second parameters tab.
Set up subquestions in "Parameters" (tab 2)
The parameters sheet should list all potential combinations of values for your primary question and its subquestions.
Create columns for your primary question and subquestions: List all potential combinations as new rows. For example, if a user selects "Drinks" from the primary question, list all the different drink options, like "Coke," "Water," and "Ginger Ale." Repeat this process for each value from your primary question.
Your sheet should look something like this:
You can continue adding columns for additional subquestions, making it easier to filter this table by item and sub-item.
Please note: In your second tab, you must list every possible combination of values as new rows. If not done correctly, users may experience issues when filling out your form.
You can also choose to create a new table for each set of parameters rather than one long table as shown above (e.g., a table for "Drinks" values, a table for "Dessert" values, etc.). However, this will affect how you configure your form questions in the next step.
Adjust the main forms sheet (tab 1)
Now that your parameters are listed, return to the "Form questions" tab to apply the configurations for the subquestion behavior. Below is an example of what your main forms tab should look like:
The “Order Item Type” above is the column you created in the first step. This should contain the field title and be formatted with data validation. You can also add configurations to adjust how it is presented in the form.
Now it’s time to create the next set of columns for your subquestions. For this, you’ll need to use the following order of configurations:
Use conditional configuration to set up the relationship with the primary question column (in this example, the “Order Item Type” column).
It should look like this in our current example:[conditional: “Order Item Type”]
The value after the conditional should be the exact name of the primary question column.
Next, you must link to the cell within the parameters sheet that directly link to your primary question. In this example, I need to link the range of cells in the “Type” and “Item” columns in my parameter tab, to the “Order Item Type”.
“Parameters!A2:B100”
This is similar to how you’d build a vlookup. At this step, the configuration will look like this so far:
[conditional: “Order Item Type”, range: “Parameters!A2:B100”]
You can also set up the field to accept any text response by adding the command text to the end of the header. It will then look like this:
[conditional: "Order Item Type", range: "Parameters!A2:B100", text]
This allows you to input any text item that you wish but in case it matches one of the previously listed items, it also accepts that input and shows the new option as expected.
Repeat the steps for however many sub-questions you need on your form.
Once you have carried out the steps above, your form spreadsheet is ready to use. Using the example above, whenever a user selects an item in the “Order Item Type” field, their specific answers will trigger a new sub-question field where they can continue to select the following answer.
How to use Auto-fill
Another powerful feature of the subquestions is the auto-fill function, which allows a new answer to be automatically completed based on the previous one.
To configure this on your form, use the configuration vlookup and two others based on a conditional field, key, and range.
This is used to indicate another field that will provide the value to be searched for. The value of the key configuration will be the same name as this other field. Range, on the other hand, is used to indicate the data. The first column must have the searched value and the following columns must have values related to this one. Here is an example of how the configuration looks on your spreadsheet.
Using the above example, a pre-filled field can be configured to: [default:vlookup, key: "User id", range:"Auto fill parameters!A2:B"], where an existing field called User id will have options ranging from 1 to 6. When this field changes, our pre-filled field will change its default value based on the previous value. Here is a look at this system in action:
Additionally, an index can be added to indicate the values from which column of the range to display in the form. This index starts at 1 and can go up to the number of columns on your spreadsheet.
In the image above, if the range was "Auto fill parameters!A2:C", including the column Color, and values of column Name should be displayed, adding index:2 would achieve this goal. If no index is indicated, the last column of the range will be used.
Configuring subquestions in Sheetgo forms is a powerful way to enhance your data collection process. Following these steps, you can set up conditional logic to show subquestions based on respondents' answers, making your forms more dynamic and efficient.
Need more help?
Contact us via chat, on our email ([email protected]), or send us a ticket!
Visit our Community for help, tips, tricks, and feedback for Sheetgo.