Search code examples
if-statementgoogle-sheetsgoogle-formsarray-formulasgoogle-sheets-query

Add email address to sheet once Google form is submitted

I have a google form where the user can choose 1 of 5 options, "Red" "Green" "Blue" "Violet" "Yellow" I want to be able to add an email address to a column when a form is submitted so that I can set up an easy mail merge.

For example David wanting 2 boxes from the blue team.

The "color -team" Name always comes in column "C". I want their group email to auto populate in Column "G" based on column > C3 would be "Blue" Then G3 would become "[email protected]"

I am thinking this would be done with an array formula and the if-then sort of idea?

Any help or directions would be greatly appreciated.

Solution

  • depends on how your form looks like if like this then use:

    =ARRAYFORMULA(IF(A:A<>"", 
 TRIM(TRANSPOSE(QUERY(TRANSPOSE(B:F),,999^99)))&"[email protected]", ))

    enter image description here

    or perhaps:

    ={"emails"; ARRAYFORMULA(IF(A2:A<>"", REGEXREPLACE(
 TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(B2:F>0, B1:F1, )),,999^99)))&
 "[email protected]", "^[email protected]", ), ))}

    0

    UPDATE:

    =ARRAYFORMULA(IFNA(VLOOKUP(C3:C, {
 "Blue",   "[email protected]";
 "Green",  "[email protected]";
 "Yellow", "[email protected]"}, 2, 0)))