google-apps-script - 如何计算列中的值以使用它来定义变量以及如何使用 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});
解决方案
修改点:
关于
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 脚本,我认为这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});
}
}
参考:
添加:
当我看到您当前的脚本时,我认为您没有正确反映我提出的脚本。请按如下方式修改您当前的脚本。
从:
+ '<br/><br/>and more: '+ '<a href="${report_url}">Go to Google</a>'
到:
+ '<br/><br/>and more: '+ `<a href="${report_url}">Go to Google</a>`
- 请使用
`
而不是'
.
参考:
推荐阅读
- javascript - Jquery - 如何调用基于 console.log 数据的方法?
- c - QEMU pcie_host 如何将物理地址转换为 pcie 地址
- mysql - AWS Glue 读取数据库中默认设置的空值,如何避免?
- android - 全屏意图远程通知
- xamarin.forms - 将 Xamarin apk 发布到云存储而不是 Google Play 商店
- audio - 如何在 MacOS 上使用 pulseaudio 创建虚拟麦克风
- linux - 解压缩时将文件保存到s3
- java - 如何先构建 java 模块,然后构建我的 android 应用程序的其余部分?
- java - 如何检测显示屏上的内容是什么?
- typescript - RxJs `sample` 操作符多次