Google app script : call another function after reached last row on google sheet

user_14020657 0
Viewed 43

May I don't know how to call another function when reached last row of register code column on google sheet, users click the submit on google form will auto email out another sendMail function to said that eg 'registration had been closed, thank you.'

enter image description here

my code :

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);
}
google-apps-script google-sheets gmail
1 Answers

I believe your goal is as follows.

  • In your situation, the values are put to the columns "A" and "B". At that time, your function sendmail is run. At this time, when the values are put to the columns "A" and "B", when the row is over the last row of the column "C", you want to send an email body of registration had been closed, thank you..

If my understanding is correct, how about the following modification?

Modified script:

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)) {,,,}, in this script, when the values of columns "A" and "B" are put to the next row of the last row of the column "C", email_Body = "registration had been closed, thank you." is used. When you want to use this email_Body at the last row of the column "C", please modify (lastRow < sh1.get1stEmptyRowFromTop(3)) to (lastRow < sh1.get1stEmptyRowFromTop(3) - 1).

  • When you want to add "registration had been closed, thank you." to email_Body, when the values are put to the same row of the last row of the column "C", please modify the above if statement as follows.

      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.";
  }
user_71086530
Related