谷歌應用程式腳本：到達谷歌作業表的最后一行后呼叫另一個函式

我可能不知道如何在到達谷歌表上注冊代碼列的最后一行時呼叫另一個函式，用戶單擊谷歌表單上的提交將自動通過電子郵件發送另一個 sendMail 函式，例如“注冊已關閉，謝謝。” '

我的代碼：

function sendmail() {

  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("Sheet1");
  var EMAIL_SENT = "EMAIL_SENT";
  
  var lastRow = sh1.getLastRow();
  
  var email = sh1.getRange(lastRow, 2).getValue();
  var name = sh1.getRange(lastRow, 1).getValue();
  var registercode = sh1.getRange(lastRow, 3).getValue();
  
  subjecttxt = "Thank you for registered with us"
  email_Body = "Dear "   name   ","   "<br>"  
              "your registeration is successfully, your registered code is "   registercode   
              "Thank you for your registeration"
  
              MailApp.sendEmail({
                to:email,
                subject: subjecttxt,
                htmlBody: email_Body,
              }) ;           
  sh1.getRange(lastRow, 4).setValue(EMAIL_SENT);
}

uj5u.com熱心網友回復：

我相信你的目標如下。

• 在您的情況下，值被放入“A”和“B”列。那時，您的功能sendmail已運行。此時，當將值放入“A”和“B”列時，當該行超過“C”列的最后一行時，您要發送電子郵件正文registration had been closed, thank you.。

如果我的理解是正確的，下面的修改如何？

修改后的腳本：

function sendmail() {
  // This is from https://stackoverflow.com/a/44563639
  Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
    const range = this.getRange(offsetRow, columnNumber, 2);
    const values = range.getDisplayValues();
    if (values[0][0] && values[1][0]) {
      return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()   1;
    } else if (values[0][0] && !values[1][0]) {
      return offsetRow   1;
    }
    return offsetRow;
  };

  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("Sheet1");
  var EMAIL_SENT = "EMAIL_SENT";
  var lastRow = sh1.get1stEmptyRowFromTop(1) - 1;
  var email = sh1.getRange(lastRow, 2).getValue();
  var name = sh1.getRange(lastRow, 1).getValue();
  var registercode = sh1.getRange(lastRow, 3).getValue();
  var subjecttxt = "Thank you for registered with us";
  var email_Body;
  if (lastRow < sh1.get1stEmptyRowFromTop(3)) {
    email_Body = "Dear "   name   ","   "<br>"   "your registeration is successfully, your registered code is "   registercode   "Thank you for your registeration";
  } else {
    email_Body = "registration had been closed, thank you.";
  }
  MailApp.sendEmail({
    to: email,
    subject: subjecttxt,
    htmlBody: email_Body,
  });
  sh1.getRange(lastRow, 4).setValue(EMAIL_SENT);
}

• About if (lastRow < sh1.get1stEmptyRowFromTop(3)) {,,,}，在這個腳本中，當“A”和“B”列的值被放到“C”列最后一行的下一行時，email_Body = "registration had been closed, thank you."使用。當您想email_Body在“C”列的最后一行使用它時，請修改(lastRow < sh1.get1stEmptyRowFromTop(3))為(lastRow < sh1.get1stEmptyRowFromTop(3) - 1)。

• 當您要添加"registration had been closed, thank you."到email_Body，當值被放到同一行的列“C”的最后一排，請修改上面的if陳述句如下。

    if (lastRow < sh1.get1stEmptyRowFromTop(3) - 1) {
      email_Body = "Dear "   name   ","   "<br>"   "your registeration is successfully, your registered code is "   registercode   "Thank you for your registeration";
    } else {
      email_Body  = "registration had been closed, thank you.";
    }
  

標籤：谷歌应用程序脚本 谷歌表格 邮箱

上一篇：有沒有辦法使用ArrayFormula或簡化這個VLOOKUP？

下一篇：條件格式，不能按行gSheets加粗