根据单元格值发送电子邮件 [英] Sending Email Based on Cell Value

问题描述

电子邮件功能可以正常工作，但是代码具有以下错误:

The email function works fine, but the code has the following error:

SyntaxError:添加函数checkValue时，输入的意外结束(第90行，文件"Code.gs")(最后一行).

SyntaxError: Unexpected end of input (line 90, file "Code.gs") (last line) when the function checkValue was added.

基本上，我们希望在编辑D2时自动发送电子邮件.我们希望这将消除在每次使用辅助工具复制模板时都允许发送电子邮件的权限的需求.

Basically, we want the email to send automatically when D2 is edited. We are hoping this will eliminate the need to allow permissions to send email every time a template is copied by an aid.

    function checkValue () {
var check = sheet.getRange("D2").getValue();
var rangeEdit =e.range.getA1Notation();
if(rangeEdit == "D2") {
  {
   
function email(checkValue) {
 
 

  // Send the PDF of the spreadsheet to this email address
 

  // Get the currently active spreadsheet URL (link)
  // Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
   
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("ClassA")
   
  var lrow = sheet.getLastRow()
   
 
  var name = sheet.getRange("E4").getValue();
  var aid = sheet.getRange("E3").getValue();
  var email = sheet.getRange("E5").getValue();
  var pemail = sheet.getRange("E2").getValue();
 
  var period = sheet.getRange("C1").getValue();
 var og= sheet.getRange("D2").getValue();
 
 



  // Subject of email message
  var subject = "Grade Summary | " + og +"- " + period;

  // Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail
  var body = "Hi " + name + ", "+ "<br><br> Please find the grade summary  attached for " + period + ". <br><br>  Let us know if you have any questions.<br><br>  Thank you,<br><br> " + aid;
     
  var aliases = GmailApp.getAliases()
Logger.log(aliases); //returns the list of aliases you own
Logger.log(aliases[0]); //returns the alias located at position 0 of the aliases array
 
  // Base URL
  var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

  /* Specify PDF export parameters
  From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
  */

  var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
  + '&size=letter'                       // paper size legal / letter / A4
  + '&portrait=True'                    // orientation, false for landscape
  + '&fitw=true'           // fit to page width, false for actual size
  + '&sheetnames=false&printtitle=false' // hide optional headers and footers
  + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
  + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
  + '&gid=';                             // the sheet's Id

  var token = ScriptApp.getOAuthToken();


  //make an empty array to hold your fetched blobs  
  var blobs;


    // Convert your specific sheet to blob
    var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
      headers: {
        'Authorization': 'Bearer ' +  token
      }
    });

    //convert the response to a blob and store in our array
    blobs = response.getBlob().setName(sheet.getName() + '.pdf');


  // Define the scope
  Logger.log("Storage Space used: " + DriveApp.getStorageUsed());
   
    MailApp.sendEmail(email, subject, body, {
      htmlBody: body,
 
      name:'class',
      bcc: aid,
      noReply: true,
      attachments:[blobs]    
    });  
}   

推荐答案

您的代码没有足够的右括号来终止函数 checkValue ，因此它返回语法错误.

Your code does not have enough closing brackets to terminate the function checkValue, thus it returns a syntax error.

由于您的意图是在发送电子邮件之前先检查该值，所以最好将 email 函数分开并从 checkValue 调用它，而不是将其包含在其中一个if语句.也不需要该参数.

Since your intent is to check the value first before sending the email, it is better if you separate the email function and call it from checkValue instead of enclosing it in an if statement. The parameter is not needed as well.

此外，由于您指出需要发送电子邮件，因此需要可安装的触发器.通过运行 createSpreadsheetEditTrigger()使用脚本服务创建触发器，然后将运行 checkValue().

Also since you indicated that you need to send an email, you need an installable trigger. Create a trigger using the script service by running createSpreadsheetEditTrigger(), which will then run checkValue().

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger('checkValue')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

function checkValue(e) {
  var rangeEdit = e.range.getA1Notation();
    if(rangeEdit == "D2") {
      email();
    }
}

function email() {
          // Send the PDF of the spreadsheet to this email address

          // Get the currently active spreadsheet URL (link)
          // Or use SpreadsheetApp.openByUrl("<<SPREADSHEET URL>>");
          
          var ss = SpreadsheetApp.getActiveSpreadsheet();
          var sheet = ss.getSheetByName("ClassA")
          
          var lrow = sheet.getLastRow()
          
        
          var name = sheet.getRange("E4").getValue();
          var aid = sheet.getRange("E3").getValue();
          var email = sheet.getRange("E5").getValue();
          var pemail = sheet.getRange("E2").getValue();
        
          var period = sheet.getRange("C1").getValue();
        var og= sheet.getRange("D2").getValue();
        
        



          // Subject of email message
          var subject = "Grade Summary | " + og +"- " + period;

          // Email Body can  be HTML too with your logo image - see ctrlq.org/html-mail
          var body = "Hi " + name + ", "+ "<br><br> Please find the grade summary  attached for " + period + ". <br><br>  Let us know if you have any questions.<br><br>  Thank you,<br><br> " + aid;
            
          var aliases = GmailApp.getAliases()
        Logger.log(aliases); //returns the list of aliases you own
        Logger.log(aliases[0]); //returns the alias located at position 0 of the aliases array
        
          // Base URL
          var url = "https://docs.google.com/spreadsheets/d/SS_ID/export?".replace("SS_ID", ss.getId());

          /* Specify PDF export parameters
          From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
          */

          var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
          + '&size=letter'                       // paper size legal / letter / A4
          + '&portrait=True'                    // orientation, false for landscape
          + '&fitw=true'           // fit to page width, false for actual size
          + '&sheetnames=false&printtitle=false' // hide optional headers and footers
          + '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
          + '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
          + '&gid=';                             // the sheet's Id

          var token = ScriptApp.getOAuthToken();


          //make an empty array to hold your fetched blobs  
          var blobs;


            // Convert your specific sheet to blob
            var response = UrlFetchApp.fetch(url + url_ext + sheet.getSheetId(), {
              headers: {
                'Authorization': 'Bearer ' +  token
              }
            });

            //convert the response to a blob and store in our array
            blobs = response.getBlob().setName(sheet.getName() + '.pdf');


          // Define the scope
          Logger.log("Storage Space used: " + DriveApp.getStorageUsed());
          
            MailApp.sendEmail(email, subject, body, {
              htmlBody: body,
        
              name:'class',
              bcc: aid,
              noReply: true,
              attachments:[blobs]    
            });  
}

参考文献:

可安装触发器|Apps脚本

这篇关于根据单元格值发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！