时间戳

首页 > 解决方案 > 有没有办法在循环浏览消息列表时从谷歌表格向多个用户发送多封电子邮件？

google-apps-script - 有没有办法在循环浏览消息列表时从谷歌表格向多个用户发送多封电子邮件？

问题描述

嘿，这是我在 StackOverflow 上的第一个问题。我已经在这段代码上苦苦挣扎了两天，并试图上下移动循环，但什么也没发生。该代码没有显示任何错误，但我没有收到邮件。我要做的是向多个用户（表：Sheet1）发送多条消息（表：buysignals）。似乎每个人都将每封电子邮件作为唯一的接收者。前任 如果 Buysignals 表中有文字，您会收到一封新邮件。这里有人可以帮助我如何解决它吗？

function sendBuySignal() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Buysignals");
const dataRange = sheet.getRange("A2:R17");
const data = dataRange.getValues();

  
  data.forEach(function (rowData) {
    const ticker = rowData[0];
    
    // Stock info
    const kurtosis = rowData[1];
    const sigma = rowData[2];
    const mCap = rowData[17];
    const std = rowData[4];
    const timeStamp = rowData[6];
    const buyPrice = rowData[8];
    const buyIndicator = rowData[16];
    
    
    // Send Signal Mail
    if (buyIndicator > 0) {
      const greeting = 'The stock ' + ticker + ',\n'
      const buyMessage = 'Is at ' + timeStamp + ' in the buy zone at ' + buyPrice + '. With a kurtosis of ' + kurtosis + ', sigma ' + sigma + ', mcap in bill ' + mCap + '.';
      const greatJobMessage = 'Hurry up! Go buy it :)';
      
      const message = [greeting, buyMessage, greatJobMessage].join('\n');
      const subject = 'Test: New Buy ' + ticker;
      
      // Fetch the email address
      const emailRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
      const n=emailRange.getLastRow();
      for (var i = 2; i > n+1 ; i++ ) {
        const emailAddress = emailRange.getRange(i,1).getValue();   
      }
      MailApp.sendEmail(emailAddress, subject, message);
    }
      return

      })

}

标签： google-apps-scriptgoogle-sheetsgmail

解决方案

该问题与循环有关

  1. 语法for (var i = 2; i > n+1 ; i++ ) {不起作用
  • 假设最后一行大于起始行（即大于 2），条件i > n+1将永远不会满足，因此您不会进入循环 - 相反，您应该指定i < n
  • 这n不是n+1因为您从第二行而不是第一行开始 ( i = 2)
  1. MailApp.sendEmail(emailAddress, subject, message);位于for循环之外
  • 这意味着在每个循环中，该函数都会覆盖emailAddress并且在退出循环后，您将发送一封电子邮件 - 到最后保存的电子邮件地址
  • 这个问题可以通过MailApp.sendEmail(emailAddress, subject, message);在for循环内插入来解决

样本：

function sendBuySignal() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Buysignals");
  const dataRange = sheet.getRange("A2:R17");
  const data = dataRange.getValues();
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var n = sheet1.getLastRow();
  const emails = sheet1.getRange("A2:A" + n).getValues();
  
  data.forEach(function (rowData) {
    const ticker = rowData[0];
    
    // Stock info
    const kurtosis = rowData[1];
    const sigma = rowData[2];
    const mCap = rowData[17];
    const std = rowData[4];
    const timeStamp = rowData[6];
    const buyPrice = rowData[8];
    const buyIndicator = rowData[16];
    
    // Send Signal Mail
    if (buyIndicator > 0) {
      Logger.log("buyIndicator > 0");
      const greeting = 'The stock ' + ticker + ',\n'
      const buyMessage = 'Is at ' + timeStamp + ' in the buy zone at ' + buyPrice + '. With a kurtosis of ' + kurtosis + ', sigma ' + sigma + ', mcap in bill ' + mCap + '.';
      const greatJobMessage = 'Hurry up! Go buy it :)';
      
      const message = [greeting, buyMessage, greatJobMessage].join('\n');
      const subject = 'Test: New Buy ' + ticker;
      
      // Fetch the email address
      for (var i = 0; i < emails.length ; i++ ) {
        var emailAddress = emails[i][0];
        Logger.log("email: " + emailAddress);  
        MailApp.sendEmail(emailAddress, subject, message);
      }        
    }       
  })  
}

请注意，对于这个示例，我允许自己在进入循环之前检索所有电子邮件。这比每次循环迭代中的多次调用更有效。getRange()

推荐阅读