google spreadsheet email

Question

I'm a newbie with Google Scripts and was wondering if someone could help me out.

I have a shared Google spreadsheet that basically updates rows with new Employee Information.

I want an e-mail to be triggered only when there is a string match to "ABC" on a specific Column (let's say Column F) when these rows of new employees get inserted. Basically the e-mail trigger will let our team know to set up new employee accounts.

Can anyone help me out? I don't know how to do a string match or have it send specifically to a fixed e-mail recipient. I've installed Python and gspread and gdata and feel like I'm overcomplicating it but haven't made any progress whatsoever for days ...

Please help!

Thanks in advance, Jamie

Thomas Thomas · Accepted Answer · 2012-09-25T21:41:06

The answer here depends on how the spreadsheet obtains its data.

If the data is obtained trough a form you can simply make a function and setup an on submit trigger. Code:

function formSubmit(e) {
  //Column F is the 6th colmn so you want the 5th value from the array (array starts with 0)
  if (e.values[5] == "ABC"){
    //read: https://developers.google.com/apps-script/class_gmailapp#sendEmail
    GmailApp.sendEmail("[email protected]","Subject","Content")
  }
}

Then go to your script Triggers and add this function to the onSubmit trigger.

If someone is typing in the data into the spreadsheet it is somewhat harder to find out in what column it is in

try something like this:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  if (rangeArray = ss.getActiveCell().getRow() == 6){
    if (ss.getActiveCell().getValue() == "ABC"){
      GmailApp.sendEmail(recipient, subject, body, options)
      //etcetc
    }
  }
}

Now your using the onEdit function and you dont need to setup a trigger here.

Hope this helps a bit!

With kind regards,

Thomas van Latum

google spreadsheet email

1 Answers