Sent e-mail to specific recipents based on form response

[BandJosh]

I have been using this form since last Spring and love it.

However, I am wondering if it's possible and how I can go about sending an e-mail response to person based on a form response.  Example, a form is submitted with 7 in column F.  Based on that 7, I want the form to e-mail a specific person, whereas if the form comes back with an 8 it would e-mail a different person.

Any help would be great!

Thanks for your hard work on this.  Any chance of adding a them to the e-mail or some added color?

[Rob]

If I'm understanding you correctly, this can be done using a VLOOKUP formula in the main spreadsheet.  To do this create another spreadsheet tab that has two columns:  one with the "number (e.g. 7 or 8 as you desire)" that is collected in the form, and the other with the corresponding email address.  You can then use this formula to look up the correct email from the submitted form value, and then use this in the FormEmailer template to send to that email.  

You'll want to see the spreadsheet formula help, if you are unfamiliar with VLOOKUP, and you'll also need to review Enrique's instructions for utilizing formulas located on this site.

Good luck!

[BandJosh]

That worked!  Thanks so much.  Using the VLOOKUP formula was much easier than I expected.

Thanks!

[Henrique G. Abreu]

This is really nice! I think it's the first time a problem is completely solved without any direct help from me. It feels like a community :)

Thanks Rob and BandJosh... and all FormEmailer users.

Kind regards,
Henrique G. Abreu

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.

To view this discussion on the web visit https://groups.google.com/d/msg/formemailer/-/i2wrttqlmU8J.

To post to this group, send email to forme...@googlegroups.com.
To unsubscribe from this group, send email to formemailer...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/formemailer?hl=en.

[BandJosh]

UPDATE: Now that I have forms being submitted, the VLOOKUP is not work because once a form is submitted for that line, the incoming form overwrites where the formula is, even when it's after the last column field.  So the VLOOKUP is working until a form is submitted.

How can I have the VLOOKUP formula remain in the row even after a form is submitted so that the FormEMailer knows where the formula is to execute?

THANKS!

[Rob]

Have you read Henriques section on "advanced settings"  where he explains how to set up formulas.  There are some advanced settings changes you need to make.  You must indicate where the formula is located, and the first line of data must not be changed.

[Ryan Pankoe]

Sorry this is bring up an older topic, but still valid.  I am trying to get this to work, but don't understand the VLOOKUP entirely.

I am trying to get FormEmailer to choose a recipient to email the form results to based on the value selected in column M.  

I have created a new tab called EmailRecipient.  In that tab, I have listed all the possible values that column M on the Form Responses could have since it's a multiple choice field.  Then in Column B on that EmailRecipient tab, I entered the corresponding email address that the results should be sent to for each entry (column A).  I went back to the main form responses tab and created a new column (B) just to the right of FormEmailer Status and entered the VLOOKUP formula in B2.  It seems that I am not configuring VLOOKUP formula properly though.  From reading the previous materials, this is how I understand what I need to do, but it's not working.  Any assistance would be appreciated.

-Ryan

This electronic message is intended to be for the use of the named recipient, and may contain information that is confidential or privileged. This communication may contain protected health information (PHI) that is legally protected from inappropriate disclosure by the Privacy Standards of Health Insurance Portability and Accountability Act (HIPAA) and relevant Pennsylvania Laws.  If you are not the intended recipient, please note that any dissemination, distribution or copying of this communication is strictly prohibited.  If you have received this message in error, you should notify the sender immediately by telephone or by return email and delete and destroy all copies of this message.  Direct questions to the Corporate Compliance and Privacy Officer at (610) 847-2165.

[Henrique Abreu]

Have you checked Google help page for this formula? It documents this formula nicely and has some usage examples.

https://support.google.com/docs/answer/3093318?hl=en

Henrique Abreu

--

You received this message because you are subscribed to the Google Groups "FormEmailer" group.

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.

To post to this group, send email to forme...@googlegroups.com.

Visit this group at http://groups.google.com/group/formemailer.
For more options, visit https://groups.google.com/d/optout.

[Ryan Pankoe]

For the second value of VLOOKUP I didn't realize that I needed to actually select both columns, the one with what I want to match to and the 2nd with what would be returned to my lookup field. After a couple hours playing with this, I finally got it working.  

-Ryan

On Friday, January 30, 2015 at 12:25:29 PM UTC-5, Henrique Abreu wrote:

Have you checked Google help page for this formula? It documents this formula nicely and has some usage examples.

https://support.google.com/docs/answer/3093318?hl=en

Henrique Abreu

[David Hotler]

I am trying to use VLOOKUP or QUERY to do what is being described here. I want to have an email address returned when a string value is entered into the form. So the user selected a MC answer that produces a string (in this case the nearest metro stop to them). That string is associated with an email (the volunteer who lives closest to that metro stop) and the email is returned on query or lookup and used as the email to send a response. Make sense?

[Taher Naggar]

Hello All,

Thanks, Henrique, for creating the script, and to all of you for the posts. This is my first attempt at using a script, so please bear with me. I have created a form for my school for staff to be able to request support from one of three categories, after they have selected whether they are primary school staff or secondary school staff. The categories are:

E-Learning Design - Primary

E-Learning Design - Secondary

G Suite Training - Primary

G Suite Training - Secondary

G Suite Technical Support (either primary or secondary) 

Once the staff member has completed the form, I would like a message sent to the appropriate support team member so that he/she may follow-up with the person who made the request.

I've installed the script and entered a sample response. I've also read through Henrique's site and looked at the other link that you've all provided, but I'm stuck. Since the responses categories above are text-based, I think I should be using Query instead of vLookup, but can't figure out how to set it up. Any assistance would be greatly appreciated.

Thanks,

Taher

[Henrique Abreu]

Hi Taher,

It seems your use-case works with either vlookup or query, or even just a chain of IFs.

Here are some basic steps on how to achieve this:

1. Come up with a Google Sheets formula that retrieves the desired recipient based on whatever logic you want.
   
• Example: =IF(C2="Yes","me@domain","someoneelse@domain")
  
2. Set this formula on your form responses sheet on row 2 (1st row with responses, since row 1 is the header) in a new column right after the end of your form.
   
• Example: cell H2
  
3. Write a unique header for this new column (i.e. in row 1, right above where you entered the formula).
   
• Example: in cell H1 write: Recipient Logic
  
4. Open FormEmailer settings and use the placeholder for this column in the desired field (for example, the "To" field)
   
• Example: set "To" field to: #Recipient Logic#
  
5. On FormEmailer advanced settings tab set the "Formula location" parameter to the column you wrote the formula
   
• Example: Form Responses!H
  
6. Save and close
   

That's it, you should be all set.

People usually get stuck on step 1. If that's your case, I guess the easiest approach is using IFs

=IF(condition; then-value; else-value), which is simple to chain in order achieve multiple, flexible tests. For example:

Let's say your Category (condition field) is on column C. Your formula would look like:

=IF(C2="Category1", "cat1@address", IF(C2="Category2, "cat2@address", "catch-all@address"))

Are you stuck in a different step? Was this helpful?

Kind regards,

Henrique G. Abreu

--

You received this message because you are subscribed to the Google Groups "FormEmailer" group.

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer+unsubscribe@googlegroups.com.

To post to this group, send email to forme...@googlegroups.com.

Visit this group at https://groups.google.com/group/formemailer.

[ANN CHAVEZ]

Hello,

This is not working for me. I've read and re-read the instructions many times. When the script reads my formula, it is changing the numbers in the formula, like when you try to copy down, the numbers increase with each cell, so the parameters in the formula end up being off and cannot locate the necessary values. How can I freeze the formula so the exact formula is used on every line when each form is submitted?

Thank you!

On Tuesday, October 24, 2017 at 4:52:20 PM UTC-5, Henrique Abreu wrote:

Hi Taher,

It seems your use-case works with either vlookup or query, or even just a chain of IFs.

Here are some basic steps on how to achieve this:

1. Come up with a Google Sheets formula that retrieves the desired recipient based on whatever logic you want.
   
• Example: =IF(C2="Yes","me@domain","someoneelse@domain")
  
2. Set this formula on your form responses sheet on row 2 (1st row with responses, since row 1 is the header) in a new column right after the end of your form.
   
• Example: cell H2
  
3. Write a unique header for this new column (i.e. in row 1, right above where you entered the formula).
   
• Example: in cell H1 write: Recipient Logic
  
4. Open FormEmailer settings and use the placeholder for this column in the desired field (for example, the "To" field)
   
• Example: set "To" field to: #Recipient Logic#
  
5. On FormEmailer advanced settings tab set the "Formula location" parameter to the column you wrote the formula
   
• Example: Form Responses!H
  
6. Save and close
   

That's it, you should be all set.

People usually get stuck on step 1. If that's your case, I guess the easiest approach is using IFs

=IF(condition; then-value; else-value), which is simple to chain in order achieve multiple, flexible tests. For example:

Let's say your Category (condition field) is on column C. Your formula would look like:

=IF(C2="Category1", "cat1@address", IF(C2="Category2, "cat2@address", "catch-all@address"))

Are you stuck in a different step? Was this helpful?

Kind regards,

Henrique G. Abreu

On Mon, Oct 23, 2017 at 5:42 PM, Taher Naggar via FormEmailer <formemailer+APn2wQcgP2-fPICMbeh3sjroMzhCyjIYskwU9Km2xn1qZ8...@googlegroups.com> wrote:

Hello All,

Thanks, Henrique, for creating the script, and to all of you for the posts. This is my first attempt at using a script, so please bear with me. I have created a form for my school for staff to be able to request support from one of three categories, after they have selected whether they are primary school staff or secondary school staff. The categories are:

E-Learning Design - Primary

E-Learning Design - Secondary

G Suite Training - Primary

G Suite Training - Secondary

G Suite Technical Support (either primary or secondary) 

Once the staff member has completed the form, I would like a message sent to the appropriate support team member so that he/she may follow-up with the person who made the request.

I've installed the script and entered a sample response. I've also read through Henrique's site and looked at the other link that you've all provided, but I'm stuck. Since the responses categories above are text-based, I think I should be using Query instead of vLookup, but can't figure out how to set it up. Any assistance would be greatly appreciated.

Thanks,

Taher

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.

[Lee Carver]

What is the formula that you are copying down? The row numbers should increase, but you're saying there's another number that increases as well?

Can you show us the formula from Rows 2 and 3?

[ANN CHAVEZ]

The formula is =VLOOKUP(D:D,Sheet2!A2:B3,2,0)

This is what it was doing yesterday:

However I just ran the form twice and now it's not even bringing the formula in at all, it's just giving me N/A. This is how my advanced settings look in FormEmailer:

Thank you!

 

Ann Fox Chavez, VP, MA, CFE

Corporate Investigations & Recovery Services - Sr. Risk Manager

Mobile 817-312-2395 / Tel. 817-838-1726 / Fax 205-524-0725

P.O. Box 650561, Dallas, TX 75265 / TX FW RM SCY

Facebook // Twitter // Instagram // LinkedIn // YouTube // Snapchat 

Henrique G. Abreu

On Mon, Oct 23, 2017 at 5:42 PM, Taher Naggar via FormEmailer <formemailer+APn2wQcgP2-fPICMbeh3sjroMzhCyjIYskwU9Km2xn1qZ8X7c...@googlegroups.com> wrote:

Hello All,

Thanks, Henrique, for creating the script, and to all of you for the posts. This is my first attempt at using a script, so please bear with me. I have created a form for my school for staff to be able to request support from one of three categories, after they have selected whether they are primary school staff or secondary school staff. The categories are:

E-Learning Design - Primary

E-Learning Design - Secondary

G Suite Training - Primary

G Suite Training - Secondary

G Suite Technical Support (either primary or secondary) 

Once the staff member has completed the form, I would like a message sent to the appropriate support team member so that he/she may follow-up with the person who made the request.

I've installed the script and entered a sample response. I've also read through Henrique's site and looked at the other link that you've all provided, but I'm stuck. Since the responses categories above are text-based, I think I should be using Query instead of vLookup, but can't figure out how to set it up. Any assistance would be greatly appreciated.

Thanks,

Taher

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.
To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.
To post to this group, send email to forme...@googlegroups.com.

Visit this group at https://groups.google.com/group/formemailer.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer+unsubscribe@googlegroups.com.

[Lee Carver]

Hrm... a couple of quick questions.

1. Why are you looking at the entire D column for the vlookup? Shouldn't you only be looking at the value for that row? (ie. the first row should be =VLOOKUP(D2,Sheet2!A2:B3,2,FALSE) ?)

2. It seems like the primary issue you're experiencing is the vlookup table is adjusting for each row. Is your reference table on Sheet2 set? If so, I would lock your vlookup table to specific values. So if your table goes for A2 to B6, then your vlookup formula should be: =VLOOKUP(D2,Sheet2!$A$2:$B$6,2,FALSE). That way, your formula is always looking at the same 5 rows on Sheet2 no matter how many rows you have on your Form Responses tab.

Hopefully this helps!

[ANN CHAVEZ]

I have it as just D because the value in D will vary by each form response. If I put D2, won't it just look at D2 every time instead of looking at the response value in column D?

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer+unsubscribe@googlegroups.com.

[Lee Carver]

No. What you have now is looking at the entire D column. If you put in D2 in the formula, as new rows are added, D2 will progress incrementally (D3, D4, D5). This is what your Sheet2 table is doing. See how A2:B3 is increasing incrementally by one for each new row? (e.g. A3:B4, A4:B5). That's what D2 will do. So it will be looking at column D for that particular row.

[ANN CHAVEZ]

Ok, I will try this. Thank you!

To unsubscribe from this group and stop receiving emails from it, send an email to formemailer+unsubscribe@googlegroups.com.

[ANN CHAVEZ]

It worked! Thank you sooo much!