我的问题是，我想修改appscript中的现有脚本，以便在邮件室跟踪器中不导入电子邮件地址。因此，我希望脚本在运行电子邮件发送按钮时执行以下操作：
- 脚本在另一个工作簿(其他谷歌工作表(中查找相同的收件人名称，然后在右侧的下一个单元格中获取相应的电子邮件地址
- 当它有时，它会将其临时存储在数组中
- 使用它，然后删除表
- 包含电子邮件的文档不应在计算机上打开，而应以虚拟方式打开
以下是我想修改的工作表和脚本的示例(url都可以访问(：
- mailroon跟踪器："https://docs.google.com/spreadsheets/d/16ub8m4mHscHuOhuDxNHJI5XTrNPqd2qNdBCjeansUI8/edit?usp=sharing">
- 人员列表和电子邮件地址：；https://docs.google.com/spreadsheets/d/1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels/edit?usp=sharing">
- 列出项目
如果你能帮我做这件事，并用粗线条向我解释你做了什么，那将对我有很大帮助！！
谨致问候，康斯坦丁
以下是我所做的：
// you need to activate the script, give authorization, and put a triiger on onSpeEdit once
function onOpen(e) {
var ui = SpreadsheetApp.getUi();
ui.createMenu('** ACTIVATION **')
.addItem('👉 Activate', 'activate')
.addToUi();
}
function activate(){
if(!isTrigger('onSpeEdit')) {
ScriptApp.newTrigger('onSpeEdit')
.forSpreadsheet(SpreadsheetApp.getActive())
.onEdit()
.create();
}
SpreadsheetApp.getActive().toast('your script is now active !')
}
// main function
function onSpeEdit(event) {
var ws = event.source.getActiveSheet()
var cel = event.source.getActiveRange()
if (cel.getValue()){
var wb = SpreadsheetApp.openById('1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels')
var mails = wb.getSheetByName('On site')
var data = mails.getDataRange().getValues()
var recipientName = ws.getRange(cel.getRow(),4).getValue()
var emailAddress = ''
for (var i=0;i<data.length;i++){
if (data[i][1] == recipientName){
emailAddress = data[i][2]
}
}
SpreadsheetApp.getActive().toast(recipientName + ' ' + emailAddress)
// ############### send email here ###############
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2;
var numRows = sheet.getLastRow()-1;
var dataRange = sheet.getRange(startRow, 1, numRows, 10)
var data = dataRange.getValues();
Logger.log(data)
for (i in data) {
var row = data[i];
var Date = row[0];
var Tracking = row[1];
var Carrier = row[2];
var Recepient = row [4]
var Business = row[5];
var EmailSent = row[8].trim();
var PickedUp = row [9]
var Reminder = row[10];
if (Tracking !="" && EmailSent!="X" && PickedUp=="") {
Logger.log("IncomingDate="+Tracking +" Sent to ="+ emailAddress);
MailApp.sendEmail(
emailAddress+"gmail.com",
"Your Parcel from "+Carrier+ " has been delivered today",
"Hi "+Recepient+",nn"+
"I hope you are having a great day so far.nnI just received a parcel for you delivered by "+Carrier+" with tracking number "+TrackingNumber+". As from now, you can pick it up from the B-BRU office mailroom on the 3rd floor from Monday till Friday between 8:30am-5pm.n nShould you have any other questions or concerns, please do not hesitate to reach out to us. We are always happy to help you!:) nn"+
"Best regards,nn"+
"The B-BRU Reception team nnn");
}
sheet.getRange("F"+(parseInt(i)+2).toString()).setValue("X");
}
}
}
// trigger management
function myTriggerSetup(nom) {
if(!isTrigger(nom)) {
ScriptApp.newTrigger(nom)
.timeBased()
.everyMinutes(1)
.create();
}
}
function myTriggerDelete(nom){
deleteTriggersByName(nom);
}
function deleteTriggersByName(name){
var triggers = ScriptApp.getProjectTriggers();
for (var i = 0; i < triggers.length; i++){
if (triggers[i].getHandlerFunction().indexOf(name) != -1)
{
ScriptApp.deleteTrigger(triggers[i]);
}
}}
function isTrigger(funcName) {
var r=false;
if(funcName) {
var allTriggers=ScriptApp.getProjectTriggers();
var allHandlers=[];
for(var i=0;i<allTriggers.length;i++) {
allHandlers.push(allTriggers[i].getHandlerFunction());
}
if(allHandlers.indexOf(funcName)>-1) {
r=true;
}
}
} return r;
}
您可以使用获取信息
function myFunction() {
var wb = SpreadsheetApp.openById('1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels')
var ws = wb.getSheetByName('On site')
var data = ws.getDataRange().getValues()
Logger.log(data)
}
参考
openById
编辑
您的完整脚本：
// main function
function onSpeEdit(event) {
var ws = event.source.getActiveSheet()
var cel = event.source.getActiveRange()
if (cel.getValue()){
var wb = SpreadsheetApp.openById('1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels')
var mails = wb.getSheetByName('On site')
var data = mails.getDataRange().getValues()
var recipientName = ws.getRange(cel.getRow(),4).getValue()
var emailAddress = ''
for (var i=0;i<data.length;i++){
if (data[i][1] == recipientName){
emailAddress = data[i][2]
}
}
var row=ws.getRange(cel.getRow(),1,1,ws.getLastColumn()).getValues()
var Date = row[0][0];
var Tracking = row[0][1];
var Carrier = row[0][2];
var Recepient = row[0][3]
var Business = row[0][4];
var EmailSent = row[0][5].trim();
var PickedUp = row[0][6];
var Reminder = row[0][7];
if (Tracking !="" && EmailSent!="X" && PickedUp=="") {
MailApp.sendEmail({
to: emailAddress+"@gmail.com",
subject: "Your Parcel from "+Carrier+ " has been delivered today",
htmlBody: "Hi "+Recepient+",<br><br>I hope you are having a great day so far.<br><br>I just received a parcel for you delivered by "+Carrier+" with tracking number "+Tracking+". As from now, you can pick it up from the B-BRU office mailroom on the 3rd floor from Monday till Friday between 8:30am-5pm.<br> <br>Should you have any other questions or concerns, please do not hesitate to reach out to us. We are always happy to help you!:) <br><br>Best regards,<br><br>The B-BRU Reception team <br><br><br>"
})
SpreadsheetApp.getActive().toast("IncomingDate = "+Tracking +" Sent to ="+ emailAddress);
}
ws.getRange("F"+cel.getRow()).setValue("X");
}
}
- 首先，您必须激活脚本，授予授权，并在onSpeEdit函数上设置触发器
- 然后选中一个框
- 我添加了一些额外的触发管理功能
编辑2
如果你想用按钮做同样的事情
function sendEmails() {
var ws =SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
for (var ligne=1;ligne<=ws.getLastDataRow('A');ligne++){
if (ws.getRange('E'+ligne).getValue() && ws.getRange("F"+ligne).getValue()==''){
var wb = SpreadsheetApp.openById('1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels')
var mails = wb.getSheetByName('On site')
var data = mails.getDataRange().getValues()
var recipientName = ws.getRange(ligne,8).getValue()
var emailAddress = ''
for (var i=0;i<data.length;i++){
if (data[i][1] == recipientName){
emailAddress = data[i][2]
}
}
var row=ws.getRange(ligne,1,1,ws.getLastColumn()).getValues()
var Date = row[0][0];
var Tracking = row[0][1];
var Carrier = row[0][2];
var Recepient = row[0][3]
var Business = row[0][4];
var EmailSent = row[0][5].trim();
var PickedUp = row[0][6];
var Reminder = row[0][7];
var SendEmail = row[0][8]
if (Tracking !="" && EmailSent!="X" && PickedUp=="") {
MailApp.sendEmail({
to: emailAddress+"@gmail.com",
subject: "Your Parcel from "+Carrier+ " has been delivered today",
htmlBody: "Hi "+Recepient+",<br><br>I hope you are having a great day so far.<br><br>I just received a parcel for you delivered by "+Carrier+" with tracking number "+Tracking+". As from now, you can pick it up from the B-BRU office mailroom on the 3rd floor from Monday till Friday between 8:30am-5pm.<br> <br>Should you have any other questions or concerns, please do not hesitate to reach out to us. We are always happy to help you!:) <br><br>Best regards,<br><br>The B-BRU Reception team <br><br><br>"
})
SpreadsheetApp.getActive().toast("IncomingDate = "+Tracking +" Sent to ="+ emailAddress);
}
ws.getRange("F"+ligne).setValue("X");
}
}
}
Object.prototype.getLastDataRow = function(col){
var lastRow = this.getLastRow();
var range = this.getRange(col + lastRow);
if (range.getValue() !== "") {
return lastRow;
} else {
return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
};
您可以使用openById((或**openByURL**方法从该电子邮件表单中提取数据。
function extractEmails() {
var ss = SpreadsheetApp.openById('1Ag6RMAITxpEEG6D7kGArBv4EbH3cRdT8nUPkyYhfels')//This will access your spreadsheet "virtually"
var ssname = wb.getSheetByName('Employee_Email_SheetName')//This will access the following sheet in that spreadsheet, make sure to use proper sheet name
var emailData = ws.getDataRange().getValues() // this will get the data from that sheet
return emailData
}假设你只需要来自该表特定列的电子邮件，例如：C1:C20有你的电子邮件信息，在这种情况下你可以替换
ws.getDataRange().getValues()
带有
ws.getRange('C1:C20').getValues() //C1:C20 is your email range
参考：
openById(id(
openByUrl(url(