Sending email when cell is changed on Google Spreadsheet

Vote count:

0

I'm trying to write some code that does a few things.

I want it to send an email if certain cells are changed. The text of an email would be different depending on who adds the task.

Here's the problem, for starters sometimes var program comes through the email as "undefined" other times it works fine.

Secondly, the email for Beau and Jill won't work unless I run the function myself through the script manager. I would it to do it automatically.

Lastly, the spreadsheet will grow and will continue to grow. Currently, it's just checking the second column. I want it to check E2:E100 or whatever. But, I only want it to send the email once. So if today I add something to E2, I want it to send an email. Tomorrow if I add something to E3, I only want it to email me about E3. But the way it's written, it's going to email the changed to E2 every time the function is run.

I appreciate any guidance on this. This may be a situation where I'm just way over my head and I need to look into a different option. So if that's the case, just be honest. I'll appreciate the transparency.

function readCell() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses");  
  var value = sheet.getRange("E2").getValue();
  var program = sheet.getRange("C2").getValue();
  var promo = sheet.getRange("H2").getValue();
  var web = sheet.getRange("K2").getValue();
  if(value != '') {
    sendEmail(value, program);
  } else if (promo != ' ') {
   sendJillEmail(promo, program);
  } else if (web != ' ') {
   sendBeauEmail(web, program) 
  }
};


function sendEmail(value, program) {
  var email = "bmwaldrop@gmail.com";
  var subject = "Jacob has added a task.";
  var body = "This is an automated generated email, Jacob Workman has added " + value + " for " + program;

  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("http://ift.tt/1cfwLz5");
  oauthConfig.setRequestTokenUrl("http://ift.tt/MViSOC");
  oauthConfig.setAuthorizationUrl("http://ift.tt/1cfwMD6");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  MailApp.sendEmail(email,subject,body); 

};

function sendJillEmail(promo, program) {
  var email = "bmwaldrop@gmail.com";
  var subject = "Jill has added a task.";
  var body = "This is an automated generated email, Jill Frank has added " + promo + " for " + program;

  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("http://ift.tt/1cfwLz5");
  oauthConfig.setRequestTokenUrl("http://ift.tt/MViSOC");
  oauthConfig.setAuthorizationUrl("http://ift.tt/1cfwMD6");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  MailApp.sendEmail(email,subject,body); 

};

function sendBeauEmail(web, program) {
  var email = "bmwaldrop@gmail.com";
  var subject = "Beau has added a task.";
  var body = "This is an automated generated email, Beau Waldrop has added " + web + " for " + program;

  var oauthConfig = UrlFetchApp.addOAuthService("google");
  oauthConfig.setAccessTokenUrl("http://ift.tt/1cfwLz5");
  oauthConfig.setRequestTokenUrl("http://ift.tt/MViSOC");
  oauthConfig.setAuthorizationUrl("http://ift.tt/1cfwMD6");
  oauthConfig.setConsumerKey("anonymous");
  oauthConfig.setConsumerSecret("anonymous");
  MailApp.sendEmail(email,subject,body); 

};

asked 15 secs ago

deadendstreet

34