How can I count the values in a column to use it to define a variable and how can I display a url as-CodePudding

I've developed the below script (with the help of existing scripts in stackoverflow) and it does what it is intended to do. There are 2 things I want to change in the script but not sure how.

1- Instead of reading the value in a cell where I do a count of values, I want to count the values in the script

2- In the email I send out, I want to display a hyperlink text instead of the url.

Any help would be highly appreciated.

function sendEmails() {
  //enter the sheet name
  var sheetname = 'CFS Open Cases Report' 
  var counter_sheet = 'count of recipients'
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);

  //A1 is the cell that counts the number of rows in Column A. Try counting values in A2:A in the script instead
  var row_counter = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(counter_sheet).getRange("A2"); 
  var row_count = row_counter.getValue();
 

  var startRow = 2;  // First row of data to process
  var numRows = row_count;   // Number of rows to process

  //find a way to display the url as a hyperlink in the email body
  var report_url = "https://google.com";

 
  // Fetch the range of cells A2:D 
  var dataRange = sheet.getRange(startRow, 1, numRows, 4)
  // Fetch values for each row in the Range.

  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var first_name = row[0]; // First column
    var emailAddress = row[3]; // Fourth column
    
    //html message text
    var msgHtml = 'message and'   report_url
    ; 
    //today's date to be used as report date
    var report_date = Utilities.formatDate(new Date(), "GMT 1", "dd/MM/yyyy");
    var report_desc = "Report Name"
    var subject = report_date  ' - '  report_desc; 

            
    //send the email
    MailApp.sendEmail(emailAddress, subject, msgHtml);
  }
}

Here's the modified script:

function sendEmails() {
  var sheetname = 'Sheet1' // enter the sheet name where the recipient details are listed
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  var row_count = sheet.getRange("A2:A"   sheet.getLastRow()).getValues().flat().filter(String).length; // counts the rows in the A2:A range
  var startRow = 2; // first row of data to process
  var numRows =　row_count; // number of rows to process
      
  var report_url = "www.google.com";    


  // Fetch the range of cells A2:D 
  var dataRange = sheet.getRange(startRow, 1, numRows, 4)
  // Fetch values for each row in the Range.

  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var first_name = row[0]; // First column
    var emailAddress = row[3]; // Fourth column
    
    //html message text
    var msgHtml = 'Hi '   first_name  ',' 
      '<br/><br/>message here.'
      '<br/><br/>more message here.'
      '<br/><br/>and more: '  '<a href="${report_url}">Go to Google</a>'
      '<br/><br/>Kind Regards,'
      '<br/><br/>my name'
    ; 
    //today's date to be used as report date
    var report_date = Utilities.formatDate(new Date(), "GMT 1", "dd/MM/yyyy");
    var report_desc = "CFS Open Cases Report"
    var subject = report_date  ' - '  report_desc; 

    // clear html tags and convert br to new lines for plain mail    
    var msgPlain = msgHtml.replace(/\<br\/\>/gi, '\n').replace(/(<([^>] )>)/ig, ""); 
    
    //send the email
    MailApp.sendEmail(emailAddress, subject, msgPlain);
  }
}

Here's where I'm stuck with. I need to get rid of the html tags but keep the line breaks before I send the emails out:

var msgHtml = 'Hi '   first_name  ',' 
      '<br/><br/>second line.'
      '<br/><br/>third line.'
      '<br/><br/>fourth line '   `<a href="${report_url}">Go to Google</a>`
      '<br/><br/>fifth line,'
      '<br/><br/>sixth line'
    ;    

    //today's date to be used as report date
    var report_date = Utilities.formatDate(new Date(), "GMT 1", "dd/MM/yyyy");
    var report_desc = "CFS Open Cases Report"
    var subject = report_date  ' - '  report_desc; 

    // clear html tags and convert br to new lines for plain mail    
    var msgPlain = msgHtml.replace(/\<br\/\>/gi, '\n').replace(/(<([^>] )>)/ig, ""); 
    
    //send the email
    MailApp.sendEmail(emailAddress, subject, msgHtml); 
  }

Finally understood what was wrong:

MailApp.sendEmail(emailAddress, subject, msgHtml);

Changed the above to and it works now:

MailApp.sendEmail({to: emailAddress, subject: subject, htmlBody: msgHtml});

CodePudding user response：

Modification points:

About Instead of reading the value in a cell where I do a count of values, I want to count the values in the script, in order to covert counta(A2:A) to Google Apps Script, I thought that var row_count = counter_sheet_obj.getRange("A2:A" counter_sheet_obj.getLastRow()).getValues().flat().filter(String).length; might be useful.
About In the email I send out, I want to display a hyperlink text instead of the url., in order to send an email including the hyperlink of report_url, I thought that htmlBody can be used. For this, I modified var msgHtml = 'message and' report_url to var msgHtml = 'message and ' ${report_url};.

When these points are reflected in your script, it becomes as follows.

Modified script:

function sendEmails() {
  var sheetname = 'CFS Open Cases Report'
  var counter_sheet = 'count of recipients'
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  var counter_sheet_obj = ss.getSheetByName(counter_sheet);
  var row_count = counter_sheet_obj.getRange("A2:A"   counter_sheet_obj.getLastRow()).getValues().flat().filter(String).length;
  var startRow = 2;
  var numRows =　row_count;
  var report_url = "https://google.com";
  var dataRange = sheet.getRange(startRow, 1, numRows, 4)
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var first_name = row[0]; // This is not used in your script.
    var emailAddress = row[3];
    var msgHtml = 'message and '   `<a href="${report_url}">${report_url}</a>`;
    var report_date = Utilities.formatDate(new Date(), "GMT 1", "dd/MM/yyyy");
    var report_desc = "Report Name"
    var subject = report_date   ' - '   report_desc;
    MailApp.sendEmail({to: emailAddress, subject: subject, htmlBody: msgHtml});
  }
}

Reference:

sendEmail(message) of Class MailApp

Added:

When I saw your current script, I think that you don't correctly reflect my proposed script. Please modify your current script as follows.

From:

  '<br/><br/>and more: '  '<a href="${report_url}">Go to Google</a>'

To:

  '<br/><br/>and more: '  `<a href="${report_url}">Go to Google</a>`

Please use ` instead of '.

Reference:

Template literals