Google Script/Formula HELP! Want to send different emails based on response on Google Form



  • I have a Google Form which the Responses go into Google Sheets. I have been trying for days to get a formula or script to work. We have 7 locations and one of the answers on the form asks which clinic location they want to attend. So I want to have the response emails go to separate people based on the location answer (which is in column D). I have used the following formula with it and no luck:
    I have also tried a form emailer script with this formula but it kept sending blank form emails continuously, I had to remove it after getting 376 emails.

    =IF($D2=”Location 1”, [email protected],IF($D2=”Location 2”, [email protected],IF($D2=”Location 3”, [email protected],IF($D2=”Location 4”, [email protected],IF($D2=”Location 5”,[email protected],IF($D2=”Location 6”,[email protected],IF($D2=”Location 7”,[email protected])))))))

    Any suggestions/help would be appreciated! Thank you! I am not very good at coding yet, Learning :)



  • Anyone have any suggestions??? Please??


  • administrators

    Hi @skillsprout, I can't really tell whats the issue without looking at your data. Can you share your google sheet with some sample data?





  • suggestions?


  • administrators

    @skillsprout I think you are using an addon called FormEmailer which is outdated and also I don't have full access to the spreadsheet so I couldn't see your settings too.

    Have you tried this - Create Auto responding google form with 2 lines of code



  • @ranjithkumar10 ok, but how can I have the form be emailed to different people based on their "location" answer?


  • administrators

    You just have to add an IF statement to the code in https://codingislove.com/auto-responding-google-form-2-lines-code/

    It looks something like this -

    function autoResponder(e){
    var location = e.namedValues.Location[0]
    if(location == 'location 1'){
    MailApp.sendEmail("[email protected]", "New form submission for location 1", "Hello, There's a new enquiry for your branch office");
    }
    else if(location == 'location 2'){
    MailApp.sendEmail("[email protected]", "New form submission for location 2", "Hello, There's a new enquiry for your branch office");
    }
    else {
    MailApp.sendEmail("[email protected]", "New form submission", "Hello, There's a new enquiry for your office")
    }
    }
    

    Change form field names according to your google form. More info about accessing form values is in the above link.



  • @ranjithkumar10 Thank you!! I have set it up and added a trigger. I get the following error:
    TypeError: Cannot read property "namedValues" from undefined. That happened when I ran the script manually, so I submited a test form and never got the email that the response was assigned to. so it did not work. Here is my script:
    0_1500933278765_Screen Shot 2017-07-24 at 4.53.10 PM.png

    Thanks for any input.


  • administrators

    Looks like your script screenshot did not get uploaded. can you paste the code here?



  • @ranjithkumar10 here is the code, (the last else is for a field if they choose other and type in something, I'm not sure how to code that field?)

    function autoResponder(e){
    var location = e.namedValues.Location[0], email = e.namedValues.Email[0], adminEmail = "[email protected]" 
      if (location == 'Location1, IL'){
         MailApp.sendEmail("[email protected]","New inquiry/referral form submission for Region1, "Sydni, Here's a new inquiry/referral submission for Region1.");
       } 
        else if(location == 'Location2, IL'){
        MailApp.sendEmail("[email protected]","New inquiry/referral form submission for Region2, "Stacey, Here's a new inquiry/referral submission for Region2.");
       }
        else if(location == 'Location3, IL'){
        MailApp.sendEmail("[email protected]","New inquiry/referral form submission for Region3, "Steph, Here's a new inquiry/referral submission for Region3.");
       }
        else if(location == Location4, IL'){
        MailApp.sendEmail("[email protected]","New inquiry/referral form submission for Region4, "Sam, Here's a new inquiry/referral submission for Region4.");
       }
        else if(location == 'Location5, IL'){
        MailApp.sendEmail("[email protected]","New inquiry/referral form submission for Region5, "Sal, Here's a new inquiry/referral submission for Region5.");
       }
        else if(location == 'Location6, IL'){
        MailApp.sendEmail("[email protected]","New inquiry/referral form submission for Region6, "Syd, Here's a new inquiry/referral submission for Region6.");
       }
        else if(location == 'Location7, TX'){
        MailApp.sendEmail("[email protected]","New inquiry/referral form submission for Region7, "Saul, Here's a new inquiry/referral submission for Region7.");
       }
        else if(location == 'Location8, TX'){
        MailApp.sendEmail("[email protected]","New inquiry/referral form submission for Region8, "Sydni, Here's a new inquiry/referral submission for Region8.");
       }
        else {
        MailApp.sendEmail("[email protected]", "New inquiry/referral form submission for Other", "Jessica, Here's a new inquiry/referral submisison for Other regions.");
       }
       }
    

  • administrators

    This script won't work because your field names are wrong. Look at this line - var location = e.namedValues.Location[0] The field name should match exactly with the name of the field in your form.

    I had a look at your form in your website. According to that I'm guessing that the field name would be something like this -

    var location = e.namedValues['Skill Sprout Clinic Location:'][0]
    

    I can't tell that for sure unless I have full access to form. If you don't want to use namedValues then you can use e.values which would be something like this -

    var location = e.values[2]  //Assuming that location is the second field in your form
    

    Understand the code before implementing it and debug it using Logger.log method.

    Verify if the script is being triggered or not like this -

    function autoResponder(e){
    Logger.log("script started");
    Logger.log(e);
    //Other code
    }
    

    Then check our logs in script editor after submitting the form by pressing Ctrl + Enter or View > Logs


guest-login-reply
 

reconnecting-message