Email Response Automation
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++;
// 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);
}