代码之家 › 专栏 › 技术社区 › RomaVictor19

应用程序脚本G表在特定单元格编辑时自动发送电子邮件

google-apps-script google-sheets

RomaVictor19 · 技术社区 · 2 年前

我有四个选项卡,其中包含类似的信息集。当选项卡1中的下拉列表设置为“NEW”时,我希望它能给特定的人发电子邮件。我遇到的问题是,当在同一区域编辑Tab 2时,它会发送相同的电子邮件。我想做的是,当只在特定选项卡中进行编辑时,让每个选项卡都向不同的人发送电子邮件。
示例:选项卡1已编辑>电子邮件发送给人员1;选项卡2已编辑>电子邮件发送给人员2 现在,当编辑选项卡1或选项卡2时,它会向同一个人发送电子邮件。我确信我需要一个新的选项卡2、选项卡3等的脚本,但我想知道如何只让选项卡1的编辑发送电子邮件。

以下是我正在使用的脚本:

function onEdit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 

  // EVENT VARIABLES 
  let range = e.range; 
  let row = e.range.getRow(); 
  let col = e.range.getColumn(); 
  let cellValue = sheet.getActiveCell().getValue(); 

  let projectName = sheet.getRange(row,1).getValue(); 
  let user = Session.getActiveUser().getEmail(); 
  let cellLocation = sheet.getActiveCell().getA1Notation(); 
  let url = "https://docs.google.com/spreadsheets/d/1bAJ0Asma4lX1tW3HYezW_Al6llkpaIKWSLptfjV-pAI/edit#gid=712318935"

  if ( col == 1 && cellValue == "NEW") {
    // Browser.msgBox('It works'); 
    MailApp.sendEmail(
      '[email protected]',
      'Staff Overview Update',
      "There has been an update to the Staff Overview sheet requiring your attention: " + url + '&range=' + cellLocation,
      {name: 'Team Leader'}
    ); 
  }; 

}

1 回复 | 直到 2 年前

player0 2 年前

尝试

function onEdit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  let sheetName = sheet.getName(); // Get the name of the active sheet

  // EVENT VARIABLES 
  let range = e.range; 
  let row = e.range.getRow(); 
  let col = e.range.getColumn(); 
  let cellValue = sheet.getActiveCell().getValue(); 

  let projectName = sheet.getRange(row,1).getValue(); 
  let user = Session.getActiveUser().getEmail(); 
  let cellLocation = sheet.getActiveCell().getA1Notation(); 
  let url = "https://docs.google.com/spreadsheets/d/1bAJ0Asma4lX1tW3HYezW_Al6llkpaIKWSLptfjV-pAI/edit#gid=712318935";

  if (col == 1 && cellValue == "NEW") {
    let recipientEmail;
    
    // Determine the recipient based on the sheet name
    if (sheetName === "Tab 1") {
      recipientEmail = '[email protected]';
    } else if (sheetName === "Tab 2") {
      recipientEmail = '[email protected]';
    } else if (sheetName === "Tab 3") {
      recipientEmail = '[email protected]';
    } else if (sheetName === "Tab 4") {
      recipientEmail = '[email protected]';
    }
    
    if (recipientEmail) {
      MailApp.sendEmail(
        recipientEmail,
        'Staff Overview Update',
        "There has been an update to the " + sheetName + " sheet requiring your attention: " + url + '&range=' + cellLocation,
        {name: 'Team Leader'}
      ); 
    }
  }
}

更新

function onEdit(e) {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  let sheetName = sheet.getName(); // Get the name of the active sheet
  let sheetId = sheet.getSheetId(); // Get the unique ID of the active sheet

  // EVENT VARIABLES 
  let range = e.range; 
  let row = e.range.getRow(); 
  let col = e.range.getColumn(); 
  let cellValue = sheet.getActiveCell().getValue(); 

  let projectName = sheet.getRange(row,1).getValue(); 
  let user = Session.getActiveUser().getEmail(); 
  let cellLocation = sheet.getActiveCell().getA1Notation(); 
  let baseUrl = "https://docs.google.com/spreadsheets/d/1bAJ0Asma4lX1tW3HYezW_Al6llkpaIKWSLptfjV-pAI/edit";

  if (col == 1 && cellValue == "NEW") {
    let recipientEmail;
    
    // Determine the recipient based on the sheet name
    if (sheetName === "Tab 1") {
      recipientEmail = '[email protected]';
    } else if (sheetName === "Tab 2") {
      recipientEmail = '[email protected]';
    } else if (sheetName === "Tab 3") {
      recipientEmail = '[email protected]';
    } else if (sheetName === "Tab 4") {
      recipientEmail = '[email protected]';
    }
    
    if (recipientEmail) {
      let url = `${baseUrl}#gid=${sheetId}&range=${cellLocation}`;
      MailApp.sendEmail(
        recipientEmail,
        'Staff Overview Update',
        `There has been an update to the ${sheetName} sheet requiring your attention: ${url}`,
        {name: 'Team Leader'}
      ); 
    }
  }
}