12 Days of the cloud provider (Day 11) – Save Receipt Data and Attachments in Google Sheets

				
					from flask import Flask, request, jsonify
import os
import base64
import uuid
import boto3
from dotenv import load_dotenv
from openai import OpenAI
import logging
import json
import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Load environment variables
load_dotenv()

# Initialize Flask app
app = Flask(__name__)

# Configure logging
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')

# Initialize the cloud provider GenAI client
SECURE_AGENT_KEY = os.getenv("SECURE_AGENT_KEY")
AGENT_BASE_URL = os.getenv("AGENT_BASE_URL")
AGENT_ENDPOINT = f"{AGENT_BASE_URL}/api/v1/"
client = OpenAI(base_url=AGENT_ENDPOINT, api_key=SECURE_AGENT_KEY)

# object storage credentials
SPACES_ACCESS_KEY = os.getenv("SPACES_ACCESS_KEY")
SPACES_SECRET_KEY = os.getenv("SPACES_SECRET_KEY")
SPACES_BUCKET = os.getenv("SPACES_BUCKET_NAME")
SPACES_REGION = os.getenv("SPACES_REGION")
SPACES_ENDPOINT = f"images/storing-receipt-details-in-google-sheets-section-1.png"

# Initialize object storage client
session = boto3.session.Session()
s3_client = session.client(
 's3',
 region_name=SPACES_REGION,
 endpoint_url=SPACES_ENDPOINT,
 aws_access_key_id=SPACES_ACCESS_KEY,
 aws_secret_access_key=SPACES_SECRET_KEY
)

# Google Sheets API setup
creds_json = os.getenv("GOOGLE_CREDS")
if not creds_json:
 raise ValueError("Google credentials not found in environment variables.")
creds_dict = json.loads(creds_json)

scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
credentials = ServiceAccountCredentials.from_json_keyfile_dict(creds_dict, scope)
sheets_client = gspread.authorize(credentials)
sheet = sheets_client.open("Receipts").sheet1 # Replace "Receipts" with your sheet name

def extract_text_from_email(email_content):
 """Extract relevant details from the email content using the cloud provider GenAI."""
 logging.debug("Extracting details from email content.")
 prompt = (
 "Extract the following details from the email:\n"
 "- Date of transaction\n"
 "- Amount\n"
 "- Currency\n"
 "- Vendor name\n\n"
 f"Email content:\n{email_content}\n\n"
 "Ensure the output is in JSON format with keys: date, amount, currency, vendor."
 )
 response = client.chat.completions.create(
 model="your-model-id", # Replace with your GenAI model ID
 messages=[{"role": "user", "content": prompt}]
 )
 logging.debug("GenAI processing completed.")
 return json.loads(response.choices[0].message.content)

def decode_and_save_attachment(attachment):
 """Decode base64-encoded attachment and save it locally with a unique name."""
 file_name = attachment.get("Name")
 encoded_content = attachment.get("Content")

 if not file_name or not encoded_content:
 logging.warning("Invalid attachment, skipping.")
 return None

 unique_file_name = f"{uuid.uuid4()}_{file_name}"
 file_path = os.path.join("/tmp", unique_file_name)

 try:
 with open(file_path, "wb") as file:
 file.write(base64.b64decode(encoded_content))
 logging.info(f"Attachment saved locally: {file_path}")
 return file_path
 except Exception as e:
 logging.error(f"Failed to decode and save attachment {file_name}: {e}")
 return None

def upload_attachment_to_spaces(file_path):
 """Upload a file to object storage and return its public URL."""
 file_name = os.path.basename(file_path)
 object_name = f"email-receipt-processor/{file_name}"
 try:
 s3_client.upload_file(file_path, SPACES_BUCKET, object_name, ExtraArgs={"ACL": "public-read"})
 file_url = f"images/storing-receipt-details-in-google-sheets-section-1.png}"
 logging.info(f"Attachment uploaded to Spaces: {file_url}")
 return file_url
 except Exception as e:
 logging.error(f"Failed to upload attachment {file_name} to Spaces: {e}")
 return None

def process_attachments(attachments):
 """Process all attachments and return their URLs."""
 attachment_urls = []
 for attachment in attachments:
 file_path = decode_and_save_attachment(attachment)
 if file_path:
 file_url = upload_attachment_to_spaces(file_path)
 if file_url:
 attachment_urls.append({"file_name": os.path.basename(file_path), "url": file_url})
 os.remove(file_path) # Clean up local file
 return attachment_urls

def save_to_google_sheets(extracted_data, attachment_urls):
 """Save extracted receipt data and attachment URLs to Google Sheets."""
 try:
 # Combine all attachment URLs into a single string (comma-separated)
 attachments_str = ", ".join([attachment["url"] for attachment in attachment_urls])

 # Append a new row with extracted data and attachment URLs
 sheet.append_row([
 extracted_data.get("vendor", ""),
 extracted_data.get("amount", ""),
 extracted_data.get("currency", ""),
 extracted_data.get("date", ""),
 attachments_str # Store all attachment URLs in a single column
 ])
 logging.info("Data and attachments saved to Google Sheets.")
 except Exception as e:
 logging.error(f"Failed to save data to Google Sheets: {e}")

@app.route('/inbound', methods=['POST'])
def handle_inbound_email():
 """Process inbound emails and return extracted JSON."""
 logging.info("Received inbound email request.")
 data = request.json

 email_content = data.get("TextBody", "")
 attachments = data.get("Attachments", [])

 if not email_content:
 logging.error("No email content provided.")
 return jsonify({"error": "No email content provided"}), 400

 extracted_data = extract_text_from_email(email_content)
 attachment_urls = process_attachments(attachments)

 # Save extracted data and attachment URLs to Google Sheets
 save_to_google_sheets(extracted_data, attachment_urls)

 response_data = {
 "extracted_data": extracted_data,
 "attachments": attachment_urls
 }

 # Log the final combined data
 logging.info("Final Response Data: %s", response_data)

 return jsonify(response_data)

if __name__ == "__main__":
 logging.info("Starting Flask application.")
 app.run(port=5000)