Category: work life

IMPORTANT NOTE: This script will analyze the email, determine which reply from a set of templates is most appropriate, then customize that reply as needed for that particular customer/inquiry, adding in the customer’s name and adding your desired email signature. Then it will forward that generated reply back to you at the same address, for human review (as in-line text). You still need to then send the generated reply, it will NOT do the sending for you.

So we’ve refined the previous script a bit to add a JSON file full of reply templates. Now there’s a JSON added to the template literal prompt for {templateOptions}, which is then passed to the LLM.

The context size is increasing but the tradeoff is that we are getting increased functionality from our auto-responder (only intended for nuisance, non-sensitive emails or in line with your company security policy):

// Global variable to cache the loaded reply templates to avoid re-reading the file on every function call.
var cachedReplyTemplates = null;

/**
 * Main function to process forwarded emails.
 */
function processForwardedEmails() {
  try {
    console.log("Starting email processing...");

    var allThreads = [];
    var start = 0;
    var maxBatch = 100; // Process 100 threads at a time

    // Search for UNREAD emails that don't have "AUTOMATED REPLY" in the subject
    var searchQuery = 'is:unread -subject:"AUTOMATED REPLY"';

    do {
      var threads = GmailApp.search(searchQuery, start, maxBatch);
      allThreads = allThreads.concat(threads);
      start += threads.length;
      console.log("Found " + threads.length + " unread forwarded threads in batch, total " + allThreads.length);
    } while (threads.length === maxBatch);

    console.log("Total threads to process: " + allThreads.length);

    for (var i = 0; i < allThreads.length; i++) {
      var thread = allThreads[i];
      var messages = thread.getMessages();
      var latestMessage = messages[messages.length - 1];

      console.log("Processing email " + (i + 1) + ": " + thread.getFirstMessageSubject());
      console.log("From: " + latestMessage.getFrom());
      console.log("Is unread: " + thread.isUnread());

      // Extract original subject from the forwarded email content
      var originalSubject = extractOriginalSubject(latestMessage.getPlainBody(), thread.getFirstMessageSubject());
      console.log("Original subject: " + originalSubject);

      // Generate reply using Gemini
      console.log("Generating reply...");
      var reply = generateReply(latestMessage.getPlainBody());
      console.log("Reply generated, length: " + reply.length);

      // Send the generated reply back to you for review
      console.log("Sending reply to self...");
      sendReplyToSelf(originalSubject, latestMessage.getPlainBody(), reply);
      console.log("Reply sent!");

      // Mark as read, but DO NOT archive the email
      thread.markRead(); // First, mark as read
      console.log("Email marked as read.");
    }

    console.log("Processing complete!");
  } catch (error) {
    console.log("Error: " + error.toString());
    console.log("Stack trace: " + error.stack);
  }
}

/**
 * Extracts the original subject from a forwarded email's body.
 * @param {string} emailBody The plain text body of the email.
 * @param {string} forwardedSubject The subject of the forwarded email from Gmail.
 * @returns {string} The extracted original subject.
 */
function extractOriginalSubject(emailBody, forwardedSubject) {
  // Try to find "Subject:" in the forwarded email body
  var subjectMatch = emailBody.match(/Subject:\s*(.+)/i);

  if (subjectMatch && subjectMatch[1]) {
    return subjectMatch[1].trim();
  }

  // Fallback: remove "Fw:" or "Fwd:" from the Gmail subject
  var cleanedSubject = forwardedSubject.replace(/^(Fw:|Fwd:)\s*/i, '').trim();
  return cleanedSubject;
}

/**
 * Loads reply templates from the 'replies.json.html' file.
 * Caches the templates after the first load for efficiency.
 * @returns {object} An object containing the reply templates.
 */
function loadReplyTemplates() {
  if (cachedReplyTemplates === null) {
    try {
      var jsonString = HtmlService.createTemplateFromFile('replies.json').evaluate().getContent();
      cachedReplyTemplates = JSON.parse(jsonString);
      console.log("Reply templates loaded from replies.json.html");
    } catch (e) {
      console.error("Error loading or parsing replies.json.html: " + e.message);
      throw new Error("Failed to load reply templates. Please ensure 'replies.json.html' exists and contains valid JSON.");
    }
  }
  return cachedReplyTemplates;
}

/**
 * Generates a reply using the Gemini API, selecting from a set of pre-defined templates.
 * @param {string} emailBody The plain text body of the email to respond to.
 * @returns {string} The customized reply generated by Gemini.
 */
function generateReply(emailBody) {
  try {
    // IMPORTANT: Ensure you have your GEMINI_API_KEY set in Script Properties (File > Project properties > Script properties)
    var apiKey = PropertiesService.getScriptProperties().getProperty('GEMINI_API_KEY');

    // Ensure API Key is available
    if (!apiKey) {
      throw new Error("GEMINI_API_KEY is not set in Script Properties.");
    }

    // Load all available reply templates
    var templates = loadReplyTemplates();

    // Convert templates object to a string for the prompt
    // This provides Gemini with the full list of options and their content
    var templateOptions = JSON.stringify(templates, null, 2); // Pretty print for readability for AI

    var prompt = `You are an email assistant for TechCorp Solutions. Your task is to select the most appropriate pre-written reply template based on the content of the email and then customize it.

Here are the available reply templates, identified by their keys:
${templateOptions}

Instructions:
1.  **Analyze the "EMAIL TO RESPOND TO" carefully.**
2.  **Determine which of the provided templates is the best fit for the email's intent.**
3.  **Extract the sender's first name, product/service name (if mentioned, otherwise "not specified"), and company name (if mentioned, otherwise "not specified") from the email.**
4.  **Populate the chosen template's placeholders ({customerName}, {productName}, {companyName}, etc.) with the extracted information.** If a placeholder's information is "not specified", omit that part of the sentence or use a polite general phrase where applicable.
5.  **If no template is a good fit, respond with "noMatch".** In this case, provide a polite generic response that asks for more information.
6.  If the email is clearly not about the specific topic of the chosen template, politely redirect them to use the standard template but mention their specific topic.

EMAIL TO RESPOND TO:
${emailBody}

YOUR RESPONSE FORMAT:
Chosen Template Key: [Key of chosen template, or 'noMatch']
Customized Reply: [The full customized reply based on the chosen template and extracted info, or the generic response if 'noMatch']

Example of expected output if a template is chosen:
Chosen Template Key: pricingRequest
Customized Reply: Hello John, Thank you for your interest in our cloud hosting services. Our pricing starts at $99/month for the basic plan and $299/month for premium features. I'll send you a detailed quote within the next business day. You can also view our pricing at www.techcorp.com/pricing. Best regards, Sales Team

Example of expected output if customer name is not specified:
Chosen Template Key: generalInquiry
Customized Reply: Hi there, Thank you for reaching out to TechCorp Solutions. We have received your inquiry and will respond within 24 hours. For urgent matters, please call our support line at 555-0123. Best regards, Customer Service Team

Example of expected output if 'noMatch' is chosen:
Chosen Template Key: noMatch
Customized Reply: Dear Sender, thank you for your email. To assist you further, please provide more details about your inquiry.

---
Chosen Template Key:`; // Start the prompt to guide Gemini's output format

    var payload = {
      'contents': [{
        'parts': [{
          'text': prompt
        }]
      }]
    };

    var options = {
      'method': 'POST',
      'headers': {
        'Content-Type': 'application/json',
      },
      'payload': JSON.stringify(payload)
    };

    var response = UrlFetchApp.fetch(
      `https://generativelanguage.googleapis.com/v1/models/gemini-1.5-flash:generateContent?key=${apiKey}`,
      options
    );

    var data = JSON.parse(response.getContentText());

    if (data.candidates && data.candidates[0] && data.candidates[0].content) {
      var geminiOutput = data.candidates[0].content.parts[0].text;

      // Parse Gemini's structured output
      var lines = geminiOutput.split('\n');
      var chosenKey = '';
      var customizedReply = '';

      for (var i = 0; i < lines.length; i++) {
        if (lines[i].startsWith('Chosen Template Key:')) {
          chosenKey = lines[i].replace('Chosen Template Key:', '').trim();
        } else if (lines[i].startsWith('Customized Reply:')) {
          customizedReply = lines[i].replace('Customized Reply:', '').trim();
          // Concatenate remaining lines if the reply spans multiple lines
          for (var j = i + 1; j < lines.length; j++) {
            customizedReply += '\n' + lines[j].trim();
          }
          break; // Stop after finding the reply
        }
      }

      if (chosenKey === 'noMatch') {
        console.log("Gemini determined no suitable template. Generic reply generated.");
        return customizedReply; // Return the generic response
      } else if (templates[chosenKey]) {
        console.log("Gemini chose template: " + chosenKey);
        // Gemini is now responsible for populating the variables within the 'Customized Reply'
        return customizedReply;
      } else {
        console.warn("Gemini suggested an unknown template key: " + chosenKey + ". Returning generic error reply.");
        return "Error: Gemini suggested an invalid template or failed to choose. Please check manually.";
      }

    } else {
      // Log more details if the Gemini API response is not as expected
      console.log("Unexpected Gemini API response structure: " + JSON.stringify(data));
      return "Error generating reply - please check manually";
    }

  } catch (error) {
    console.log("Gemini API error: " + error.toString());
    return "Error generating reply: " + error.toString();
  }
}

/**
 * Sends the generated reply back to your specified email address for review.
 * @param {string} originalSubject The subject of the original forwarded email.
 * @param {string} originalBody The plain text body of the original forwarded email.
 * @param {string} generatedReply The reply generated by Gemini.
 */
function sendReplyToSelf(originalSubject, originalBody, generatedReply) {
  var subject = "AUTOMATED REPLY: " + originalSubject;
  var body = "Generated Reply:\n" + generatedReply + "\n\n--- Original Email ---\n" + originalBody;

  // Send to your review email address
  GmailApp.sendEmail("admin@techcorp.com", subject, body);
}

And the JSON file has camelcase keys representing a bunch of customer scenarios, with the potential responses as values.

{
  "generalInquiry": "Hi {customerName}, Thank you for reaching out to TechCorp Solutions. We have received your inquiry and will respond within 24 hours. For urgent matters, please call our support line at 555-0123. Best regards, Customer Service Team",
  
  "pricingRequest": "Hello {customerName}, Thank you for your interest in our services. Our pricing starts at $99/month for the basic plan and $299/month for premium features. I'll send you a detailed quote within the next business day. You can also view our pricing at www.techcorp.com/pricing. Best regards, Sales Team",
  
  "supportTicket": "Hi {customerName}, We've received your support request (Ticket #{ticketNumber}) and our technical team is reviewing it. Expected response time is 4-6 hours during business days. You can track your ticket status at www.techcorp.com/support. Thanks, Technical Support",
  
  "partnershipProposal": "Dear {partnerName}, Thank you for your partnership proposal. We appreciate your interest in collaborating with TechCorp. Our partnerships team will review your submission and respond within 5-7 business days. Regards, Business Development Team",
  
  "jobApplication": "Hello {applicantName}, Thank you for applying for the {positionTitle} role at TechCorp. We have received your application and will review it with our hiring team. If your qualifications match our needs, we will contact you within two weeks. Best wishes, HR Department",
  
  "vendorInquiry": "Hi {vendorName}, Thank you for introducing your services to TechCorp. We are currently evaluating our vendor relationships and will reach out if we have a need that matches your offerings. Please keep us updated on your services quarterly. Thanks, Procurement Team",
  
  "mediaRequest": "Hello {reporterName}, Thank you for your media inquiry. Our PR team will review your request and respond within 48 hours. For urgent press matters, please contact our media hotline at 555-0199. Regards, Public Relations Team",
  
  "refundRequest": "Dear {customerName}, We have received your refund request for order #{orderNumber}. Our billing department will process this within 3-5 business days and send you a confirmation email. If you have questions, please contact billing@techcorp.com. Customer Care Team",
  
  "scheduleMeeting": "Hi {contactName}, Thank you for requesting a meeting. Please use our scheduling link to book a convenient time: www.calendly.com/techcorp-meetings. Available slots are Monday through Friday, 9 AM to 5 PM EST. Looking forward to speaking with you, Sales Team",
  
  "productFeedback": "Hello {customerName}, Thank you for taking the time to share your feedback about {productName}. Your insights help us improve our products. Our product team will review your suggestions for future updates. We appreciate your continued support, Product Team",
  
  "serviceNotOffered": "Hi {customerName}, Thank you for your inquiry about {requestedService}. Unfortunately, TechCorp does not currently offer this service. We specialize in cloud infrastructure and software development solutions. You might want to try reaching out to companies that focus specifically on {serviceCategory}. We appreciate your interest, Customer Service Team",
  
  "unsolicitedAdvertising": "Hello, Thank you for reaching out. TechCorp is not interested in {advertisedProduct} at this time. Please remove our email address from your mailing list and do not send us further promotional materials. If you continue to contact us after this request, we will report it as spam. Regards, TechCorp Administration"
}

To try this if you haven’t already from my previous part 1 post, go to https://script.google.com/home and add a new project (you can make a new Gmail account just for forwarding). Then, add a new script. In the Code.gs file, paste this into the editor window. Then add another script and name it ‘replies.json.html’, pasting in the JSON file.

Inside the Code.gs script, go to the sendReplyToSelf() function and replace the dummy email with your own email address where you will review the generated messages.

Your Gemini API key can be entered in https://script.google.com/home/projects under Settings. Run a test by forwarding an email to this new Gmail address, then click the “Run” button at the top of the code editor page. You should see the execution log at the bottom for any errors.

After confirming that it’s working, of course you’ll want to replace the dummy scenarios in the JSON file with your own frequently-encountered scenarios. It’s a lot of work to type out each one, so perhaps ask an AI to edit the JSON using your company FAQ, or your Zendesk help center documentation, etc.

You can also add fallbacks to another API, like OpenAI for example, in case Gemini is too overloaded. Just add an additional key inside your Apps Script settings page:

function generateReply(emailBody) {
  var prompt = buildPrompt(emailBody);
  
  // 1. Try with Gemini first (primary AI)
  var geminiReply = callGeminiApi(prompt);
  
  if (geminiReply !== "ERROR_GEMINI_OVERLOADED") {
    // If Gemini didn't fail with a 503, return its response (or any other error)
    return geminiReply;
  }
  
  // 2. If Gemini is overloaded, try with OpenAI (fallback AI)
  console.warn("Gemini is overloaded. Attempting to use OpenAI as a fallback...");
  var openAiReply = callOpenAiApi(prompt);
  
  if (openAiReply.startsWith("ERROR_")) {
    console.error("OpenAI fallback also failed. Error: " + openAiReply);
  } else {
    console.log("Successfully generated reply with OpenAI fallback.");
  }
  
  // Return the OpenAI response, whether it's a success or an error
  return openAiReply;
}

How to Deploy after Testing

You might notice that you need to manually run this app or that the timing trigger will stop working after a certain interval — this is because the app needs to be properly deployed.

From inside the Google Apps Script code editor, you’ll see a “Deploy” button in the upper-right of the screen — click that.

Then Select “API Executable” as the Deployment Type. Restrict access to yourself only.

Have you used this or other email automation? Please do let me know what’s working for you, or if you need help setting this up!