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.

[eyan1018]

Thanks so much for following up, Michael! I'm going to watch this video shortly - really appreciate your help.

[Kildere S Irineu]

O problema central não é apenas “gatilho”: é que o Form 2 tem uma lista global, enquanto a lista desejada é individual por cliente.

Pensou por 15s

A solução eficaz é não tentar “disparar uma submissão falsa” no Form 2.

O problema real é este: o dropdown do Google Forms é global, não individual. Se o cliente A envia o e-mail e o script muda o dropdown do Form 2, todos que abrirem o Form 2 verão aquela lista. Se dois clientes fizerem isso ao mesmo tempo, um sobrescreve o outro.

Melhor solução

Criar um Web App em Google Apps Script no lugar do Form 2.

Fluxo:

1. Cliente preenche o Form 1 com e-mail.
2. O Apps Script recebe o envio.
3. O script identifica o nome e as contas vinculadas.
4. O cliente é direcionado para uma página personalizada.
5. Essa página mostra apenas as contas daquele cliente.
6. A resposta é gravada na aba de respostas.

Isso resolve:

• atraso do Form Ranger;
• dropdown “uma submissão atrasado”;
• conflito entre vários clientes;
• risco de um cliente ver contas de outro;
• dependência da “última linha” do Form 1.

Solução aceitável, mas limitada

Se forem poucos clientes e quase nunca houver envios simultâneos, você pode usar Apps Script para atualizar o dropdown do Form 2 assim que o Form 1 for enviado.

Exemplo básico:

const FORM_2_ID = 'COLE_AQUI_O_ID_DO_FORM_2';
const SHEET_NAME_CLIENTES = 'Clientes';
const FORM2_ACCOUNT_ITEM_TITLE = 'Account';

function onForm1Submit(e) {
const email = e.namedValues['Email Address'][0].trim().toLowerCase();

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName(SHEET_NAME_CLIENTES);
const data = sheet.getDataRange().getValues();

const headers = data[0];
const emailCol = headers.indexOf('Email');
const accountCol = headers.indexOf('Account');

const accounts = [];

for (let i = 1; i < data.length; i++) {
const rowEmail = String(data[i][emailCol]).trim().toLowerCase();

if (rowEmail === email) {
accounts.push(String(data[i][accountCol]).trim());
}
}

const uniqueAccounts = [...new Set(accounts)].filter(Boolean);

if (uniqueAccounts.length === 0) {
uniqueAccounts.push('No account found - please contact support');
}

const form2 = FormApp.openById(FORM_2_ID);
const items = form2.getItems(FormApp.ItemType.LIST);

const accountItem = items.find(item => item.getTitle() === FORM2_ACCOUNT_ITEM_TITLE);

if (!accountItem) {
throw new Error('Dropdown item not found in Form 2.');
}

accountItem.asListItem().setChoiceValues(uniqueAccounts);
}

Como instalar o gatilho

No Apps Script:

1. Abra o projeto vinculado à planilha.
2. Clique em Triggers / Acionadores.
3. Clique em Add Trigger / Adicionar acionador.
4. Escolha a função: onForm1Submit.
5. Evento: From spreadsheet.
6. Tipo de evento: On form submit.
7. Salve e autorize.

Minha recomendação

Para uso real com clientes, eu não usaria dois Google Forms. Usaria:

Form 1 + Web App personalizado em Apps Script.

Isso é mais seguro, mais profissional e evita o erro estrutural de atualizar um dropdown global para uma experiência que precisa ser individual. O próprio contexto mostra que a tentativa atual depende de atualização dinâmica após submissão, mas chega atrasada em relação ao usuário que avança para o Form 2.

[Michael O'Shaughnessy]

Hello everyone!

Well, I thought I would offer up 2 solutions here. Both are web apps using Google Apps Script.  The first is a "barebones" it just works solution and it should be viewed as a "lerarning" tool.  The second is a "production ready" solution that uses error trapping and provides feedback to the user.

MrOResolutions Suppliers Barebones Prototype

MrOResolutions Supplier Drop Down Prototype

Let me know if anyone has any questions.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/5074b529-97d2-485f-9bd7-e4831f5c4da5n%40googlegroups.com.