CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Ray
Ray

Reputation: 189

Error On Sending Email From Google App Script

I'm trying to send an email to a user form after the submit the form. But I got an error:

Uncaught at emailCode2 at processForm2

Is there anything I did wrong? Your respond would be appreciated.

Here's my .html code:

<script>    
function handleFormSubmit(formObject) {
        google.script.run.processForm2(formObject);
        document.getElementById("myForm").reset();
        alert("Your form is submitted. Thank you!");
      }
</script>

Here's my .gs code:

function processForm2(formObject) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getSheetByName("Sheet1");
  var tz = ss.getSpreadsheetTimeZone();
  var d = Utilities.formatDate(new Date(), tz, 'dd/MM/yyyy @ HH:mm:ss');
  var code = Math.floor(Math.random() * 99999) + 10000;

  ws.appendRow([code, d, null, null, formObject.name, formObject.email, 
                  formObject.things]);
  emailCode2(code);
 }

function emailCode2(code){
  var url = "https://docs.google.com/spreadsheets/d/11X8e10TGQwzUQVJzTJ24JB1KMCTzkL5F5vObKrH0__k/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var lastRow = ss.getLastRow();
  var sheet= ss.getSheetByName("Sheet1");
  
  var name = sheet.getRange(lastRow,5).getValue();
  var emailAddress = sheet.getRange(lastRow,8).getValue();
  var subject = 'Code';

  var message = 'Dear ' + name + ',\n\nHere is your code: ' + code;
  MailApp.sendEmail(emailAddress, subject, message);
}

Upvotes: 1

Views: 80

Answers (1)

Tanaike
Tanaike

Reputation: 201378

How about this answer?

Modification points:

  • I think that the error means that an error occurs at Google Apps Script side.

  • I think that the reason of your issue might be the following part.

      var ss = SpreadsheetApp.openByUrl(url);
  var lastRow = ss.getLastRow();
  var sheet= ss.getSheetByName("Sheet1");

    In this case, lastRow is the 1st tab of the Spreadsheet. If Sheet1 is not the 1st tab, the last row might be different. Please be careful this. So please modify as follows.

      var ss = SpreadsheetApp.openByUrl(url);
  var sheet= ss.getSheetByName("Sheet1");  // Modified
  var lastRow = sheet.getLastRow();  // Modified

  • If the active Spreadsheet is the same with var url = "https://docs.google.com/spreadsheets/d/11X8e10TGQwzUQVJzTJ24JB1KMCTzkL5F5vObKrH0__k/edit#gid=0";, after the value was appended with appendRow, the email address is retrieved the column "H". But the appended values has the email at the column "F". By this, the email is not declared. By this, an error occurs.

When above points are reflected to your script, it becomes as follows.

Pattern 1:

If the active Spreadsheet is the same with var url = "https://docs.google.com/spreadsheets/d/11X8e10TGQwzUQVJzTJ24JB1KMCTzkL5F5vObKrH0__k/edit#gid=0";, how about the following modification?

Modified script:

function processForm2(formObject) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getSheetByName("Sheet1");
  var tz = ss.getSpreadsheetTimeZone();
  var d = Utilities.formatDate(new Date(), tz, 'dd/MM/yyyy @ HH:mm:ss');
  var code = Math.floor(Math.random() * 99999) + 10000;

  ws.appendRow([code, d, null, null, formObject.name, formObject.email, formObject.things]);
  emailCode2(code);
}

function emailCode2(code){
  var url = "https://docs.google.com/spreadsheets/d/11X8e10TGQwzUQVJzTJ24JB1KMCTzkL5F5vObKrH0__k/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var sheet= ss.getSheetByName("Sheet1");  // Modified
  var lastRow = sheet.getLastRow();  // Modified
  
  var name = sheet.getRange(lastRow,5).getValue();
  var emailAddress = sheet.getRange(lastRow,6).getValue();  // Modified
  var subject = 'Code';

  var message = 'Dear ' + name + ',\n\nHere is your code: ' + code;
  MailApp.sendEmail(emailAddress, subject, message);
}

Pattern 2:

If the active Spreadsheet is NOT the same with var url = "https://docs.google.com/spreadsheets/d/11X8e10TGQwzUQVJzTJ24JB1KMCTzkL5F5vObKrH0__k/edit#gid=0";, how about the following modification?

Modified script:

function processForm2(formObject) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getSheetByName("Sheet1");
  var tz = ss.getSpreadsheetTimeZone();
  var d = Utilities.formatDate(new Date(), tz, 'dd/MM/yyyy @ HH:mm:ss');
  var code = Math.floor(Math.random() * 99999) + 10000;

  ws.appendRow([code, d, null, null, formObject.name, formObject.email, formObject.things]);
  emailCode2(code);
}

function emailCode2(code){
  var url = "https://docs.google.com/spreadsheets/d/11X8e10TGQwzUQVJzTJ24JB1KMCTzkL5F5vObKrH0__k/edit#gid=0";
  var ss = SpreadsheetApp.openByUrl(url);
  var sheet= ss.getSheetByName("Sheet1");  // Modified
  var lastRow = sheet.getLastRow();  // Modified
  
  var name = sheet.getRange(lastRow,5).getValue();
  var emailAddress = sheet.getRange(lastRow,8).getValue();
  var subject = 'Code';

  var message = 'Dear ' + name + ',\n\nHere is your code: ' + code;
  MailApp.sendEmail(emailAddress, subject, message);
}

Note:

  • In this modification, it supposes that formObject of processForm2(formObject) has the correct values. If this has wrong values, an error might occur. Please be careful this.

Reference:

Upvotes: 2

Related Questions