Email Response Automation

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++;

    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();

        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 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}`);
  const query = `in:inbox is:unread after:${formattedYesterdayDate}`;
  const threads = GmailApp.search(query);
  return threads;
}

function checkEmails() {
  const threads = getTodaysAndYesterdaysUnseenEmails();

  threads.forEach(thread => {
    const messages = thread.getMessages();
    if (messages.length > 1) return;

    messages.forEach(message => {
      if (!message.isUnread()) return;

      var senderInfo = message.getFrom();
      var emailPattern = /<(.+)>/;
      var emailMatch = senderInfo.match(emailPattern);
      var fromEmail = emailMatch ? emailMatch[1] : senderInfo;

      if (fromEmail !== 'kmpragya409@gmail.com') return;
      if (emailMatch == 'noreply-apps-scripts-notifications@google.com') return;

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

      const result = 'yes';

      if (result === 'yes' || 1) {
        var checkEmailDomain = checkDomain(fromEmail);
        var reply = getHtmlFromFile('reply');

        if (checkEmailDomain == 'brand') {
          reply = getHtmlFromFile('reply_brand');
        }

        Logger.log('Email Type: ' + checkEmailDomain);

        var tempVariables = { "name": name };
        message.reply('', { htmlBody: reply });

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

        sheet.appendRow([
          name, 
          fromEmail, 
          message.getSubject(), 
          emailUrl, 
          thread.getId(), 
          messageId, 
          new Date()
        ]);

        message.markRead();
        message.star();
      }
    });
  });
}

// --------------------------
// Functions to handle templates
// --------------------------

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

function getHtmlFromFile(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function getHtmlFromGoogleDoc(docId) {
  var doc = DocumentApp.openById(docId);
  return convertToHtml(doc.getBody());
}

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);
  for (var key in variables) {
    if (variables.hasOwnProperty(key)) {
      var placeholder = '{{' + key + '}}';
      template = template.replace(new RegExp(placeholder, 'g'), variables[key]);
    }
  }
  return template;
}

// --------------------------
// Functions to handle sheet data
// --------------------------

function getCellValueByIdAndName(spreadsheetId, sheetName, row, column) {
  var spreadsheet = SpreadsheetApp.openById(spreadsheetId);
  var sheet = spreadsheet.getSheetByName(sheetName);
  return sheet.getRange(row, column).getValue();
}

function checkContentForGuestPostOrLinkExchange(content) {
  const apiEndpoint = 'https://api.openai.com/v1/chat/completions';
  const apiKey = 'YOUR_OPENAI_API_KEY_HERE';
  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.\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());
    return jsonResponse.choices[0].message.content.trim().toLowerCase();
  } 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) {
    sheet.appendRow([todayFormatted, 0, 0, 0, 0, 0]);
    rowIndex = sheet.getLastRow() - 1;
  }

  return rowIndex + 1;
}

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

function setDataInRow(rowNumber, sheet, totalIncoming=false, guestPost=false, normal=false, brand=false, spam=false) {
  const row = sheet.getRange(rowNumber, 2, 1, 5);
  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]);
}