Welcome to Day 11 of the “12 Days of DigitalOcean” series! In today’s tutorial, we will integrate Google Sheets into our app to store extracted receipt details and uploaded attachment URLs. Google Sheets provides a simple and familiar interface for managing and tracking receipt data, making our application even more effective.

What You’ll Learn

1. Set up a Google Cloud Project and enable the Google Sheets API.
2. Create a service account to securely access Google Sheets.
3. Store Google credentials safely in DigitalOcean App Platform.
4. Use the gspread library to update Google Sheets with receipt data and attachment URLs programmatically.

What You’ll Need

To follow along, ensure you have:

1. A deployed Flask app on DigitalOcean. Instructions can be found in Day 7 of our series.
2. Postmark configured for testing email receipts. Follow Day 8 to connect Postmark to your app.
3. DigitalOcean Spaces set up for storing processed attachments. Check Day 10 for guidance.
4. Access to Google Cloud Console to configure APIs and create a service account.
5. A blank Google Sheet titled Receipts (or another name of your choice) shared with the service account created during this process.

Step 1: Set Up Google Sheets API

First, we’ll secure permission for our app to interact with Google Sheets.

1. Create a Google Cloud Project

   • Go to the Google Cloud Console and click on New Project.
   • Name your project (e.g., Receipt Processor) and click Create.

2. Enable APIs

   • Navigate to APIs & Services > Enabled APIs & Services.
   • Search for Google Sheets API and Google Drive API. Enable both APIs.

3. Create a Service Account

   • Go to APIs & Services > Credentials and click + Create Credentials.
   • Choose Service Account, give it a name, and click Create and Continue. You can skip role assignment for this tutorial.

4. Download Credentials

   • Locate your service account in the Credentials screen, edit it, and go to the Keys tab.
   • Click Add Key > Create New Key, select JSON, and download the file.

5. Share the Google Sheet

   • From the JSON file you downloaded, get the client_email.
   • Open your Google Sheet, click Share, paste the email address, and assign Editor access.

Step 2: Store Your Credentials Securely

To prevent sensitive credentials from being exposed in your codebase, store them as an environment variable in DigitalOcean App Platform.

1. Format the JSON

   • Run a Python script that reads your service account JSON file and prints it as a single-line string.

2. Add to DigitalOcean

   • Go to your app’s Settings > Environment Variables.
   • Create a new variable named GOOGLE_CREDS and paste the single-line JSON string as the value. Ensure you check the Encrypt option before saving.

Step 3: Update the App

Now, connect your app to Google Sheets and update it to handle receipt data and attachments.

1. Install Dependencies

   • Run pip install flask boto3 python-dotenv gspread oauth2client openai to install necessary libraries.
   • Capture installed libraries in requirements.txt with pip freeze > requirements.txt.

2. Connect to Google Sheets

   • Add code to your Flask app to load the GOOGLE_CREDS and authenticate using the gspread library. Replace the name of your Google Sheet with your own.

3. Save Receipt Data and Attachments

   • Implement a function that takes extracted receipt data and processed attachment URLs, combining them into a single row in your Google Sheet.

Step 4: Deploy to DigitalOcean

Push your code updates to GitHub which will trigger automatic deployment in DigitalOcean. Track the progress in the Deployments section of your dashboard.

Step 5: Test the Entire Workflow

Finally, send a test email to your Postmark inbox and monitor the entire process:

1. Send a Test Email with the text body and attachment.
2. Check Postmark Activity to confirm JSON payloads.
3. Review Logs on your DigitalOcean dashboard to verify processing.
4. Check DigitalOcean Spaces for uploaded files.
5. Open Google Sheets to verify that data was added successfully.

Wrap-Up

Congratulations! You’ve successfully integrated Google Sheets into your application to manage receipt data. You now have a complete email-to-Google Sheets workflow, setting the stage for further automation, like sending confirmation emails for processed receipts.

Thank you for following along with the DigitalOcean Community!

Welcome to DediRock, your trusted partner in high-performance hosting solutions. At DediRock, we specialize in providing dedicated servers, VPS hosting, and cloud services tailored to meet the unique needs of businesses and individuals alike. Our mission is to deliver reliable, scalable, and secure hosting solutions that empower our clients to achieve their digital goals. With a commitment to exceptional customer support, cutting-edge technology, and robust infrastructure, DediRock stands out as a leader in the hosting industry. Join us and experience the difference that dedicated service and unwavering reliability can make for your online presence. Launch our website.