从Google App脚本发送电子邮件时出错

Question

我正在尝试发送电子邮件给用户表单后提交表单。但我有一个错误：

这是我的.html代码：

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

Answer 1

>

我认为该错误意味着Google Apps脚本端出现了错误。

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

在本例中，lastrow是电子表格的第一个选项卡。如果sheet1不是第一个选项卡，则最后一行可能不同。请小心这个。所以请修改如下。

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

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);
}

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);
}

• 在此修改中，它假设processForm2(formObject)的formObject具有正确的值。如果有错误的值，则可能发生错误。请小心这个。

• GetLastRow()