时间戳

首页 > 解决方案 > 谷歌表格电子邮件多单元格值

google-apps-script - 谷歌表格电子邮件多单元格值

问题描述

如何根据单元格中的附加值“拒绝”添加另一个自动电子邮件回复，然后发送不同的消息“您的请求被拒绝，请提交正确的详细信息”。请帮忙

    function sendEmail(e) {
    var ss = e.source.getActiveSheet();
    if (ss.getName() !== 'Form Responses 1' || e.range.columnStart !== 22 || 
    e.value !== 'Completed') return;
    var values = ss.getRange(e.range.rowStart, 2, 1, 7)
    .getValues()[0];
    var headers = ss.getRange(1, 2, 3, 7)
    .getValues()[0];
    var subject = "New Vendor Request Completed";
    var email = values[0];
    var cols = [1, 2, 3];
    var body = "Hi,\n\n We are please to let you know that the vendor that you have requested has been added to the system of you request. Please read the details below.\n\n"; //add more text if you want to..
    for (var i = 1; i < values.length; i++) {
    if (cols.indexOf(i) === -1) continue;
    body += headers[i] + ": " + values[i] + "\n"
    }
    MailApp.sendEmail(email, subject, body)

标签： google-apps-scriptgoogle-sheets

解决方案

我把这些放在一起主要是为了我自己的练习。我希望它做你想要的。

我将数据收集部分与电子邮件部分分开，然后创建了两个不同的电子邮件函数，由 if 语句调用以查找Completed或Declined编辑。 

function sendEmail(e) {
  var ss = e.source.getActiveSheet();
  if (ss.getName() !== 'Form Responses 1' || e.range.columnStart !== 22) return;
  var values = ss.getRange(e.range.rowStart, 2, 1, 7)
    .getValues()[0];
  var headers = ss.getRange(1, 2, 3, 7)
    .getValues()[0];
  if ( e.value == 'Completed') {
   completed(values, headers);
   }
  else if ( e.value == 'Declined') {
   declined(values, headers);
   }
  }


function completed(values, headers) {
  var subject = "New Vendor Request Completed";
  var email = values[0];
  var cols = [1, 2, 3];
  var body = "Hi,\n\n We are please to let you know that the vendor that you have requested has been added to the system of you request. Please read the details below.\n\n"; //add more text if you want to..
  for (var i = 1; i < values.length; i++) {
    if (cols.indexOf(i) === -1) continue;
      body += headers[i] + ": " + values[i] + "\n"
    }
  MailApp.sendEmail(email, subject, body)
  }

function declined(values, headers) {
  var subject = "New Vendor Request Declined";
  var email = values[0];
  var cols = [1, 2, 3];
  var body = "Hi,\n\n Unfortunately, the vendor that you have requested has not been added to the system of you request. Please read the details below.\n\n"; //add more text if you want to..
  for (var i = 1; i < values.length; i++) {
    if (cols.indexOf(i) === -1) continue;
     body += headers[i] + ": " + values[i] + "\n"
    }
  MailApp.sendEmail(email, subject, body)
  }

如果这不能满足您的需求，请告诉我。

推荐阅读