ارسال ایمیل در صورت تغییر در Google Sheet

function onOpen(e) {
   installTrigger();
};

function listTriggers() {
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => {
    Logger.log(`Trigger ID: ${trigger.getUniqueId()}, Handler: ${trigger.getHandlerFunction()}`);
  });
}

function deleteTriggers() {
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => {
    ScriptApp.deleteTrigger(trigger);
  });
}

// Function to install the onEdit trigger
function installTrigger() {
  const triggers = ScriptApp.getProjectTriggers();
  
  // Check if the trigger already exists
  const existingTrigger = triggers.some(trigger => 
    trigger.getHandlerFunction() === 'sendEmailOnEdit'
  );
  
  if (!existingTrigger) {
    ScriptApp.newTrigger('sendEmailOnEdit')
             .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
             .onEdit()
             .create();
  }
}

// Global variable to store processed changes
let processedChanges = [];

// Function to send email on edit
function sendEmailOnEdit(e) {
  // Get the active spreadsheet and sheet
  const sheet = e.source.getActiveSheet();
  const range = e.range;
  const row = range.getRow();
  const column = range.getColumn();
  const oldValue = e.oldValue;
  const newValue = e.value;
  const changeType = getChangeType(e);
  const headerRange = sheet.getRange(1,1,1,sheet.getLastColumn()).getValues()[0];
  const header = headerRange[column-1];

  if (header !== 'توضیح' || newValue === oldValue) {
    return;
  }

  // Check if the change has already been processed
  const changeId = `${sheet.getName()}_${row}_${column}_${newValue}`;
  const cache = CacheService.getScriptCache();
  const cachedChange = cache.get(changeId);
  
  if (cachedChange) {
    return;
  }

  // Get all user emails with access to the spreadsheet
  const userEmails = getAllUserEmails();

  // Create the email subject and body
  const subject = 'Life';

    const body = `
    <div style="direction: rtl; text-align: right;font-family: Tahoma;">
      <p>نوع تغییر: ${changeType}</p>
      <p>نام شیت: ${sheet.getName()}</p>
      <p>ردیف: ${row}</p>
      <p>مقدار: ${newValue}</p>
    </div>
  `;             

  //Send the email to all users
  userEmails.forEach(email => {
    MailApp.sendEmail({
      to: email,
      subject: subject,
      htmlBody: body
    });
 });

  // Cache the changeId to avoid sending duplicate emails
  cache.put(changeId, 'processed', 3600); // Cache it for 1 hour
}

// Function to determine the type of change
function getChangeType(e) {
  const range = e.range;
  const sheet = e.source.getActiveSheet();
  const lastRow = sheet.getLastRow();
  const lastColumn = sheet.getLastColumn();

  if (range.getRow() > lastRow - 1 && range.getLastRow() == lastRow) {
    return 'جدید';
  } else if (e.oldValue === undefined) {
    return 'حذف';
  } else {
    return 'ویرایش';
  }
}

// Function to get all user emails with access to the spreadsheet
function getAllUserEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const editors = sheet.getEditors();
  
  const emails = new Set();

  editors.forEach(editor => emails.add(editor.getEmail()));

  return Array.from(emails);
}