CODEWITHSUNDEEP
javascriptgoogle-sheetsgoogle-drive-api
deadendstreet
deadendstreet

Reputation: 111

Send Google Form data through email with Javascript

I'm trying to set up a google spreadsheet to send an email when certain values are changed to yes. So far, I've got it where it's working, except the variables task and program are "undefined."

Secondly, I only want it to send an email if no is changed to yes. So say E2 is already "yes" but E3 is no. Someone goes in and changes E3 to yes...I just want to email the information in column 3 not 2.

Lastly, how do I get it check everything in column E for a "yes:...so E2 through E50? Right now, it's only checking if E4.. I want to set it to check the whole column.

I'm a novice with javascript and seem to be stuck. Thanks.

  function readCell() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Form Responses");  
    var value = sheet.getRange("G4").getValue();
    var task = sheet.getRange("E4").getValue();
    var program = sheet.getRange("C4").getValue();
    if(value="Yes") sendEmail(value);
    if(value="Yes") sendEmail(task);
    if(value="Yes") sendEmail(program);
    };


    function sendEmail(value, task, program) {
    var email = "[email protected]";
    var subject = "Jacob has completed a task.";
    var body = "This is an automated generated email, Jacob Workman has completed  for " + task + "for" + program;

    var oauthConfig = UrlFetchApp.addOAuthService("google");
    oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
    oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://spreadsheets.google.com/feeds/");
    oauthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
    oauthConfig.setConsumerKey("anonymous");
    oauthConfig.setConsumerSecret("anonymous");
    MailApp.sendEmail(email,subject,body); 

    };

Thanks

Upvotes: 0

Views: 109

Answers (2)

Jeremy J Starcher
Jeremy J Starcher

Reputation: 23863

EDIT As Akum points out, you also have the parameter's wrong.

You are accidentally doing an assignment where you mean to be doing a compare.

if(value === "Yes") sendEmail(value, task, program);

Though it could be better written as:

if(value === "Yes") {
  sendEmail(value, task, program);
}

BTW: There is great tool called JSHint that helps find common issues like = and ===

Upvotes: 1

Akurn
Akurn

Reputation: 392

Your sendEmail() function has 3 arguments, but when your using it your only passing in one value as the value variable. Try:

if(value="Yes") {
    sendEmail(value, task, program);
}

Instead of each line separately

Upvotes: 1

Related Questions