Google Sheets Mobile的自动电子邮件功能 [英] Auto Email Function for Google Sheets Mobile

问题描述

我正在尝试运行由Lauren 共享的惊人脚本Google表格的脚本(自动电子邮件功能)，并且有问题.我在stackoverflow上还很陌生，所以让我知道是否需要以其他方式询问这个问题，是否违反了任何规范?我已经安装并成功编辑了此脚本以执行我需要的操作.我有一个团队将在移动设备上使用它.我尝试过，但没有成功！您能帮我或指出正确的方向吗?

I am trying to run an amazing script shared by Lauren Script for Google sheets (auto email function) and have an issue. I'm fairly new on stackoverflow so let me know if i need to ask this in another way if I'm breaking any norms? I have setup and successfully edited this script to do what i need it to do. I have a team that will be using this on mobile devices. I tried it and it did not work! can you help or point me in the right direction?

这是针对任务列表的，当某人随后选择一个复选框时，它将向团队负责人发送任何添加的注释和详细信息(然后根据脚本取消选中该复选框).该复选框很棒，因为每行有一个.但欢迎任何建议

This is for a task list, when someone then selects a checkbox it sends any added notes and details to team leaders (and as per the script then unchecks the box). The checkbox is great as there is one per row. But open to any suggestions

很抱歉，您所提供的信息不够清晰，感谢@TheMaster在这里提供的帮助.代码在下面，但是听起来我正在尝试做不可能的事情?以为我可以用Google表格来做到这一点，但似乎在android ap上是不可能的.我不是很先进，可能需要放弃该项目！甚至有任何建议甚至可以通过其他方法来做到这一点?

Sorry for the lack of clarity, and thanks @TheMaster for helping out here. Code is below, but sounds like i am trying to do the impossible? Thought i would be able to do this with google sheets but it seems it is not possible on sheets android ap. I am not very advanced and might need to abandon the project! Any suggestions maybe even to do this in a different method?

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

}
}
}
}

推荐答案

移动应用不支持 ui 或警报.您只需要删除它们即可.

Mobile apps don't support ui or alerts. You just need to remove them.

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

这篇关于Google Sheets Mobile的自动电子邮件功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！