Email Response Automation

Copy Code

var sheet = SpreadsheetApp.openById('1fDhyR8yxtoI36cym__bIgPjNo9zjb_uFvYeaMHQRM').getSheetByName('Customers Information');

function moveSpamToInboxAndGetBody() {
  const query = `in:spam is:unread`;
  const threads = GmailApp.search(query);

  var movedThreads = 0;
  var bodiesRetrieved = 0;

  for (var i = 0; i < threads.length; i++) {
      var thread = threads[i];
      movedThreads++;
      // Check if the thread has only one message
      if (thread.getMessageCount() < 3) {
          var message = thread.getMessages()[0];
          var body = message.getPlainBody();
          Logger.log("Subject: " + message.getSubject());
          const content = 'Subject: ' + message.getSubject() + ' <br>Email Body: ' + body;
          const result = checkContentForGuestPostOrLinkExchange(content);
          if (result === 'yes' || 1) {
            Logger.log("Guest Post Mail Found: "+result);
              thread.moveToInbox(); // Move thread to inbox
              const sheet1 = SpreadsheetApp.openById('1fDhyR8yxtoIcymBd__bIgPjNo9zjb_uFvYeaMHQRM').getSheetByName('Report');
              const selectedRow = checkAndFillTodayDate(sheet1);
              const dataOfRow = getDataFromRow(selectedRow, sheet1);
              const updateWith = dataOfRow[4] + 1;
              setDataInRow(selectedRow, sheet1, false, false, false, updateWith);
          }
          else{
            message.markRead();
          }
          bodiesRetrieved++;
      }
  }

  Logger.log(movedThreads + " spam threads moved to inbox.");
  Logger.log(bodiesRetrieved + " email bodies retrieved.");
}

function checkDomain(fromEmail) {
    const normalDomains = [
        '@gmail.com', '@outlook.com', '@yahoo.com', '@yahoo.in',
        '@ymail.com', '@live.com', '@hotmail.com'
    ];
    const regex = new RegExp(normalDomains.join('|').replace(/\./g, '\\.'));
    return regex.test(fromEmail) ? 'normal' : 'brand';
}

function getTodaysUnseenEmails() {
  const today = new Date();
  const year = today.getFullYear();
  const month = ('0' + (today.getMonth() + 1)).slice(-2);
  const day = ('0' + today.getDate()).slice(-2);
  const formattedDate = `${year}/${month}/${day}`;
  Logger.log(formattedDate);
  const query = `in:inbox is:unread after:${formattedDate}`;
  const threads = GmailApp.search(query);
  return threads;
}

function getTodaysAndYesterdaysUnseenEmails() {
  const today = new Date();
  const year = today.getFullYear();
  const month = ('0' + (today.getMonth() + 1)).slice(-2);
  const day = ('0' + today.getDate()).slice(-2);
  // const formattedTodayDate = `${year}/${month}/${day}`;
  const formattedTodayDate = 'after:2024/7/29 before:2024/8/13';

  const yesterday = new Date(today);
  yesterday.setDate(today.getDate() - 1);
  const yesterdayYear = yesterday.getFullYear();
  const yesterdayMonth = ('0' + (yesterday.getMonth() + 1)).slice(-2);
  const yesterdayDay = ('0' + yesterday.getDate()).slice(-2);
  const formattedYesterdayDate = `${yesterdayYear}/${yesterdayMonth}/${yesterdayDay}`;

  Logger.log(`Yesterday: ${formattedYesterdayDate}, Today: ${formattedTodayDate}`);
 
  // Adjust the query to include emails after the start of yesterday
  const query = `in:inbox is:unread after:${formattedYesterdayDate}`;
  const threads = GmailApp.search(query);
  return threads;
}

function checkEmails() {
    // moveSpamToInboxAndGetBody();
    const threads = getTodaysAndYesterdaysUnseenEmails();
    threads.forEach(thread => {
        const messages = thread.getMessages();

        if (messages.length > 1) {
            return;
        }
        messages.forEach(message => {
            if (!message.isUnread()) return;
            var recipientInfo = message.getTo();
            var emailPattern = /<(.+)>/;
            var emailMatch = recipientInfo.match(emailPattern);
            var email = emailMatch ? emailMatch[1] : recipientInfo;

            // if (email !== 'sales@outrightcrm.com' || email !== 'support@outrightcrm.com')  return;
            var senderInfo = message.getFrom();
            var emailPattern = /<(.+)>/;
            var emailMatch = senderInfo.match(emailPattern);
            var email = emailMatch ? emailMatch[1] : senderInfo;
            var fromEmail1 = email;
            if(fromEmail1 !== 'kmpragya409@gmail.com'){
              // return;
            }
            if (email == 'noreply-apps-scripts-notifications@google.com') return;

            Logger.log(email);
            var namePattern = /^(.+?)\s*</;
            var nameMatch = senderInfo.match(namePattern);
            var name = nameMatch ? nameMatch[1] : '';

            // const content = 'Subject: ' + message.getSubject() + ' <br>Email Body: ' + message.getPlainBody();
            // const result = checkContentForGuestPostOrLinkExchange(content);
const result = 'yes';

            if (result === 'yes' || 1) {
            var checkEmailDomain = checkDomain(fromEmail1);
                            var reply = getHtmlFromFile('reply');
            if(checkEmailDomain == 'brand'){
                       //     templateDocId = '1aJ57NN2GU_k9j5AfhsC8-Ch4Odqt1KVPEyWJzIW6OxY';
                // reply = replaceTemplatePlaceholders(tempVaribales, templateDocId);

               reply = getHtmlFromFile('reply_brand');
              //  return;

            }
                          Logger.log('Email Type: '+checkEmailDomain);
                var tempVaribales = {
                    "name": name
                };

                message.reply('', {
                    htmlBody: reply
                });
                var messageId = message.getId();
                var emailUrl = 'https://mail.google.com/mail/u/0/#inbox/' + messageId;

                sheet.appendRow([
                    name,
                    email,
                    message.getSubject(),
                    emailUrl,
                    thread.getId(),
                    messageId,
                    new Date(),
                    //reply,
                ]);
                message.markRead();
                message.star();
              Logger.log("aaaaaaaaaaa");
            }
        });
    });
}

function getTemplateFromGoogleDoc(docId) {
    var doc = DocumentApp.openById(docId);
    var body = doc.getBody();
    var templateContent = body.getText();
    return templateContent;
}

function getHtmlFromFile(filename) {
    // Load HTML content from a file within the same project
    return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
function getHtmlFromGoogleDoc(docId) {
    var doc = DocumentApp.openById(docId);
    var body = doc.getBody();
    var htmlOutput = convertToHtml(body);
    return htmlOutput;
}

function convertToHtml(body) {
    var output = "";
    var numChildren = body.getNumChildren();
    for (var i = 0; i < numChildren; i++) {
        var child = body.getChild(i);
        output += convertElementToHtml(child);
    }
    return output;
}

function convertElementToHtml(element) {
    var output = "";
    var type = element.getType();

    switch (type) {
        case DocumentApp.ElementType.PARAGRAPH:`
            output += "<p>" + element.getText() + "</p>";
            break;
        case DocumentApp.ElementType.TABLE:
            output += "<table border='1'>";
            var numRows = element.getNumRows();
            for (var i = 0; i < numRows; i++) {
                output += "<tr>";
                var row = element.getRow(i);
                var numCells = row.getNumCells();
                for (var j = 0; j < numCells; j++) {
                    var cell = row.getCell(j);
                    output += "<td>" + cell.getText() + "</td>";
                }
                output += "</tr>";
            }
            output += "</table>";
            break;
        case DocumentApp.ElementType.LIST_ITEM:
            output += "<li>" + element.getText() + "</li>";
            break;
        default:
            output += element.getText();
            break;
    }

    return output;
}
function replaceTemplatePlaceholders(variables, docId) {
    var template = getHtmlFromGoogleDoc(docId);
    var result = template;
    for (var key in variables) {
        if (variables.hasOwnProperty(key)) {
            var placeholder = '{{' + key + '}}';
            var value = variables[key];
            result = result.replace(new RegExp(placeholder, 'g'), value);
        }
    }
    return result;
}
function getCellValueByIdAndName(spreadsheetId, sheetName, row, column) {
    var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    var sheet = spreadsheet.getSheetByName(sheetName);
    var cellValue = sheet.getRange(row, column).getValue();
    return cellValue;
}


function checkContentForGuestPostOrLinkExchange(content) {
    const apiEndpoint = 'https://api.openai.com/v1/chat/completions';
    const apiKey = 'sk-1qARGM8P_3HRNLQ47j1MCKBWiEG7bRKoG8ZpDW1PUgWqz8T38sdT3BlbkFJWcy0e4BFahyvd8OqaqxstlZpaIrMP6Q5HUpOGluajdYtQv6_qA8hxEcA';


    const prompt = `Analyze the following content and determine if it's related to guest posting or link exchange. Respond with only "yes" if it is related, or "no" if it is not related.\n\nContent: ${content}`;


    const requestBody = {
        model: "gpt-4",
        messages: [
            { role: "system", content: "You are a helpful assistant that analyzes content." },
            { role: "user", content: prompt }
        ]
    };


    const options = {
        method: 'post',
        headers: {
            'Authorization': `Bearer ${apiKey}`,
            'Content-Type': 'application/json'
        },
        payload: JSON.stringify(requestBody)
    };


    try {
        const response = UrlFetchApp.fetch(apiEndpoint, options);
        const jsonResponse = JSON.parse(response.getContentText());
        const result = jsonResponse.choices[0].message.content.trim().toLowerCase();


        return result;
    } catch (error) {
        console.error('Error calling ChatGPT API:', error);
        return null;
    }
}


function checkAndFillTodayDate(sheet) {
  const today = new Date();
  const todayFormatted = Utilities.formatDate(today, Session.getScriptTimeZone(), 'MM/dd/yyyy');
 
  const dateColumn = 1;
  const dataRange = sheet.getDataRange();


  const data = dataRange.getValues();
 
  let rowIndex = data.findIndex(row => {
    const rowDate = Utilities.formatDate(new Date(row[dateColumn - 1]), Session.getScriptTimeZone(), 'MM/dd/yyyy');
    return rowDate === todayFormatted;
  });


  if (rowIndex === -1) {
    // Today's date not found, append a new row
    sheet.appendRow([todayFormatted, 0, 0, 0, 0, 0]);
    rowIndex = sheet.getLastRow() - 1; // Zero-based index
  }


  return rowIndex + 1; // Convert zero-based index to one-based row number
}


function getDataFromRow(rowNumber, sheet) {  
  const row = sheet.getRange(rowNumber, 1, 1, sheet.getLastColumn());
  const rowData = row.getValues()[0];
 
  return rowData;
}


function setDataInRow(rowNumber, sheet, totalIncoming=false, guestPost=false, normal=false, brand=false, spam=false) {
  const row = sheet.getRange(rowNumber, 2, 1, 5); // Columns B to F (2 to 6)
  const rowData = row.getValues()[0];
 
  if (totalIncoming !== false) rowData[0] = totalIncoming;
  if (guestPost !== false) rowData[1] = guestPost;
  if (normal !== false) rowData[2] = normal;
  if (brand !== false) rowData[3] = brand;
  if (spam !== false) rowData[4] = spam;
 
  row.setValues([rowData]);
}


function countTodaysEmails() {
  var today = new Date();
  var formattedDate = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy/MM/dd');
  var query = 'after:' + formattedDate;
  var threads = GmailApp.search(query);
  var emailCount = 0;
 
  for (var i = 0; i < threads.length; i++) {
    var messages = threads[i].getMessages();
    for (var j = 0; j < messages.length; j++) {
      var message = messages[j];
      var messageDate = Utilities.formatDate(message.getDate(), Session.getScriptTimeZone(), 'yyyy/MM/dd');
      if (messageDate === formattedDate) {
        emailCount++;
      }
    }
  }
 
  return emailCount;
}


function getAllDataFromSheet(sheet) {
  if (!sheet) {
    Logger.log('Sheet not found: ' + sheetName);
    return;
  }
  var data = sheet.getDataRange().getValues();
  // for (var i = 0; i < data.length; i++) {
  //   Logger.log(data[i]);
  // }
  return data;
}


function testFun(){
  var sheetData = getAllDataFromSheet(sheet);
  var finalArray = [];
  for(var i = 0; i < sheetData.length; i++){
      var checkDate = compareWithCurrentDate(sheetData[i][6]);
      if(checkDate==true){
        finalArray[i] = sheetData[i];
      }
  }
}


function compareWithCurrentDate(givenDateString) {
  var currentDate = new Date();
   var givenDate = new Date(Date.parse(givenDateString));
         Logger.log('currentDate: '+currentDate);
               Logger.log('givenDate: '+givenDate);
  if (givenDate.getTime() === currentDate.getTime()) {
    return true;
  }
  return false;
}


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


function updateReportWithEmailCount() {
    var sheetId = '1fDhyR8yxtoI36cymBd__bIgPjNo9zjb_uFvYeaMHQRM';
    var sheetName = 'Customers Information';
    var reportSheetName = 'Report';
    var webhookUrl = 'https://chat.googleapis.com/v1/spaces/AAAAlahKTKo/messages?key=AIzaSyDdI0hCZtE6vySjMm-WEfRCPzqKqqsHI&token=ycvxT37ydVp9tOf436NRN722oK9ZQFuRYYOxyfZJsno'; // Replace with your Google Chat webhook URL


    // Open the spreadsheet and get sheets
    var spreadsheet = SpreadsheetApp.openById(sheetId);
    var sheet = spreadsheet.getSheetByName(sheetName);
    var reportSheet = spreadsheet.getSheetByName(reportSheetName);


    if (!sheet || !reportSheet) {
        Logger.log('Error: One or both sheets not found');
        return;
    }


    // Check if headers are already present, otherwise add them
    var headers = ['Date', 'Total Non Brand Guest Post', 'Total Brand Guest Post', 'Total Automated Reply', 'Total Spam Emails'];
    var firstRow = reportSheet.getRange(1, 1, 1, headers.length).getValues()[0];
    if (firstRow.join() !== headers.join()) {
        // Set headers if they are not present
        reportSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    }


    // Count the number of entries with today's date in the 'Customers Information' sheet
    var dataRange = sheet.getDataRange();
    var data = dataRange.getValues();
    var today = new Date();
    var todayFormatted = Utilities.formatDate(today, Session.getScriptTimeZone(), 'MM/dd/yyyy');
    var count = 0;
    var brandEmailCount = 0;


    Logger.log('Today\'s Date: ' + todayFormatted);


    // Iterate through the rows to count entries with today's date
    for (var i = 0; i < data.length; i++) { var rowDate = data[i][6]; // Assuming the date is in the 7th column (index 6) var email = data[i][1]; // Assuming the email is in the 2nd column (index 1) if (rowDate) { var rowDateFormatted = Utilities.formatDate(new Date(rowDate), Session.getScriptTimeZone(), 'MM/dd/yyyy'); if (rowDateFormatted === todayFormatted) { count++; // Check if the email domain is a brand email if (isBrandEmail(email)) { brandEmailCount++; } } } } // Get email counts var emailCounts = countEmailsToday(); var nonBrandEmailCount = count - brandEmailCount; // Total Non Brand Guest Post var automatedReplyCount = nonBrandEmailCount + brandEmailCount; // Total Automated Reply Logger.log('Count of entries with today\'s date: ' + count); Logger.log('Count of brand emails with today\'s date: ' + brandEmailCount); Logger.log('Count of non-brand emails with today\'s date: ' + nonBrandEmailCount); Logger.log('Total Automated Reply: ' + automatedReplyCount); // Insert or update the data var newRow = [todayFormatted, nonBrandEmailCount, brandEmailCount, automatedReplyCount, emailCounts.spamEmails.length]; insertOrUpdateRow(reportSheet, newRow); // Send message to Google Chat if new entry is added or updated sendMessageToChat(webhookUrl, todayFormatted, nonBrandEmailCount, brandEmailCount, automatedReplyCount, emailCounts.allEmails.length, emailCounts.spamEmails.length, sheetId); } function isBrandEmail(email) { var commonDomains = ['@gmail.com', '@outlook.com', '@yahoo.com', '@yahoo.in', '@ymail.com', '@live.com', '@hotmail.com']; return !commonDomains.some(domain => email.includes(domain));
}


function insertOrUpdateRow(sheet, rowData) {
    var dataRange = sheet.getDataRange();
    var data = dataRange.getValues();


    // Check if the date already exists
    for (var i = 1; i < data.length; i++) { if (data[i][0] === rowData[0]) { // Date exists, update the row sheet.getRange(i + 1, 1, 1, rowData.length).setValues([rowData]); return; } } // Date does not exist, insert a new row at the top sheet.insertRowBefore(2); // Insert a new row below the header sheet.getRange(2, 1, 1, rowData.length).setValues([rowData]); // Insert the new data in the second row } function countEmailsToday() { // Set the start time to midnight today var startDate = new Date(); startDate.setHours(0, 0, 0, 0); // Set the end time to now var endDate = new Date(); var query = 'after:' + formatDate(startDate); var threads = GmailApp.search(query); var spamThreads = GmailApp.getSpamThreads(); var allEmails = []; var brandEmails = []; var spamEmails = []; var commonDomains = ['@gmail.com', '@outlook.com', '@yahoo.com', '@yahoo.in', '@ymail.com', '@live.com', '@hotmail.com']; // Process today's threads threads.forEach(function(thread) { var messages = thread.getMessages(); messages.forEach(function(message) { var from = message.getFrom(); allEmails.push(from); if (!commonDomains.some(domain => from.includes(domain))) {
                brandEmails.push(from);
            }
        });
    });


    // Process spam threads separately
    spamThreads.forEach(function(thread) {
        var messages = thread.getMessages();
        messages.forEach(function(message) {
            var from = message.getFrom();
            var messageDate = message.getDate();
            if (messageDate >= startDate && messageDate <= endDate) {
                spamEmails.push(from);
            }
        });
    });


    return { allEmails: allEmails, brandEmails: brandEmails, spamEmails: spamEmails };
}


function formatDate(date) {
    var year = date.getFullYear();
    var month = (date.getMonth() + 1).toString().padStart(2, '0');
    var day = date.getDate().toString().padStart(2, '0');
    return year + '/' + month + '/' + day;
}


function sendMessageToChat(webhookUrl, date, nonBrandCount, brandCount, automatedReplyCount, allCount, spamCount, sheetId) {
    var sheetLink = `https://docs.google.com/spreadsheets/d/${sheetId}`;
    var payload = JSON.stringify({
        text: `The Guest Post Automation sheet has been updated ${date}.
        \n * Total Non Brand Guest Post: ${nonBrandCount}
        \n * Total Brand Guest Post: ${brandCount}  
        \n * Total Automated Reply: ${automatedReplyCount}
        \n * Total Spam Emails: ${spamCount}
        \n * Total Incoming Emails: ${allCount}\n\n`
    });


    var options = {
        method: 'post',
        contentType: 'application/json',
        payload: payload
    };


    UrlFetchApp.fetch(webhookUrl, options);
}