How to pick the value numbers from another sheet using google app script?

Question

I have 2 google sheet tabs, first sheet1 have col A - Email address, Col B - message, Col C - status. and second sheet2 have only one column Col A - voucher code, I have insert all my necessary numbers in sheet2 Col A. So each time I run my script will send email out and automatically pick the numbers from sheet2 base on the last row of sheet1 data. Email sent without issue, but I fail to get voucher code data from sheet2 Col A in my email.

var EMAIL_SENT = 'EMAIL_SENT';

function sendEmails2() {
var sheet = SpreadsheetApp.getActiveSheet();
  

var sheet2 = SpreadsheetApp.getActive().getSheetByName("Sheet2");
var strRow2 = 2;
var numRows2 = 2;
var dataRange2 = sheet2.getRange(strRow2, 1, numRows2, 1)
 
var data2 = dataRange2.getValue();
  
for (var j = 0; j < data2.length; ++j) {    
var row2 = data2[j];
var code = row2[0];
} 
  
var startRow = 2; 
var numRows = 2; 

var dataRange = sheet.getRange(startRow, 1, numRows, 3);

var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var emailAddress = row[0]; 
var message = row[1] + "\n" + code // this code no working 
var emailSent = row[2]; 
if (emailSent !== EMAIL_SENT) 
var subject = 'Sending emails from a Spreadsheet';
MailApp.sendEmail(emailAddress, subject, message);
sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT); 
SpreadsheetApp.flush();
}
}
}

This code does not work and it is very bad formatted. Can you show us a minimal reproducible example? Also which voucher number do you need ? there is no matching key between Sheet1 and Sheet2. Please improve the question so the community will be able to help you. — soMario
Sorry, I'm new on google app script and new for this posting, i try my best to improve my question. i amend my screenshot, hopefully your guy will understand what i need, thank you. — Daniel Hoong
Daniel, see my posted answer. I assumed that the subject of the email will be the message (COL B, sheet1) and the message will be the voucher. Let me know if this is what you want. — soMario
thank you very much for your suggestion solution, really appreciated, yes this is what i want, sorry for make you confusing. email subject i did put in the google sheet, message is email body and how to use htmlbody instead of body ? — Daniel Hoong

soMario soMario · Accepted Answer · 2020-08-06T10:27:47

In order to find the last row that has content you should use: getLastRow().

Here is the solution:

function sendEmail() {
  
  var ss = SpreadsheetApp.getActive();
  var sh1 = ss.getSheetByName("Sheet1")
  var sh2 = ss.getSheetByName("Sheet2")
  var EMAIL_SENT = 'EMAIL_SENT';
  
  var sh1_LR = sh1.getLastRow();

  var sh1_EA = sh1.getRange(sh1_LR,1).getValue();
  var sh1_M = sh1.getRange(sh1_LR,2).getValue();
  var sh2_V = sh2.getRange(sh1_LR,1).getValue();
 
  try{
  MailApp.sendEmail(sh1_EA, sh1_M, sh2_V);
  sh1.getRange(sh1_LR,3).setValue(EMAIL_SENT);
  }
  catch(e)
  {Logger.log("sendEmail failed")}     
}

How to pick the value numbers from another sheet using google app script?

1 Answers