I need to create a Google Form for asking game players for their name, which game they would like to play, and the rules they would like to play a game with. The rule selection is different across the games, but there is some overlap, i.e.

Game 1 choices: Rule 1, Rule 2, Rule 3

Game 2 choices: Rule 1, Rule 3, Rule 5

Game 3 choices: Rule 2, Rule 4, Rule 6

This sheet shows how I would like the linked Google sheet to look.

I have tried two options, neither of which work exactly how I would like.

Option 1: Use conditional formatting to direct players to the relevant game rules based on their game selection.

Google Form

Google Sheet

This does prevent players from selecting game rules that aren't available in their game of choice (e.g. selecting Rule 4 when they're playing Game 1). The issue is that the Google sheet has different columns for the same rule if you're playing a different game. I'd like the sheet to show Rule 1 in the same column, regardless of whether you're playing Game 1 or Game 2.

Option 2: Show all rule options to players and find a way to restrict their answers.

Google Form

Google Sheet

This option creates a Google Sheet that I would be happy with, but it doesn't prevent players from choosing rules that don't apply to their game. Is there a way to restrict which answers to "Rule Selection" are available dependent on which game they choose?

I'd be open to using apps script if that meant I could get the desired solution?

I need to create a Google Form for asking game players for their name, which game they would like to play, and the rules they would like to play a game with. The rule selection is different across the games, but there is some overlap, i.e.

Game 1 choices: Rule 1, Rule 2, Rule 3

Game 2 choices: Rule 1, Rule 3, Rule 5

Game 3 choices: Rule 2, Rule 4, Rule 6

This sheet shows how I would like the linked Google sheet to look.

I have tried two options, neither of which work exactly how I would like.

Option 1: Use conditional formatting to direct players to the relevant game rules based on their game selection.

Google Form

Google Sheet

This does prevent players from selecting game rules that aren't available in their game of choice (e.g. selecting Rule 4 when they're playing Game 1). The issue is that the Google sheet has different columns for the same rule if you're playing a different game. I'd like the sheet to show Rule 1 in the same column, regardless of whether you're playing Game 1 or Game 2.

Option 2: Show all rule options to players and find a way to restrict their answers.

Google Form

Google Sheet

This option creates a Google Sheet that I would be happy with, but it doesn't prevent players from choosing rules that don't apply to their game. Is there a way to restrict which answers to "Rule Selection" are available dependent on which game they choose?

I'd be open to using apps script if that meant I could get the desired solution?

• google-sheets
• google-apps-script
• google-forms

Share Improve this question Follow edited Nov 19, 2024 at 14:36 RuthxWF 6111 bronze badge asked Nov 19, 2024 at 13:45 Jamie NewtonJamie Newton 2122 bronze badges 1

• 1 In option 1, you can always remould the data or use onFormSubmit trigger to put the correct data in correct columns – TheMaster Commented Nov 19, 2024 at 16:22

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

Try this formula:

=query({
 query({'Form responses 1'!B2:I},"select * where Col1 is not null and Col2 = 'Game 1'");
 query({'Form responses 1'!B2:C,'Form responses 1'!G2:G,'Form responses 1'!E2:E,'Form responses 1'!H2:H,'Form responses 1'!K2:K,'Form responses 1'!I2:I,'Form responses 1'!L2:L},"select * where Col1 is not null and Col2 = 'Game 2'");
 query({'Form responses 1'!B2:C,'Form responses 1'!D2:D,'Form responses 1'!J2:J,'Form responses 1'!F2:F,'Form responses 1'!K2:K,'Form responses 1'!I2:I,'Form responses 1'!L2:L},"select * where Col1 is not null and Col2 = 'Game 3'")
},"order by Col1")

---

Logic

• the formula used the QUERY function doc ref and Query Language Reference
• there are three nested queries; one each for Game 1, Game 2, and Game 3.
• In each nested query,
  • Player and Game fields are identical
  • Rule values change according to the "Game'
  • Since there are six Rules in total, each query MUST have 6 Rule values even though the number of Rules for each Game is three or fewer.
    • Where a given Rule number doesn't apply for a given Game (e.g. there is no Rule 2 for Game 2), a column applying to another Game is used to populate a blank Rule column. For example, in Game 2, Rule 2 is populated from Column E (which is a Game 1 rule).
• The query is dynamic; it will update as each submission is received.

---

Sample output

Yes, you can reduce or filter options in a question based on a previous answer in Google Forms using the "Go to section based on answer" feature or conditional logic. Here's how you can do it:

Method 1: Using "Go to section based on answer" Create multiple sections in your form for different sets of questions.

Click on the "Add section" button (two horizontal lines) to create a new section for the next set of questions. Create the first question that will determine the options for the next question. For example, a multiple-choice question asking "What type of product are you interested in?"

Set up conditional logic for the second question:

In your form, add a new question (e.g., a dropdown or multiple-choice question) that will display different options based on the answer to the first question. Click on the three dots (⋮) on the bottom-right of the first question and select "Go to section based on answer." For each option in the first question, specify which section should follow, based on the user's response. Customize the second question in each section to show relevant options based on the first answer.

Example:

If the first question asks "What type of product are you interested in?" and you have options like "Electronics" and "Clothing," you can create separate sections for each type. In the "Electronics" section, ask about specific electronic products, and in the "Clothing" section, ask about clothing-related questions. Method 2: Using "Response validation" and hidden sections for dynamic filtering Though Google Forms doesn't allow dynamically hiding individual options in a multiple-choice or dropdown question based on a prior answer (like some other platforms), you can set up your form structure with sections and conditional navigation, as described above.

By combining sections and navigating between them based on responses, you can effectively "filter" the next set of options for the user.