Google Apps Script trigger on form submit

Question

I have a simple script that seems to work only if I edit the Specific field where I am looking for "On Campus". Currently, i have to edit the field to something different, then change it back to "On Campus" before the script will fire off.

What I want, is when the form is submitted, if the specified field says "On Campus" I want this script to fire off.

Any ideas why its only working if I manually edit the field? I have set the trigger to be on Form Submit.

function sendEmailSupervisor(e) {
  if (e.range.columnStart != 11 || e.value != "On Campus") return;
  const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,21).getValues();
  let a = rData[0][0];
  let b = rData[0][1];
  let c = rData[0][4];
  let d = rData[0][5];
  let f = rData[0][6];
  let now = new Date().toLocaleString("en-US");
  
  let msg = "Date/Time Submitted: "+ a + "\n \n" + "Email Address:  " + b +  "\n \n" + "Person Requesting? " + c +  "\n \n" + "Name of the event? " + d +  "\n \n" + "Description of the event? " + f +  "\n \n" + " Form Submitted:  " + now;
  Logger.log(msg);
  GmailApp.sendEmail("[email protected]", "On Campus Event Submitted", msg)
}

NOTE: I changed the "Notify me daily" to "Notify me immediately" but do not get anything when form is submitted. — user247365
– user247365, Commented Nov 22, 2022 at 14:28
You say your trigger is onFormSubmit(e) but it should not trigger if you edit the cell. Do you by any chance have it as both onFormSubmit(e) and onEdit(e) trigger? Also the value from an onFormSubmit(e) trigger is an array not a single value. — TheWizEd
– TheWizEd, Commented Nov 22, 2022 at 14:50
@TheWizEd - Ah yes, originally the trigger was set to OnEdit, and that is indeed why it worked for editing the field in the sheet. However, I changed the trigger to On Form Submit, but the script does not fire off. I am not sure what to change in my script to get it to accomplish my Goal. Here is the full scenario - in the form, there is a question with two options, "Off Campus" or " On campus". In the spreadsheet, that field for that question is Column "K", or the 11th Column. — user247365
– user247365, Commented Nov 22, 2022 at 14:59
If the submitted form has "On Campus" in Column "K" I want the script to fire off and email the supervisor with details from the submitted form. Hope that helps to clarify — user247365
– user247365, Commented Nov 22, 2022 at 15:02

TheWizEd · Accepted Answer · 2022-11-22 15:10:17Z

I'm not able to test this but it should work.

function sendEmailSupervisor(e) {
  Logger.log(e.values); // just so you can see what values are
  if( e.values[10] !== "On Campus" ) return;  // column K
  let a = e.values[0];
  let b = e.values[1];
  let c = e.values[4];
  let d = e.values[5];
  let f = e.values[6];
  let now = new Date().toLocaleString("en-US");
  
  let msg = "Date/Time Submitted: "+ a + "\n \n" + "Email Address:  " + b +  "\n \n" + "Person Requesting? " + c +  "\n \n" + "Name of the event? " + d +  "\n \n" + "Description of the event? " + f +  "\n \n" + " Form Submitted:  " + now;
  Logger.log(msg);
  GmailApp.sendEmail("[email protected]", "On Campus Event Submitted", msg)
}

Collectives™ on Stack Overflow

Google Apps Script trigger on form submit

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related