ColoringScriptV1-REDOnly.gs

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

// Triggers Tutorial : https://www.youtube.com/watch?v=MiWfaCNRzsA

// Global variables for colors using RGB format
// GET COLOR CODES FROM https://www.rapidtables.com/web/color/RGB_Color.html
var COLOR_PROCESSED = '#84FFA1'; // Green
var COLOR_BOUNCED = '#FF8989';   // Red
var COLOR_BOUNCED_CLIENT_EXISTS = '#FFF884'; // Yellow in RGB

var HEADER_ROW_NUMBER = 3 // the number of the row containing the header of the table
// var CLIENT_EXISTS_COL_NAME = "Clients Already Known" // the name of the column containing information on whether the customer is known or not (column values : "YES" or "NO")

// SET THE GOOGLE SHEET INFO : 
// ID is the last item in the URL, Here's an example : https://docs.google.com/spreadsheets/d/1MV2E_ccGFMeA1QKYhpM-nF7kH0fL9MKMDuU3DF8Eso8
// PAGE_NAME Is the page name inside the google sheet (a single sheet, can have multiple pages)
var GOOGLE_SHEET_ID = "1MV2E_ccGFMeA1QKYhpM-nF7kH0fL9MKMDuU3DF8Eso8" 
var GOOGLE_SHEET_PAGE_NAME = "Feuille 1"

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

function EmailsChecker() {
  // Set a label name for processed emails to avoid duplicate processing
  var processedLabelName = 'Processed-ColoringScriptV1';
  var processedLabel = GmailApp.getUserLabelByName(processedLabelName);
  if (!processedLabel) {
    processedLabel = GmailApp.createLabel(processedLabelName);
  }

  // Set a label name for all emails not in sheet
  var notFoundLabelName = 'NotFound-ColoringScriptV1';
  var notFoundLabel = GmailApp.getUserLabelByName(notFoundLabelName);
  if (!notFoundLabel) {
    notFoundLabel = GmailApp.createLabel(notFoundLabelName);
  }

  // Search for unread emails that contain "Hello" in the body or subject
  let threads = GmailApp.getInboxThreads();
  let sheet = SpreadsheetApp.openById(GOOGLE_SHEET_ID).getSheetByName(GOOGLE_SHEET_PAGE_NAME);

  // Find the index of the "ClientExists" column
  let headerRow = sheet.getRange(HEADER_ROW_NUMBER, 1, 1, sheet.getLastColumn()).getValues()[0];
  // let clientExistsColIndex = headerRow.indexOf(CLIENT_EXISTS_COL_NAME);

  for (var i = 0; i < threads.length; i++) {
    let emailFromThreadFoundInSheet = false;
    let processedAtLeastAMessage = false;
    let thread = threads[i];
    let messages = thread.getMessages();
    // let subject = messages[j].getSubject();
    // let content = messages[j].getPlainBody();
    // let date = messages[j].getDate();

    Logger.log("-> Processing Thread" + i + " " + emailFromThreadFoundInSheet)

    for (var j = 0; j < messages.length; j++) {
      let message = messages[j];
      let emailAddress = message.getFrom().replace(/^.+<([^>]+)>$/, "$1");

      // Skip the email if it has already been processed
      if (thread.getLabels().some(l => l.getName() === processedLabelName) ||
        thread.getLabels().some(l => l.getName() === notFoundLabelName)) {
        continue; // Skip to the next message
      }

      Logger.log("Checking Email from : " + emailAddress);
      processedAtLeastAMessage = true;

      if (emailAddress.includes("mailer-daemon")) {
        let body = message.getBody();
        let bouncedEmail = extractBouncedEmail(body);

        if (bouncedEmail) {
          bouncedEmail = bouncedEmail.toLowerCase()

          // Search Email in Sheet
          let found = findEmailInSheet(sheet, bouncedEmail);
          if (!found) {
            continue; // skip this email, address is not in sheet
          }
          let row = found[0];
          let col = found[1];

          // Check if the "ClientExists" column contains "YES"
          //let clientExistsValue = sheet.getRange(row + 1, clientExistsColIndex + 1).getValue().toString().toUpperCase().trim();

          let rangeToColor = sheet.getRange(row + 1, 1, 1, sheet.getMaxColumns());

          /*
          if (clientExistsValue === "YES") {
            rangeToColor.setBackground(COLOR_BOUNCED_CLIENT_EXISTS);
          }
          else {
            rangeToColor.setBackground(COLOR_BOUNCED);
          }
          */

          rangeToColor.setBackground(COLOR_BOUNCED);

          emailFromThreadFoundInSheet = true
          Logger.log("Bounced email FOUND in sheet: " + bouncedEmail);
        }
      }
      else {
        // Search Email in Sheet
        let found = findEmailInSheet(sheet, emailAddress);
        if (!found) {
          continue; // skip this email, address is not in sheet
        }
        let row = found[0];
        let col = found[1];

        let rangeToColor = sheet.getRange(row + 1, 1, 1, sheet.getMaxColumns());
        // rangeToColor.setBackground(COLOR_PROCESSED);
        emailFromThreadFoundInSheet = true
      }

      // Mark the email as read and move to processed label
      message.markRead();
    }

    Logger.log("-> Finished Processing Thread" + i + " " + emailFromThreadFoundInSheet + " " + processedAtLeastAMessage)
    thread.addLabel(processedLabel);

    if (!emailFromThreadFoundInSheet && processedAtLeastAMessage) {
      thread.addLabel(notFoundLabel);
    }
  }
}

function extractBouncedEmail(body) {
  // Define a regex to match email addresses
  var emailRegex = /[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}/;
  var match = body.match(emailRegex);
  
  if (match) {
    return match[0];
  }
  return null;
}

function findEmailInSheet(sheet, emailAddress) {
    // Find the email address in the Google Sheet and color the row
    let range = sheet.getDataRange();
    let values = range.getValues();

    for (var row = 0; row < values.length; row++) {
      for (var col = 0; col < values[row].length; col++) {
        var cellValue = values[row][col].toString()
        if (cellValue.includes(emailAddress)) {
          return [row, col]
        }
      }
    }
    return null
}