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

    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.