时间戳

首页 > 解决方案 > Google Sheets Mobile 的自动电子邮件功能

email - Google Sheets Mobile 的自动电子邮件功能

问题描述

我正在尝试运行 Lauren Script for Google 表格（自动电子邮件功能）共享的惊人脚本，但遇到了问题。我对stackoverflow相当陌生，所以如果我违反任何规范，如果我需要以另一种方式问这个问题，请告诉我？我已经设置并成功编辑了这个脚本来做我需要它做的事情。我有一个团队将在移动设备上使用它。我试过了，它没有用！你能帮助或指出我正确的方向吗？

这是一个任务列表，当有人然后选择一个复选框时，它会将任何添加的注释和详细信息发送给团队领导（然后根据脚本取消选中该框）。该复选框很棒，因为每行有一个。但愿意接受任何建议

抱歉，不清楚，感谢@TheMaster 在这里提供帮助。代码如下，但听起来我正在尝试做不可能的事情？以为我可以用谷歌表格做到这一点，但似乎在表格 android ap 上是不可能的。我不是很先进，可能需要放弃这个项目！有什么建议甚至可以用不同的方法来做到这一点？

function EmailNotification(e) {

var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Deliveries'); //source sheet
  var columnb = sheet.getRange('J:J'); //Column with check boxes
var columnbvalue = columnb.getValues();
var notifysheet = ss.getSheetByName('Responses'); //destination sheet
var data = [];
var rownum =[];

//Condition check in B:B (or check box column); If true copy the same row to data array

for (i=0; i<columnbvalue.length;i++) {

if (columnbvalue[i] == 'true') {

var columnb2 = sheet.getRange('J2:J');
columnb2.setValue('false');

// What you want to say in the pop up alert

var response = ui.alert('Hold Up!\n Are you sure you want to send an email to team leaders for this  delivery?', ui.ButtonSet.YES_NO);
if (response == ui.Button.YES) {

data.push.apply(data,sheet.getRange(i+1,1,1,20).getValues());

//Copy matched ROW numbers to rownum

rownum.push(i);

//Copy data array to destination sheet


notifysheet.getRange(notifysheet.getLastRow()+1,1,data.length,data[0].length).setValues(data);

var activeRow = notifysheet.getLastRow();
var suppliername = notifysheet.getRange(activeRow, 1).getDisplayValue(); // The number is the column number in the destination "responses" sheet that you want to include in the email
var owner = notifysheet.getRange(activeRow, 3).getDisplayValue();
var recievedby = notifysheet.getRange(activeRow, 8).getDisplayValue();
var instructions = notifysheet.getRange(activeRow, 5).getDisplayValue();
var status = notifysheet.getRange(activeRow, 7).getDisplayValue();
var notes = notifysheet.getRange(activeRow, 9).getDisplayValue();
var email = notifysheet.getRange(activeRow, 4).getDisplayValue();

var subject = 'Delivery completed!'

//Body of the email message, using HTML and the variables from above

var message =
'<br><br><div style="margin-left:40px;">Hello '+ owner +'</div>'
+'<br><br><div style="margin-left:40px;">A delivery you requested has been fulfilled</div>'
+'<br><br><div style="margin-left:40px;"><h3 style="text-decoration: underline;">Recieved Name: '+ recievedby +'</h3></div>'
+'<div style="margin-left:40px;">Supplier: '+ suppliername +'</div>'
+'<div style="margin-left:40px;">Special instructions you added: '+ instructions +'</div>'
+'<div style="margin-left:40px;">Status: '+ status +'</div>'
+'<div style="margin-left:40px;">Notes: '+ notes +'</div>'
+ '<br><br><div style="margin-left:40px;">ヽ(⌐■_■)ノI Did It ♪♬</div>';

MailApp.sendEmail(
email,
subject,
"",
{
htmlBody: message,
name: 'Shop Alerts', //The name you want to email coming from
});

}
}
}
}

标签： emailgoogle-apps-scriptgoogle-sheetsmobiletriggers

解决方案

移动应用程序不支持ui或警报。你只需要删除它们。

function EmailNotification(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Deliveries'); //source sheet
  var columnb = sheet.getRange('J:J'); //Column with check boxes
  var columnbvalue = columnb.getValues();
  var notifysheet = ss.getSheetByName('Responses'); //destination sheet
  var data = [];
  var rownum = [];

  //Condition check in B:B (or check box column); If true copy the same row to data array

  for (let i = 0; i < columnbvalue.length; i++) {
    if (columnbvalue[i][0] === true) {
      //modified to strict equality
      var columnb2 = sheet.getRange('J2:J');
      columnb2.setValue('false');
      data.push.apply(data, sheet.getRange(i + 1, 1, 1, 20).getValues());

      //Copy matched ROW numbers to rownum

      rownum.push(i);

      //Copy data array to destination sheet

      notifysheet
        .getRange(notifysheet.getLastRow() + 1, 1, data.length, data[0].length)
        .setValues(data);

      var activeRow = notifysheet.getLastRow();
      var suppliername = notifysheet.getRange(activeRow, 1).getDisplayValue(); // The number is the column number in the destination "responses" sheet that you want to include in the email
      var owner = notifysheet.getRange(activeRow, 3).getDisplayValue();
      var recievedby = notifysheet.getRange(activeRow, 8).getDisplayValue();
      var instructions = notifysheet.getRange(activeRow, 5).getDisplayValue();
      var status = notifysheet.getRange(activeRow, 7).getDisplayValue();
      var notes = notifysheet.getRange(activeRow, 9).getDisplayValue();
      var email = notifysheet.getRange(activeRow, 4).getDisplayValue();
      var subject = 'Delivery completed!';
      //Body of the email message, using HTML and the variables from above

      var message =
        '<br><br><div style="margin-left:40px;">Hello ' +
        owner +
        '</div>' +
        '<br><br><div style="margin-left:40px;">A delivery you requested has been fulfilled</div>' +
        '<br><br><div style="margin-left:40px;"><h3 style="text-decoration: underline;">Recieved Name: ' +
        recievedby +
        '</h3></div>' +
        '<div style="margin-left:40px;">Supplier: ' +
        suppliername +
        '</div>' +
        '<div style="margin-left:40px;">Special instructions you added: ' +
        instructions +
        '</div>' +
        '<div style="margin-left:40px;">Status: ' +
        status +
        '</div>' +
        '<div style="margin-left:40px;">Notes: ' +
        notes +
        '</div>' +
        '<br><br><div style="margin-left:40px;">ヽ(⌐■_■)ノI Did It ♪♬</div>';

      MailApp.sendEmail(email, subject, '', {
        htmlBody: message,
        name: 'Shop Alerts', //The name you want to email coming from
      });
    }
  }
}

推荐阅读