CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
John Lexus
John Lexus

Reputation: 3636

Triggering an email response on newly entered value in a specific column

I have a spreadsheet that looks a lot like this:

enter image description here

It's a spreadsheet to help a student with planning for the classwork. I created a column at the end, Help Needed From, that is a list of values that are names of their relatives. Let us say that list is [Alice, Bob, Trent]. I want to write a spreadsheet function that, upon entering a value in the Help Needed From column, an email would be sent out to that specific person with a message like:

"Eve needs help from you with an assignment from " + row[1] + " class. It is due " + row[5] + " and is a " + row[3] " priority item."

So row[1] would be the Class, row[5] the due date, and row[3] the priority. If that wasn't obvious. I have seen scripts only that send emails and stuff, but it seems like its something you have to manually run. Sure, I could get the student to do that - but is there a way to automate it?

Thanks.

Upvotes: 1

Views: 62

Answers (1)

Cooper
Cooper

Reputation: 64100

try something like this:

onMyEdit(e) {
  console.log(JSON.stringify(e));//look in executions to see the structure and available values in the event object.  They can save your function a lot of time.
  const sh=e.range.getSheet();
  if(sh.getName()!='Your Sheet Name')return;
  if(e.range.columnStart==8 && e.range.rowStart>3 && e.value!='') {
    //write you email function here.
    //since sending an email requires permission then this needs to be an installable trigger
    //And keep in mind onedit triggers will time out after 30 seconds.
  }
}

installable triggers

Gmail.sendEmail()

Upvotes: 3

Related Questions