12 Days Of Digitalocean (day 11) - Save Receipt Data And Attachments In Google Sheets

Introduction

Welcome to Day 11 of nan “12 Days of DigitalOcean” series! Yesterday, we group up DigitalOcean Spaces to securely shop attachments from inbound emails. Today, we’ll return it a measurement further by integrating Google Sheets to shop immoderate nan extracted receipt specifications and nan URLs of uploaded attachments.

Google Sheets is simple, familiar, and cleanable for managing and hunt receipt data. By nan extremity of this tutorial, your app will seamlessly shop extracted receipt specifications and attachment URLs successful a Google Sheet for easy organization. Let’s get started!

What You’ll Learn

1. Configure a Google Cloud Project and alteration nan Sheets API.
2. Create a work relationship to securely entree Google Sheets.
3. Store Google credentials safely successful DigitalOcean App Platform.
4. Use gspread to programmatically update Google Sheets pinch receipt accusation and attachment URLs.

🛠 What You’ll Need

To get nan astir retired of this tutorial, we presume nan following:

1. A Flask App Already Deployed connected DigitalOcean: If you haven’t deployed a Flask app yet, you tin recreation nan instructions successful Day 7: Building and Deploying nan Email-Based Receipt Processor.
2. Postmark Configured for Email Testing: To proceedings nan email-to-receipt processing pipeline, you’ll petition Postmark group up to guardant emails to your Flask app. See Day 8: Connecting Postmark to Your Flask App for a step-by-step guide.
3. DigitalOcean Spaces Setup: Processed attachments will beryllium stored successful a DigitalOcean Space. If you don’t personification a Space yet, you tin recreation nan instructions successful Day 10: Storing Attachments successful DigitalOcean Spaces.
4. Access to Google Cloud Console: You’ll petition entree to Google Cloud Console to group up APIs, create a activity account, and make credentials for Google Sheets.
5. A Google Sheet Named Receipts: Ensure you personification a blank Google Sheet titled Receipts (or immoderate different name) caller to shop nan extracted data. You’ll banal this expanse pinch nan activity narration created during this tutorial.

Step 1: Set Up Google Sheets API

Before we tin prevention point to Google Sheets, we petition to make judge our app has support to interact pinch it. This involves creating a Google Cloud Project, enabling nan basal APIs, and mounting up a activity narration to grip nan dense lifting.

1.1 Create a Google Cloud Project

A Google Cloud Project is nan instauration for everything you’ll do pinch Google APIs. It acts arsenic your app’s location guidelines for managing resources and configurations.

1. Go to nan Google Cloud Console and click New Project.

   

2. Name your task (e.g., Receipt Processor) and click Create.

   

1.2 Enable APIs

The Google Sheets API lets your app represent to Google Sheets, and nan Google Drive API gives your app entree to files stored successful Google Drive.

1. Navigate to nan caller project. Then spell to APIs & Services > Enabled APIs & Services.
2. Search for Google Sheets API and Google Drive API.
3. Click Enable for both.

1.3 Create a Service Account

Your app can’t log successful to Google Sheets nan aforesaid measurement you do. Instead, it needs a service account—a emblematic bot that handles authentication and permissions for your app. This ensures your app tin securely entree Google Sheets without requiring manual logins aliases personification interaction.

1. Go to APIs & Services > Credentials and click + Create Credentials.

   

2. Select Service Account

   

3. Give your activity narration a punishment (e.g., Receipt Bot) and a mentation for illustration “Handles relationship pinch Google Sheets for receipt tracking.”

   <$> [info] Note: Your activity narration will make an email reside (e.g., [email protected]). You’ll petition this later to banal entree to your Google Sheet. <$>

   

4. Click Create and Continue until you’re backmost astatine nan credentials screen. You tin skip assigning roles if you don’t petition different permissions.

1.4 Download Credentials

The activity narration needs credentials to beryllium its identity. These recreation successful nan style of a JSON file.

1. In nan Credentials screen, find your activity narration and click nan pencil icon to edit it.

2. Go to nan Keys tab and click Add Key > Create New Key.

3. Select JSON and download nan file.

   

<$> [info] Note: This grounds contains everything your app needs to authenticate pinch Google Sheets. Treat it for illustration a password—don’t banal it aliases perpetrate it to Git. <$>

Finally, springiness nan activity narration support to entree your Google Sheet.

1. Get nan client_email from nan JSON grounds you downloaded

   

2. Open your Google Sheet. Click Share, paste nan email address, and springiness it Editor access.

   

Now your app tin publication from and represent to nan Google Sheet securely.

Step 2: Store Your Credentials Securely

We don’t want delicate credentials for illustration nan activity narration JSON lying astir successful our codebase—it’s a accusation risk. Instead, we’ll shop nan credentials arsenic an business adaptable successful DigitalOcean App Platform, wherever they’ll beryllium safe and accessible only astatine runtime.

The tricky information is that Google’s credentials JSON grounds is formatted for readability, but business variables petition a single-line string. Let’s spread that first.

2.1 Format nan JSON

Google’s credentials JSON is pretty, but we petition it compact for retention arsenic an business variable. Here’s really to personification it into a single-line string:

1. Run this Python snippet connected your conception machine:

   import json with open("path/to/service-account.json", "r") as file: creds = json.load(file) print(json.dumps(creds))

This will output nan JSON arsenic a azygous line. Copy nan result.

2.2 Add to DigitalOcean

Now, let’s shop nan single-line JSON securely successful DigitalOcean App Platform:

1. Go to your app’s Settings > Environment Variables.

2. Add a caller variable:

   1. Click connected Raw Editor, and paste nan single-line JSON string.
   2. Paste nan single-line JSON drawstring arsenic nan value:
   GOOGLE_CREDS={"type": "service_account", "project_id": "receipt-processor-45a6b", ......}

   

3. Check nan Encrypt action to support nan credentials safe. Click Save.

4. This action will trigger an automatic redeployment of your app.

   

   That’s it! Your credentials are now securely stored, and your app is caller to usage them astatine runtime.

Step 3: Update nan App

Now that your credentials are securely stored, it’s clip to nexus your app to Google Sheets and update it to grip receipt accusation and attachments.

Before diving into nan code, let’s make judge you’ve installed nan basal limitations and updated your requirements.txt file. This ensures your app has each nan libraries it needs to tally seamlessly.

3.1 Install Dependencies

Run nan pursuing bid to instal each required Python libraries:

pip install flask boto3 python-dotenv gspread oauth2client openai

Next, frost your limitations into a requirements.txt file:

pip frost > requirements.txt

This measurement captures each your app’s dependencies, making it easier to deploy and negociate successful DigitalOcean App Platform.

3.2 Connect to Google Sheets

This measurement allows your app to authenticate pinch Google Sheets utilizing nan credentials stored successful GOOGLE_CREDS. Once authenticated, nan app tin publication and represent to nan expanse programmatically.

Add this codification to your app:

import gspread from oauth2client.service_account import ServiceAccountCredentials import os import json creds_json = os.getenv("GOOGLE_CREDS") 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

3.3 Save Receipt Data and Attachments

Your app processes email accusation and attachments—this usability ensures immoderate are saved to Google Sheets. Each connection includes receipt specifications (e.g., vendor, amount, currency) and attachment URLs.

Add this usability to your app:

def save_to_google_sheets(extracted_data, attachment_urls): """ Save extracted receipt accusation and attachment URLs to Google Sheets. """ try: attachments_str = ", ".join([attachment["url"] for attachment in attachment_urls]) sheet.append_row([ extracted_data.get("vendor", ""), extracted_data.get("amount", ""), extracted_data.get("currency", ""), extracted_data.get("date", ""), attachments_str ]) logging.info("Data and attachments saved to Google Sheets.") except Exception as e: logging.error(f"Failed to prevention accusation to Google Sheets: {e}")

3.4 Final Code

Here’s nan complete codification for your app, consolidating each nan pieces we’ve worked connected truthful far.

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_dotenv() app = Flask(__name__) logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s') 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) 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"https://{SPACES_BUCKET}.{SPACES_REGION}.digitaloceanspaces.com" 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 ) creds_json = os.getenv("GOOGLE_CREDS") if not creds_json: raise ValueError("Google credentials not recovered successful business 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 def extract_text_from_email(email_content): """Extract applicable specifications from nan email contented utilizing DigitalOcean GenAI.""" logging.debug("Extracting specifications from email content.") punctual = ( "Extract nan pursuing specifications from nan email:\n" "- Date of transaction\n" "- Amount\n" "- Currency\n" "- Vendor name\n\n" f"Email content:\n{email_content}\n\n" "Ensure nan output is successful JSON format pinch keys: date, amount, currency, vendor." ) consequence = client.chat.completions.create( model="your-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 prevention it locally pinch a unsocial 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 prevention attachment {file_name}: {e}") return None def upload_attachment_to_spaces(file_path): """Upload a grounds to DigitalOcean Spaces and return its nationalist 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"https://{SPACES_BUCKET}.{SPACES_REGION}.cdn.digitaloceanspaces.com/{object_name}" 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 each 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) return attachment_urls def save_to_google_sheets(extracted_data, attachment_urls): """Save extracted receipt accusation and attachment URLs to Google Sheets.""" try: attachments_str = ", ".join([attachment["url"] for attachment in attachment_urls]) sheet.append_row([ extracted_data.get("vendor", ""), extracted_data.get("amount", ""), extracted_data.get("currency", ""), extracted_data.get("date", ""), attachments_str ]) logging.info("Data and attachments saved to Google Sheets.") except Exception as e: logging.error(f"Failed to prevention accusation 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.") accusation = request.json email_content = data.get("TextBody", "") attachments = data.get("Attachments", []) if not email_content: logging.error("No email contented provided.") return jsonify({"error": "No email contented provided"}), 400 extracted_data = extract_text_from_email(email_content) attachment_urls = process_attachments(attachments) save_to_google_sheets(extracted_data, attachment_urls) response_data = { "extracted_data": extracted_data, "attachments": attachment_urls } logging.info("Final Response Data: %s", response_data) return jsonify(response_data) if __name__ == "__main__": logging.info("Starting Flask application.") app.run(port=5000)

Step 4: Deploy to DigitalOcean

To deploy nan updated Flask app, recreation nan steps from Day 7: Building and Deploying nan Email-Based Receipt Processor. Here’s a speedy summary:

1. Push Your Updated Code to GitHub: After making nan basal changes to your Flask app, perpetrate and push nan updated codification to GitHub. This will trigger an automatic deployment successful DigitalOcean’s App Platform.

   git add . git perpetrate -m "Add attachment processing pinch DigitalOcean Spaces" git push guidelines main

2. Monitor Deployment: You tin measurement nan advancement successful nan Deployments conception of your app’s dashboard.

   

3. Verify Your Deployment: After nan deployment completes, navigate to your app’s nationalist URL and proceedings its functionality. You tin too cheque nan runtime logs successful nan dashboard to corroborate that nan app started successfully.

   

Step 5: Test nan Entire Workflow

Now that your app is afloat configured and ready, it’s clip to proceedings nan afloat workflow. We’ll guarantee that nan email assemblage is processed, attachments are decoded and uploaded to DigitalOcean Spaces, and nan past output includes receipt specifications and attachment URLs, all saved successful Google Sheets.

Here’s really you tin proceedings measurement by step:

1. Send a Test Email: Send an email to Postmark pinch a matter assemblage and an attachment. If you’re unsure really to configure Postmark, cheque Day 8: Connecting Postmark to Your Flask App wherever we walked done mounting up Postmark to guardant emails to your app.

   

2. Check Postmark Activity JSON: In nan Postmark dashboard, navigate to nan Activity tab. Locate nan email you sent, and guarantee that nan JSON payload includes nan matter assemblage and Base64-encoded attachment data. This confirms Postmark is correctly forwarding nan email accusation to your app.

   

3. Monitor nan Logs: Check nan runtime logs successful your DigitalOcean App Platform dashboard to guarantee nan app processes nan JSON payload. We covered really to entree runtime logs successful Day 9: Automating Receipt Parsing pinch DigitalOcean’s GenAI Agent.

   

4. Verify Spaces Upload: Visit your DigitalOcean Space to corroborate that nan files were uploaded successfully. You should spot nan attachments successful your bucket.

   

5. Check Google Sheets: Open your Google Sheet and corroborate that receipt specifications and attachment URLs are saved arsenic a caller row. The specifications should include:

   • Vendor, amount, currency, and time extracted from nan email body.
   • Comma-separated URLs for nan uploaded attachments successful nan past column.

   

By nan extremity of these steps, your app will personification successfully completed nan afloat email-to-Google Sheets workflow, mounting nan style for further automation.

🎁 Wrap-Up

Amazing work! Today, you’ve learned really to seamlessly merge Google Sheets into your app to negociate receipt data. Specifically, you:

• Set up a Google Cloud Project and enabled nan Sheets API.

• Created a activity narration and securely stored its credentials successful DigitalOcean App Platform.

• Used gspread to programmatically update Google Sheets pinch receipt specifications and attachment URLs.

Up Next: In nan past tutorial, we’ll complete nan automation by enabling your app to nonstop confirmation emails whenever a receipt is processed successfully. These emails will spot nan extracted receipt specifications and a nonstop nexus to nan Google Sheet for easy entree and verification.

See you successful nan past conception of nan 12 Days of DigitalOcean series! 🚀