Triggering Submission of Two Forms by Submitting One

[eyan1018]

Hi Everyone,

I'm trying to gather client data and hoping to make the client experience as easy as possible. The ulttimate goals are to determine a) how much a prospective client might contribute towards a new opportunity, and b) what account they'll be using when they make their contribution, i.e. an existing one or a new one.

I've created a data set within a Google Sheet that is organized by client email addresses, names and current accounts. In many cases, a single client can be associated with several accounts. I've currently set up two Google Forms, and submissions for each have been set up to be collected in two tabs of the same Google Sheet that also includes the data set. The forms are respectively organized to function as follows:

FORM 1

-Clients are prompted to manually enter their email address in the form, then press "submit". The post-submission message includes a link to Form 2 and a message indicating that they should click the link and complete the data in the next form.

NOTE: I've written formulas in the Google Sheet that complete the following functions:

a) take the latest email entry from Form 1 and match it to a client name

b) Create a list of accounts to which the client name is associated

FORM 2 (the following is how Form 2 should be working)

-The first field in Form 2 prompts clients to choose the account that they intend to use for their new contribution. This is a dropdown list, and ideally the options in this list will change dynamically based on the email address that they previously submitted in Form 1, showing only the accounts that are associated with the client name that was isolated by the provided email address. (Note: I've used the "Form Range" Google Sheets plugin in order to refresh this dropdown data.)

I've realized that I need to trigger a "Submission" event for Form 2 when a new email address is submitted through Form 1, causing the dropdown options in Form 2 to have refreshed by the time that the client who is completing the forms arrives at Form 2. Currently, I can only see the options from email addresses submitted one iteration earlier, not the most recent. 

Please note that I am very new to Google Apps Scripts and can also see that both scripts and triggers seem to be entered in different locations ("Extensions" for scripts - not sure about triggers), so please bear with me - outlining answers line-by-line would be very greatly appreciated!

Thank you in advance for your help and patience.

[Michael O'Shaughnessy]

Hello eyan1018!

I don't want to "burst" your bubble but I am going to say that Google Forms is not the best approach here.  Even though they submit info on Form 1, Form 2 when they open has no idea about the info from Form 1.  You "could" create a "prefilled" url and send that link to the client but that is a tad bit involved.

BUT don't give up, you need to change your approach.  What you need is a web app that when the user enter thier email and clicks submit the next drop down box will be filled by code in the backend that grabs the info from the sheet.  Think like creting a filter on the sheet, you filter for the email and all the info that is connected to that email can be used to populate the drop down.

At first you may think this is going to be tough but once you wrap your head around the logic you will be good.

I don't have time right now to put something together BUT I do have a YouTube video you will find useful:https://www.youtube.com/watch?v=wiuAQUxakHY

The video is old BUT he does a good job of explaining what he is doing and why.  It does use Google Apps Script but you should be able to follow along.  I suggest the first time you watch it just watch the video!  Don't try to follow along just watch what he does.  AND if you need a good course on the basics of Google Apps Script you can not go wrong with Ben Collins course.  Here is a link: https://courses.benlcollins.com/p/apps-script-blastoff  and it is FREE!!

I will try to put a basic example together by the end of the weekend so keep an eye out for it.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/4fa374e4-da68-41d0-8aba-025f4f562ac5n%40googlegroups.com.