如何計算列中的值以使用它來定義變數，以及如何使用Google應用程式腳本將url顯示為超鏈接（文本）-有解無憂

我已經開發了以下腳本（在 stackoverflow 中現有腳本的幫助下）并且它完成了它的意圖。我想在腳本中更改兩件事，但不確定如何更改。

1-我想計算腳本中的值，而不是讀取我計算值的單元格中的值

2- 在我發送的電子郵件中，我想顯示一個超鏈接文本而不是 url。

任何幫助將不勝感激。

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);
  }
}

這是修改后的腳本：

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);
  }
}

這就是我堅持的地方。我需要去掉 html 標簽，但在發送電子郵件之前保留換行符：

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); 
  }

終于明白哪里不對了：

MailApp.sendEmail(emailAddress, subject, msgHtml);

將以上更改為現在可以使用了：

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

uj5u.com熱心網友回復：

改裝要點：

關于Instead of reading the value in a cell where I do a count of values, I want to count the values in the script，為了隱蔽counta(A2:A)到 Google Apps Script，我認為這var row_count = counter_sheet_obj.getRange("A2:A" counter_sheet_obj.getLastRow()).getValues().flat().filter(String).length;可能有用。
關于In the email I send out, I want to display a hyperlink text instead of the url.，為了發送包含的超鏈接的電子郵件report_url，我認為htmlBody可以使用。為此，我修改var msgHtml = 'message and' report_url為var msgHtml = 'message and ' ${report_url} ;。

當這些點反映在您的腳本中時，它變成如下。

修改后的腳本：

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});
  }
}

參考：

類 MailApp 的 sendEmail(message)

添加：

當我看到您當前的腳本時，我認為您沒有正確反映我建議的腳本。請按如下方式修改您當前的腳本。

從：

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

到：

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

Please use ` instead of '.

Reference:

Template literals

轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/332793.html

標籤：谷歌应用程序脚本谷歌表格数数超链接

上一篇：用選擇陳述句中創建的基于條件的列連接兩個表

下一篇：根據今天日期 2個作業日在谷歌表格中鎖定單元格