AppsScriptGoogleSheetsPOPup選擇是或否總是發送電子郵件

我有一個帶有下拉選單的谷歌表。當下拉串列所在的單元格等于某個字串時，螢屏上會出現一個彈出視窗。你回答是或否。當您回答是時，它會發送電子郵件。如果您回答“否”，則它不會發送電子郵件并將單元格重置為之前的狀態。我遇到的問題是，如果您選擇“否”，它確實會重置為以前的資料，但無論如何它仍然會發送電子郵件。請幫忙。我是新手，還在學習。

更新： 請參閱下面的更新。我必須修復的唯一變數是 rData，除了腳本現在按預期作業。非常感謝您的時間和投入。這讓我頭疼了一段時間。

function sendMailEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  if (ss.getSheetName() == 'Project List' &
    ss.getActiveCell().getColumn() == 3 &
    ss.getActiveCell().getValue() == "_Complete") {

    var alertPromptText = 'Are you sure you want to select Complete? '  
      'This action will notify all parties in the next process that this job is ready for fabrication.';

    var promptResponse = ui.alert(alertPromptText, ui.ButtonSet.YES_NO);

    if (promptResponse == ui.Button.YES) {

      // CHECK START
      // variable email needs to be fixed. It gets the column of values. 
      // it needs to be converted to a comma separated list of recepients
      var email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange(1, 1, 100).getValues();
      // CHECK END

      var rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,12).getValues();
      sendEmail(email,rData);

       

    } else { // For both 'No' and cancel response to pop-up
      fix(e);
    }
  }
}

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

function sendEmail(email,rData) {
  var first = 0;
  var email = 1;

  var emailTemp = HtmlService.createTemplateFromFile("send");
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email");
  var data = ws.getRange("A1:B"   ws.getLastRow()).getValues();
  var lj = rData[0][1];
  var j = rData[0][5];
  var d = rData[0][3];
  var p = rData[0][4];
  var m = rData[0][7];
  var desc = rData[0][11];
  var now = new Date().toLocaleString("en-US");
  var msg1 = "Laser Job Number: "   lj   " ("   now   ")"  
    "\nProject Job Number: "   j  
    "\nDesigner: "   d  
    "\nDate Project was Submitted to Programming: "   p  
    "\nMaterial used: "   m  
    "\nDescription: "   desc;
    var subject = "Project Ready for Fab";

  
  Logger.log(msg1);

  data.forEach(function(row){
    
    emailTemp.fn = row[first];
    emailTemp.msg = msg1;
    emailTemp.j = j;
    emailTemp.d = d;
    emailTemp.lj = lj;
    emailTemp.p = p;
    emailTemp.m = m;
    emailTemp.desc = desc;
    //emailTemp.cart = cart;

    const htmlMessage = emailTemp.evaluate().getContent();
    
GmailApp.sendEmail(row[email], subject, "Please open with an email client that supports HTML",
{htmlBody: htmlMessage});
  return;

  });
  
}

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

function fix(e) {
  e.range.setNote(e.oldValue);
  e.range.setValue(e.range.getNote());
  e.range.clearNote();
}

uj5u.com熱心網友回復：

您需要設定一個變數來捕獲對彈出視窗的回應。

然后比較腳本的 go-no-go 部分的變數值。

例如：

var response = ui.prompt('Alert', 'Are you sure you want to select Complete?', ui.ButtonSet.YES_NO);

進而

if (response.getSelectedButton() == ui.Button.YES) { ... }

更多資訊請參見檔案。

更新

試試下面的腳本。您需要設定 onEdit 觸發器來運行該函式SendMailEdit

此外，檢查獲取電子郵件收件人串列的代碼。不確定它是否會起作用。

function sendMailEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();

  if (ss.getSheetName() == 'Project List' &
    ss.getActiveCell().getColumn() == 3 &
    ss.getActiveCell().getValue() == "_Compvare") {

    var alertPromptText = 'Are you sure you want to select Compvare? '  
      'This action will notify all parties in the next process that this job is ready for fabrication.';

    var promptResponse = ui.alert(alertPromptText, ui.ButtonSet.YES_NO);

    if (promptResponse.getSelectedButton() == ui.Button.YES) {

      // CHECK START
      // variable email needs to be fixed. It gets the column of values. 
      // it needs to be converted to a comma separated list of recepients
      var email = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Email").getRange(1, 1, 100).getValues();
      // CHECK END

      var rData = ss.getRange(ss.getActiveCell().getRow(), 1, 1, 12).getValues();
      sendEmail(email, rData);

    } else { // For both 'No' and cancel response to pop-up
      fix(e);
    }
  }
}

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

function sendEmail(email, rData) {
  var lj = rData[0][1];
  var j = rData[0][5];
  var d = rData[0][3];
  var p = rData[0][4];
  var m = rData[0][7];
  var desc = rData[0][11];
  var now = new Date().toLocaleString("en-US");
  var msg = "Laser Job Number: "   lj   " ("   now   ")"  
    "\nProject Job Number: "   j  
    "\nDesigner: "   d  
    "\nDate Project was Submitted to Programming: "   p  
    "\nMaterial used: "   m  
    "\nDescription: "   desc;
  Logger.log(msg);
  GmailApp.sendEmail(email, "Project Ready for Fab", msg);
  return;
}

////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////
////////////////////////////////////////////////////////////////////////////

function fix(e) {
  e.range.setNote(e.oldValue);
  e.range.setValue(e.range.getNote());
  e.range.clearNote();
}

標籤：谷歌应用程序脚本 谷歌表格

上一篇：通過邏輯應用過濾GoogleSheet檔案上的GetRows

下一篇：從Google表格中的數字中洗掉尾隨的“.00”