Google Forms & Google Sheets - is this scenario even possible?

[mikewg]

Have an idea for a project but can't even figure out where to start. Wanted to get feedback if this idea is even possible?

I have a form that needs to be completed by the same 1,000 people annually. The questions are the same - however, the options that each person gives is different based on past answers. I currently accomplish this with an Excel spreadsheet containing all the options, and then I mail-merge this into a Word DOC that prints the forms. The 1,000 complete the form and then I manually add the answers back into an Excel spreadsheet.

I'm looking to digitize this process but very surprised that online forms don't naturally support mail-merge type functions.

As an example, I have a Google Sheet that looks like this:

ID Number	Q1a	Q1b	Q2a	Q2b
001	Apples	Oranges	Coke	Pepsi
002	Apples	Pears	Coke	Sweet Tea
003	Apples	Peanuts	Coke	Water
004	Apples	Sandwiches	Pepsi	Water

Then my Google Form would look something like this:

Page 1:
What is your ID Number? _______________

And then based on what they answer, Page 2 looks like this:

What would you like to eat?
* [Q1a]
* [Q1b]

What would you like to drink?
* [Q2a]
* [Q2b]

And then the answers to these could be captured as answers in the Google Sheet, perhaps as columns to the right of Q2b.

It seems like something like this should be relatively basic functionality and would extremely powerful for most people, so I'm surprised something like this isn't easily available.

Please let me know if something like this is even feasible through Google Sheets and Google Forms? Or am I stuck with my mail merge forever?

 

[Dan_W]

I'm sorry - I'm not sure I follow - are you asking: (1) why you can't do this / how to do it in Excel/Word?; or (2) why you can't do this / how to do it in Sheets/Google Docs?

Please let me know if something like this is even feasible through Google Sheets and Google Forms? Or am I stuck with my mail merge forever?

And I think I' must be misunderstanding this completely - this is precisely what Google Forms acutally does, isn't it? Or at least, that's the only way that I know that it can be used. But before even getting to that point, if you're using Excel and Word as part fo the process already (there is probably a way to automate / speed up your mail merge process , by the way), why don't you just use Microsoft Forms? It does what Google Forms does, but connects to an existing Excel spreadsheet for you.

 

[mikewg]

I thought there might be a way, but everything I've seen shows that Google Forms simply collections information and interjects it into a spreadsheet. What I can't find is a way for the Form to check against a sheet to provide customer information to a user based on how they answer the initial question.

IE - tell Google Forms that I'm Employee 001 so that the options I am presented in the form are the ones listed in the sheet (and are unique to me, different than what everyone else sees in their forms).

Everything I find online is ways to auto populate answers (not trying to do this), I'm trying to populate options for people to choose.

 

[Dan_W]

Ah ok - thank you for the clarification. Yes, I completely misunderstood what you were trying to do - my apologies. Frankly, that's never occured to me, but I agree that something like this should be easily available. I feel like I know how this should be done, but I can't quite put my finger on it. I don't know if Google or MS Forms have a dynamic form generation function.

You could always code one in Excel VBA, and then circulate the workbook - I've done something like that before to a client, but that was to 10 receipients. I think you could probably even do a survey through an Excel workbook without having to rely on code - but would that be feasible? (ie., do all the recipients have access to Excel such that you could you circulate a workbook and ask them to complete the survey?

Either way, I'll give it some thought.
Have you consulted with a Google sheets/forms-specifc forum?

 

[mikewg]

Unfortunately I can't guarantee that every user will have Excel access, which is why I was hoping to do this with Google Forms. I agree it doesn't sound like advanced functionality, but I can't find an instance online where someone has tried to do something like this.

I haven't consulted with another forum because I'm only familiar with Mr Excel. I used to use it 15 years ago and learned so much reading the forums here, and thought I'd give this a try. Every time I searched for a Google Forms forum, I just found a bunch of help guides and videos but nothing like this.

 

[MARK858]

Every time I searched for a Google Forms forum, I just found a bunch of help guides and videos but nothing like this.

You could try the below (not that I've used it as I rarely use Sheets). Remember if you do create a thread there that you post a link to it here so you don't infringe the rules.

Browse the Google Docs Editors Community - Google Docs Editors Community

 

[Dan_W]

Thank you for this, Mark - this is a very useful resource.

@Mike - I was curious, so I had a quick look at the site Mark referenced for dynamic Google Forms(Forms search results) , and someone asked a related question back in 2019 (link) and the response was that Google Forms doesn't support that functionality. That said, it appears to nonetheless be possible by using Google Apps Script according to the response (and sample code) in this post. I haven't tested the code because Google Scripts is not my strength - it looks like it should work, but the code appears to adopt a (slightly surprising?) approach where it deletes everything in a given form and then starts to add whatever questions you code it to ask.

I wonder if perhaps if it's worth asking your question on the forum (referencing that you've asked the same question here, like Mark pointed out) and referencing this link with the code?

In any event, it doesn't look like it's a straightforward as either of us would have thought.