how do I replace text in google sheets using script editor

Question

I am trying to get the text replaced in an email using script editor in google sheets but it does not work can anyone help TIA Link:

https://docs.google.com/spreadsheets/d/1zsfQFrx-Q-L3gqJvuD5b0GIsK5Yab_0eaRM4sCgnxDg/edit?usp=sharing

Script:

var EMAIL_SENT = 'EMAIL_SENT';
function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; 
  var numRows = 5000; 

  var dataRange = sheet.getRange(startRow, 1, numRows, 5000);
  var data = dataRange.getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var emailAddress = row[0]; 
    var message = row[1]; 
    var emailSent = row[2];
    var Name = row[3];
    var Date = row[4];
    var Boat = row[6];
    var Trip = row[7];
    var Link = row[8];

    messagebody = message.replace("{Name}",Name).replace("{Download Link}",Link).replace("{Boat}",Boat).replace("{Trip}",Trip).replace("{Date}",Date)

    if (emailSent !== EMAIL_SENT) {
      var subject = 'Your Movie Link';
      MailApp.sendEmail(emailAddress, subject, message);
      sheet.getRange(startRow + i, 3).setValue(EMAIL_SENT);

      SpreadsheetApp.flush();
    }
  }
}

Answer 1

Try this:

function sendEmails2() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());
  var data=dataRange.getValues();
  for (var i=0;i<data.length;++i) {
    var row=data[i];
    var emailAddress = row[0]; 
    var message = row[1]; 
    var emailSent = row[2];
    var Name = row[3];
    var Date = row[4];
    var Boat = row[6];
    var Trip = row[7];
    var Link = row[8];
    var messagebody=Utilities.formatString('Name: %s\nDownload link: %s\nBoat: %s\nTrip: %s\nDate: %s\n',Name,Link,Boat,Trip,Date);
    if (emailSent!="EMAIL_SENT") {
      var subject='Your Movie Link';
      MailApp.sendEmail(emailAddress, subject, messagebody);
      sheet.getRange(startRow + i, 3).setValue("EMAIL_SENT");
    }
  }
}