I would like to send an email when I add it from the form to the Google spreadsheet.

"We put ""Contact Form 7"" and ""CF7 Google Sheet Connector"" in WordPress, and add input from Contact Form 7 to the bottom of ""Sheet 1"" of the Google spreadsheet."

Now, please enter your province in the pref section of Contact Form 7 and click Sheet 2
I would like to use VLOOKUP to search for the correspondence table for the state vs. the person in charge, and then send an email.br/> Yes,
I would like to create a separate email to the person in charge of each prefecture, not the email that will be sent to admin via Contact Form 7.

So I wrote the following code, but it doesn't work well.

I don't use Google form, so I will trigger

Select event source:
from spreadsheet Select Event Type　　: Form Submission

However, it has never been run.

Also, if you debug separately from the trigger, you will get the following error:

TypeError: Cannot read property 'response' of undefined
[email protected]:4

I have checked the spelling of 'response' many times, but it seems that there are no mistakes, and I am troubled by the double punch along with the flag.

This is my first time using this kind of program, and I am using it as a copy page while searching the Internet, so please help amateurs.

//FormApp.getActiveForm()

function sendform(e){
  variables=e.response.getItemResponses();
  for (vari=0;i<items.length;i++) {
    var item=items[i];
    varq = item.getItem().getTitle();
    if(q==="pref"){
      vara = item.getResponse();
      varmail_address = VLOOKUP(a, importrange("https://docs.google.com/spreadsheets/d/xxxxxxxx-xx--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/", "sheet2!$A$1:$D$48"), 3, FALSE);
      This is a request from a customer living in varmessage=VLOOKUP(a,importrange("https://docs.google.com/spreadsheets/d/xxxxxxxx-xx--xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/", "Sheet2!$A$1:$D$48"),4,FALSE)+'\n\n';

      GmailApp.sendEmail(mail_address, 'There was an inquiry', message);

    }
  }
}

Sheet 2

AB CD
Hokkaido 〇 株式 Corporation [email protected] Hokkaido (← message will be changed later, but prefecture is included for now)