Automate Your Expense Tracking for Free: An AI Trick with Google Sheets

---

Automate Your Expense Tracking for Free: An AI Trick with Google Sheets

As an entrepreneur, you're buried in a mountain of tiny tasks. One of the most tedious is tracking expenses. Every coffee meeting, software subscription, and supply purchase comes with an email receipt. Manually entering each one into a spreadsheet is a slow, mind-numbing process that steals hours you could be spending on growing your business.

What if you could build a system that did it for you, automatically?

Today, I’m going to show you a slightly "tricky," but incredibly powerful method to create your own automated expense tracker. It uses the tools you already have—Gmail and Google Sheets—and connects them with a free AI "brain." This system will read your receipts, pull out the key information, and log it neatly in your spreadsheet, all on its own.

The Ingredients for Your Automation Machine

This entire system is built using free tools:

1. A Gmail Account: Where you receive your receipts.

2. A Google Sheet: Your new, automated expense log.

3. A Free Google AI API Key: This is the "brain" that will read and understand the receipts. Don't worry, getting one is simple and free.

The 3-Step Process to Build Your Tracker

Follow these steps carefully. In about 15 minutes, you'll have a working system.

Step 1: Prepare Your Google Sheet

This is your command center.

• Create a new Google Sheet. Name it "Automated Expense Log."

• In the first row, create the following headers in columns A, B, C, and D: Date, Vendor, Amount, and Source Email.

Step 2: Get Your Free AI "Brain" (API Key)

• Go to Google AI Studio: Outbound Link 

• Click "Create API key in new project."

• Copy the long string of letters and numbers that it generates. This is your secret key. Do not share it publicly.

Step 3: Add the "Automator" Code

This is where the magic happens. We'll add a script to your Google Sheet that connects to your Gmail and the AI.

1. From your Google Sheet, click Extensions > Apps Script.

2. A new browser tab will open with a code editor. Delete any placeholder code in the Code.gs file.

3. Copy and paste the entire block of code below into the editor.

JavaScript

// A script to automatically process email receipts and log them in a Google Sheet.

// Paste your Google AI API Key in the quotes below.
const API_KEY = "PASTE_YOUR_API_KEY_HERE";

// This is the main function that will be triggered.
function processReceipts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const gmailLabel = "Receipts"; // The Gmail label to search for.
  
  // Find all emails with the "Receipts" label that haven't been processed.
  const threads = GmailApp.search(`label:${gmailLabel} is:unread`);

  for (const thread of threads) {
    const message = thread.getMessages()[0]; // Get the first message in the thread.
    const emailBody = message.getPlainBody(); // Get the plain text of the email.
    
    // Check if the email body is not too long to avoid errors.
    if (emailBody.length > 10000) {
      Logger.log("Email body too long, skipping.");
      GmailApp.markThreadRead(thread);
      continue;
    }

    try {
      // Send the email text to the AI for analysis.
      const extractedData = callGenerativeAI(emailBody);
      
      // Add the extracted data as a new row in the spreadsheet.
      sheet.appendRow([
        extractedData.date || "N/A",
        extractedData.vendor || "N/A",
        extractedData.amount || "N/A",
        message.getFrom() // Add the sender's email for reference.
      ]);
      
      // Mark the email as "read" so it doesn't get processed again.
      GmailApp.markThreadRead(thread);

    } catch (e) {
      Logger.log("Error processing email: " + e.toString());
    }
  }
}

// This function calls the Google AI (Gemini) API.
function callGenerativeAI(text) {
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash-latest:generateContent?key=${API_KEY}`;
  
  const prompt = `
    Analyze the following email receipt text and extract the vendor name, the final total amount, and the transaction date. 
    Format the output as a single, clean JSON object with the keys "vendor", "amount", and "date". 
    The date should be in YYYY-MM-DD format. The amount should be a number only, without currency symbols.
    
    Here is the text:
    ---
    ${text}
    ---
  `;

  const requestBody = {
    "contents": [{
      "parts": [{ "text": prompt }]
    }]
  };

  const options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(requestBody)
  };

  const response = UrlFetchApp.fetch(url, options);
  const responseText = response.getContentText();
  
  // Clean up the response from the AI.
  const cleanedJson = responseText.match(/```json\n([\s\S]*?)\n```/)[1];
  return JSON.parse(cleanedJson);
}

4. Find the line const API_KEY = "PASTE_YOUR_API_KEY_HERE"; and paste your secret API key between the quotes.

5. Click the Save project icon (it looks like a floppy disk).

6. Set up the Automatic Trigger:

   • On the left menu, click the Triggers icon (it looks like a clock).

   • Click the "+ Add Trigger" button in the bottom right.

   • Set up the trigger with these settings:

     • Choose which function to run: processReceipts

     • Choose which deployment should run: Head

     • Select event source: Time-driven

     • Select type of time-based trigger: Hour timer

     • Select hour interval: Every hour

   • Click Save. You will be asked to grant permissions. Allow them. This is necessary for the script to read your Gmail and write to your Sheet.

(H2) How to Use Your New Automation

You're all set! Now, the process is simple:

1. In your Gmail, create a new label named Receipts.

2. Whenever you get an email receipt, simply apply the Receipts label to it.

3. Every hour, your script will run, find any unread emails with that label, and your Google Sheet will magically populate with the new expense data.

This trick is a perfect example of how you can use AI automation,