Labeled emails and ChatGPT Integration to Google Sheet
Extract key information from labeled emails and use ChatGPT to categorize important information and add to a Google Sheet
Solution Overview
Platform: Zapier (or Make.com)
Workflow Steps:
Trigger: New Labeled Email in Gmail.
Action: Extract key information from the email.
Action: Use ChatGPT to categorize the information.
Action: Add the categorized information to a Google Sheet.
Custom Scripts: Additional scripts to enhance the workflow.
Step-by-Step Implementation
Step 1: Trigger – New Labeled Email in Gmail
App: Gmail
Trigger Event: New Email Matching Search (for labeled emails)
Details: Set up the trigger to fire whenever a new email with a specific label is received in Gmail.
Step 2: Action – Extract Key Information from Email
Action: Use a text extraction tool or custom script to extract key details such as sender, subject, body content, dates, etc.
Step 3: Action – Use ChatGPT to Categorize Information
App: ChatGPT (OpenAI)
Action Event: Send the extracted information to ChatGPT to categorize it based on the context.
Step 4: Action – Add Categorized Information to Google Sheets
App: Google Sheets
Action Event: Add a new row with the categorized information.
Step 5: Custom Scripts
Custom Script 1: Extract Specific Details from Email Body
This script will extract key information such as the order number, customer name, and relevant dates from the email body.
// Script to extract key details from email body
const emailBody = inputData.emailBody;
const orderNumber = emailBody.match(/Order Number: (d+)/)[1];
const customerName = emailBody.match(/Customer Name: ([A-Za-zs]+)/)[1];
const date = emailBody.match(/Date: (d{2}/d{2}/d{4})/)[1];
return { orderNumber, customerName, date };
Usage: Use this script in Zapier to parse the email body and extract specific details.
Custom Script 2: Clean and Standardize Extracted Data
This script cleans and standardizes the extracted data to ensure consistency before sending it to ChatGPT.
// Script to clean and standardize data
const customerName = inputData.customerName.trim().toUpperCase();
const orderNumber = inputData.orderNumber.replace(/[^0-9]/g, '');
const date = new Date(inputData.date).toISOString();
return { customerName, orderNumber, date };
Usage: Add this script before sending data to ChatGPT to ensure that the information is standardized.
Custom Script 3: Categorize Email Content with ChatGPT
Send the cleaned data to ChatGPT and categorize the email based on its content (e.g., customer support, sales inquiry, feedback).
// Script to categorize email content using ChatGPT
const prompt = `
Categorize the following email details:
Customer Name: ${inputData.customerName}
Order Number: ${inputData.orderNumber}
Date: ${inputData.date}
Possible Categories: Customer Support, Sales Inquiry, Feedback, General Information
`;
const response = await fetch("https://api.openai.com/v1/completions", {
method: "POST",
headers: {
"Content-Type": "application/json",
"Authorization": `Bearer YOUR_OPENAI_API_KEY`
},
body: JSON.stringify({
model: "text-davinci-003",
prompt: prompt,
max_tokens: 50
})
});
const data = await response.json();
const category = data.choices[0].text.trim();
return { category };
Usage: This script sends data to ChatGPT and retrieves a category for the email.
Custom Script 4: Append Categorized Data to Google Sheets
Add the categorized information to a specific row in a Google Sheet.
// Script to append categorized data to Google Sheets
const spreadsheetId = "your-google-sheet-id";
const range = "Sheet1!A1";
return {
spreadsheetId,
range,
values: [
[inputData.orderNumber, inputData.customerName, inputData.date, inputData.category]
]
};
Usage: Use this script to add the categorized data to your Google Sheet.
Custom Script 5: Log Processed Emails
Log each processed email in a Google Sheet to track which emails have been processed and categorized.
// Script to log processed emails
const emailId = inputData.emailId;
const processedDate = new Date().toISOString();
return {
spreadsheetId: "your-log-sheet-id",
range: "Logs!A1",
values: [
[emailId, processedDate]
]
};
Usage: This script can be used to maintain a log of processed emails in a separate Google Sheet.
Custom Script 6: Send Notification for Specific Categories
Send a Slack notification if the email falls under a specific category like "Customer Support" for immediate attention.
// Script to send Slack notification for specific categories
const category = inputData.category;
if (category === "Customer Support") {
return {
message: `Urgent: A new Customer Support email has been received.`
};
} else {
return { message: null };
}
Usage: This script can be used to trigger notifications for specific categories.
Final Zap Setup
Trigger: New Labeled Email in Gmail.
Action: Custom Script 1 - Extract Specific Details from Email Body.
Action: Custom Script 2 - Clean and Standardize Extracted Data.
Action: Custom Script 3 - Categorize Email Content with ChatGPT.
Action: Custom Script 4 - Append Categorized Data to Google Sheets.
Action: Custom Script 5 - Log Processed Emails.
Action: Custom Script 6 - Send Notification for Specific Categories.
Summary
This solution extracts key information from labeled emails, categorizes it using ChatGPT, and adds the categorized data to Google Sheets. The custom scripts enhance the workflow by automating data extraction, standardization, logging, and notifications, making the process more efficient and robust.