Apple Billing Receipt Parsing (not Apple card) for Google Sheet Apps Script

stephensandlin · May 31, 2026, 6:11pm

I’m posting this here in case it’s useful to anyone, or ideally, if someone wants to build from it to make something even more robust. I’m not a scripting or RegEx expert, but I understand the frameworks enough to dabble… or at least to tell Gemini what I want, for some trial and error.

I currently have all of my Apple family spending (App Purchases, App subscriptions, Streaming Subscriptions, Music Purchases, Movie Rentals, AppleCare+ Subscriptions, etc…) on a credit card. When the credit card statement comes into Tiller, the description simple comes up as “Apple. com/us” or “Apple. com/bill”. That is relatively useless to me, as I have specific Categories in Tiller for Software, Digital Subscriptions, Streaming Services, Movies, Music, etc… So, AutoCat has nothing to start from.

Apple’s web-based purchase list is atrocious, non-exportable, and basically non-scrapeable. However, what I do have is a decade’s worth of Apple receipt in my Gmail Inbox, all with the subject line: “Your receipt from Apple.” Enter a scripting solution. I was inspired by @daveahlers 's Amazon Importer and @jsharpie7 's A Better Way To Deal with Amazon Transactions, as at least a starting points for what could be done with the receipts I was receiving.

I’ve been working with Gemini to craft an Apps Script for Google Sheets. It does some intense and hyper-specific Regular Expression processing on the emails to extract useable data. It also accounts for Apple changing their email receipt formatting in mid-2024.

Basically, it goes from this raw email:

To this data in a spreadsheet:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

So, please read, below… Is it perfect? No. Is it a start for something? Maybe. It was at least useful for getting my Apple line items in Tiller in a semi-organized way.

I’m really hoping that someone out there has the skill to take this basic idea, can clean up the script or offer suggestions, or even turn it into a cleaner solution that might be helpful to more people. Thanks to Tiller for existing, and to this community for being willing to help others.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Currently, I followed these steps:

In Google Sheets, create a blank Google Sheet titled “Apple Billing”

Go to Extensions > Apps Script and paste the script

function parseAppleReceipts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Apple Billing") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("Apple Billing");

  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      "Date", "Description", "Amount", "Institution", "Account #", "Account", 
      "Time", "Document No", "Subtotal", "Tax", "Payment Method"
    ]);
  }

  // Remove the 'newer_than:2d' operator temporarily if you are running this to backfill your entire history
  const searchQuery = 'subject:"Your receipt from Apple." from:no_reply@email.apple.com';
  const threads = GmailApp.search(searchQuery);

  const formatCutoffDate = new Date('2024-08-24T00:00:00');

  threads.forEach(thread => {
    const messages = thread.getMessages();
    
    messages.forEach(message => {
      const body = message.getPlainBody();
      const msgDate = message.getDate();
      const isNewFormat = msgDate >= formatCutoffDate;

      // Detect if this receipt is a refund to toggle positive/negative output
      const isRefund = /refund/i.test(body);

      // --- 1. CORE DATA EXTRACTION ---
      const orderIdMatch = body.match(/ORDER ID:?\s*([A-Z0-9]+)/i);
      const docNoMatch = body.match(/DOCUMENT(?: NO\.?)?:?\s*(\d+)/i);
      const accountMatch = body.match(/([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})/);
      
      const orderId = orderIdMatch ? orderIdMatch[1] : "N/A";
      const docNo = docNoMatch ? docNoMatch[1] : "N/A";
      const email = accountMatch ? accountMatch[0] : "N/A";
      
      const dateStr = Utilities.formatDate(msgDate, Session.getScriptTimeZone(), "MM/dd/yyyy");
      const timeStr = Utilities.formatDate(msgDate, Session.getScriptTimeZone(), "HH:mm:ss");

      // Robust Financial Extraction (Ensures Grand Total is captured regardless of format)
      let subtotal = "N/A";
      let subMatches = [...body.matchAll(/(?:Subtotal|SUBTOTAL)[^\$]{0,150}?\$((?:\d{1,3},)?\d{1,3}\.\d{2})/g)];
      if (subMatches.length > 0) subtotal = "$" + subMatches[subMatches.length - 1][1];

      let tax = "N/A";
      let taxMatches = [...body.matchAll(/(?:Tax|TAX)[^\$]{0,150}?\$((?:\d{1,3},)?\d{1,3}\.\d{2})/g)];
      if (taxMatches.length > 0) tax = "$" + taxMatches[taxMatches.length - 1][1];

      let total = "N/A";
      let totalMatches = [...body.matchAll(/(?:TOTAL|Total|Amount Billed|Amount Charged)[^\$]{0,150}?\$((?:\d{1,3},)?\d{1,3}\.\d{2})/g)];
      
      if (totalMatches.length > 0) {
          total = "$" + totalMatches[totalMatches.length - 1][1];
      } else {
          let allPrices = [...body.matchAll(/\$((?:\d{1,3},)?\d{1,3}\.\d{2})/g)];
          if (allPrices.length > 0) {
              let lastFew = allPrices.slice(-4).map(m => parseFloat(m[1].replace(/,/g, '')));
              total = "$" + Math.max(...lastFew).toFixed(2);
          }
      }

      let paymentMethod = "N/A";
      const cardMatch = body.match(/(Visa|MasterCard|Amex|Discover|Apple Card|PayPal)[.\s\d]*(\(Apple Pay\))?/i);
      if (cardMatch) {
        paymentMethod = cardMatch[0].trim();
      }

      // --- 2. ISOLATE TARGET TEXT BLOCK ---
      let startIndex = email !== "N/A" ? body.indexOf(email) + email.length : 0;
      
      if (docNo !== "N/A") {
        let docIndex = body.indexOf(docNo);
        if (docIndex > startIndex) {
          startIndex = docIndex + docNo.length;
        }
      }

      const remainingBody = body.substring(startIndex);
      
      let endIndexMatch = remainingBody.match(/Billing and Payment|Subtotal/i);
      if (!endIndexMatch) {
          let lastTotalIndex = remainingBody.search(/\b(?:TOTAL|Total)\b(?![\s\S]*\b(?:TOTAL|Total)\b)/i);
          if (lastTotalIndex !== -1) endIndexMatch = { index: lastTotalIndex };
      }
      
      let rawItems = endIndexMatch ? remainingBody.substring(0, endIndexMatch.index) : remainingBody;

      // --- 3. UNIVERSAL FORMATTING LOGIC ---
      let itemsArray = [];

      if (/AppleCare/i.test(body)) {
          let aggMatch = body.match(/Agreement\/?Policy\s*Number:?\s*([A-Z0-9]+)/i) || body.match(/Agreement\s*Number:?\s*([A-Z0-9]+)/i);
          let policyNum = aggMatch ? aggMatch[1] : "";
          
          let itemPrice = "N/A";
          let priceMatch = rawItems.match(/\$(?:\d{1,3},)?\d{1,3}\.\d{2}/);
          if (priceMatch) {
              itemPrice = priceMatch[0];
          } else if (subtotal !== "N/A") {
              itemPrice = subtotal;
          } else if (total !== "N/A") {
              let tF = parseFloat(total.replace(/[\$,]/g, ''));
              let taxF = tax !== "N/A" ? parseFloat(tax.replace(/[\$,]/g, '')) : 0;
              itemPrice = "$" + (tF - taxF).toFixed(2);
          }

          let desc = ("AppleCare+ " + policyNum).trim() + (itemPrice !== "N/A" ? " - " + itemPrice : "");
          itemsArray.push(desc);
          
      } else {
          let cleanText = rawItems;

          cleanText = cleanText.replace(/BILLED TO[\s\S]*?(?:USA|United States)/i, "");
          cleanText = cleanText.replace(/DATE\s*[A-Z][a-z]{2}\s\d{1,2},?\s\d{4}/ig, "");
          cleanText = cleanText.replace(/[A-Z][a-z]{2}\s\d{1,2},?\s\d{4}\s*TOTAL/ig, ""); 
          cleanText = cleanText.replace(/\bDATE\b/ig, "");
          cleanText = cleanText.replace(/(?:Seller\s+|Artist\s+|Cloud\s+|Art\s+)?(?:Seller\s+|Artist\s+)?Type\s+Unit\s+Price/ig, "");
          cleanText = cleanText.replace(/iTunes Store|App Store|Mac App Store|Apple Music|PRICE/ig, "");
          cleanText = cleanText.replace(/^iCloud\s*$/igm, "");
          cleanText = cleanText.replace(/<[^>]+>/g, ""); 
          cleanText = cleanText.replace(/\[[^\]]+\]/g, ""); 
          cleanText = cleanText.replace(/with Apple Card\.?\s*1\s*Apply and use in\s*\n?minutes\.?\s*2?/ig, "");
          cleanText = cleanText.replace(/Save 3% on all your Apple purchases.*?(?:minutes\.2?)?/ig, "");
          cleanText = cleanText.replace(/DOCUMENT(?: NO\.?)?:?\s*\d+/ig, ""); 

          cleanText = cleanText.replace(/[\r\n]+/g, " "); 
          
          let itemChunks = cleanText.match(/(.*?\$(?:\d{1,3},)?\d{1,3}\.\d{2})/g);
          
          if (itemChunks) {
              itemChunks.forEach(chunk => {
                  let priceMatch = chunk.match(/\$(?:\d{1,3},)?\d{1,3}\.\d{2}/);
                  if (priceMatch) {
                      let price = priceMatch[0];
                      let name = chunk.replace(price, "").trim();
                      
                      name = name.replace(/-{2,}/g, " "); 
                      name = name.replace(/Renews\s+[A-Z][a-z]{2,8}\s+\d{1,2},?\s+\d{4}/ig, "");
                      name = name.replace(/(?:Next\s+)?(?:Billing\s+Date|Contract\s+Renewal\s+Date):?\s*[A-Z][a-z]{2,8}\s+\d{1,2},?\s+\d{4}/ig, "");
                      name = name.replace(/(?:Action\s*&\s*Adventure|Comedy|Drama|Horror|Documentary|Kids\s*&\s*Family|Sci-Fi\s*&\s*Fantasy|Romance|Independent|Concert Films|Music Feature Films|Thriller|Classic|Short Films|Anime)\s+Movie Rental/ig, "Movie Rental");
                      name = name.replace(/\(\s*S\/N:\s*[A-Z0-9]+\s*\)/ig, "");
                      name = name.replace(/Apple\s*TV(?:\s*-\s*[A-Z]+)?/ig, ""); 
                      name = name.replace(/\(Monthly\)/ig, "");
                      name = name.replace(/\(Annual\)/ig, "");
                      name = name.replace(/\(Yearly\)/ig, "");
                      name = name.replace(/\(?Automatic Renewal\)?/ig, "");
                      name = name.replace(/Subscription Renewal/ig, "");
                      name = name.replace(/Write a Review\s*\|\s*Report a Problem/ig, "");
                      name = name.replace(/Report a Problem/ig, "");

                      name = name.replace(/^:\s*/, ""); 
                      name = name.replace(/^(?:I\s+|Artist\/\s*|Seller\s+|Type\s+|Unit\s+|-\s*)+/i, ""); 
                      name = name.replace(/(?:\s+I|\s+Artist\/|\s+Seller|\s+Type|\s+Unit|\s+-)+\s*$/i, ""); 
                      
                      name = name.replace(/\s{2,}/g, " ").trim();

                      if (!/^(?:Subtotal|Tax|TOTAL|Total|Billed To)/i.test(name) && name.length > 2) {
                          itemsArray.push(name + " - " + price);
                      }
                  }
              });
          }
      }

      if (itemsArray.length === 0) {
          itemsArray.push(rawItems.trim());
      }

      // --- 4. INDIVIDUAL ROW GENERATION & TAX SPLITTING MATH ---
      let taxFloat = tax !== "N/A" ? parseFloat(tax.replace(/[\$,]/g, '')) : 0;
      let n = itemsArray.length;
      
      let totalTaxCents = Math.round(taxFloat * 100);
      let baseTaxCents = Math.floor(totalTaxCents / n);
      let remainderCents = totalTaxCents % n;

      itemsArray.forEach((itemDesc, index) => {
          let itemSubFloat = 0;
          let priceMatch = itemDesc.match(/\$([0-9,]+\.\d{2})$/);
          
          if (priceMatch) {
              itemSubFloat = parseFloat(priceMatch[1].replace(/,/g, ''));
          } else if (n === 1) {
              itemSubFloat = subtotal !== "N/A" ? parseFloat(subtotal.replace(/[\$,]/g, '')) : 
                             (total !== "N/A" ? parseFloat(total.replace(/[\$,]/g, '')) - taxFloat : 0);
          }

          let itemTaxCents = baseTaxCents + (index < remainderCents ? 1 : 0);
          let itemTaxFloat = itemTaxCents / 100;
          let itemTotFloat = itemSubFloat + itemTaxFloat;

          // Apply positive/negative logic based on whether it is a refund or a purchase
          let sign = isRefund ? 1 : -1;
          let signedAmount = itemTotFloat * sign;
          
          // Format visually as -$X.XX or $X.XX
          let amountStr = signedAmount < 0 ? "-$" + Math.abs(signedAmount).toFixed(2) : "$" + signedAmount.toFixed(2);

          // Generate Unique Order ID (e.g., MLV5JQDYGW-1)
          let uniqueOrderId = orderId !== "N/A" ? `${orderId}-${index + 1}` : `UNKNOWN-${index + 1}`;

          // Append each item as its own independent row
          sheet.appendRow([
            dateStr,              
            itemDesc,             // Column B: Description 
            amountStr,            // Column C: Calculated Row Amount (Negative for purchases)
            "Apple Purchase",     // Column D: Institution
            uniqueOrderId,        // Column E: Account # (Now Unique ID)
            email,                // Column F: Account
            timeStr,              // Column G: Time
            docNo,                // Column H: Document No
            "$" + itemSubFloat.toFixed(2),  // Column I: Calculated Row Subtotal
            "$" + itemTaxFloat.toFixed(2),  // Column J: Calculated Row Tax
            paymentMethod         // Column K: Payment Method
          ]);
      });

    });
  });
}

Save and Run the script
The script parses through Gmail to:
1. Find all Apple receipts
2. Extract the individual purchases from each receipt
3. Create a new row in the “Apple Billing” spreadsheet for each individual purchase
4. Fills data columns for:
  - Date
  - Description
  - Amount
  - Account # (Order ID)
  - Account (Apple ID)
  - Time
  - Document No (Invoice Number)
  - Subtotal
  - Tax
  - Payment Method
5. The searchQuery (Line 12) for the initial run of the script should read:
  - ```
  const searchQuery = 'subject:"Your receipt from Apple." from:no_reply@email.apple.com';
```
- Subsequent daily runs should only should read the newest email receipts, so Line 12 should be changed to the code below, and run daily with a time-based trigger. This can be set up in the Apps Scripts extension
- ```
const searchQuery = 'subject:"Your receipt from Apple." from:no_reply@email.apple.com newer_than:2d';
```
Format the Apple Billing data for Tiller Community Solutions Import CSV Line Items workflow in a new tab
Export a CSV of the reformatted Apple Billing data, and import into Tiller using the CSV Line Items import tool
On the Tiller Transactions sheet, copy the unique Order IDs (Column E), over to the hidden Transaction ID column (column K) *see Note 1, below
I also AutoCat the original credit card transactions containing “Apple. com/us” or “Apple. com/bill” as “Apple” and set them as a Transfer, rather than an Expense, so they won’t show up in my spreadsheets.
I can then Categorize/AutoCat the new Apple receipt line items into their proper Categories, and those show up on my other sheets.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Notes

1. Tiller requires transactions to have an unique Transaction ID in Column K, in order to successfully run AutoCat on an item. Apple’s “Order ID” on each email receipt is unique, and a starting point. But, there are sometimes multiple purchases on one receipt. So, the script looks for multiple purchases on the same receipt, and adds a suffix (-1, -2, -3) to each Order ID. That gives all of the purchases a truly unique Order ID for Tiller.
1. Tax is a tricky on with these receipts. Because apps, movies, music, etc are all sold from different locations, states, countries… there is no consistent tax rate to divide proportionally among the given purchases on a receipt. Meaning, the tax on a $0.99 app could be $0.02, and the tax on a $0.99 song could be $0.06. So, my script just defaults to 3 items, divide the tax by 3. That’s me being a little lazy. I’m sure there’s a better long-term solution out there.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

jpfieber · May 31, 2026, 8:07pm

Very cool! I haven’t purchased a lot from Apple, but I recently did switch to a Macbook and have a couple purchases, so I tried it and it worked well, finding the two purchases I’ve made and adding them to my sheet! I ran it again and it reimported the same two purchases again as duplicates, so I asked Gemini to help with that, and add some logging, here is the resulting script that skips duplicates, and gives some feedback in the console log if you’re running it from there:

function parseAppleReceipts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Apple Billing") || SpreadsheetApp.getActiveSpreadsheet().insertSheet("Apple Billing");
  
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      "Date", "Description", "Amount", "Institution", "Account #", "Account", 
      "Time", "Document No", "Subtotal", "Tax", "Payment Method"
    ]);
  }
  
  // --- DUPLICATE PREVENTION: Load existing Document Numbers ---
  const existingDocNumbers = new Set();
  const lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    const docNoValues = sheet.getRange(2, 8, lastRow - 1, 1).getValues();
    docNoValues.forEach(row => {
      if (row[0] && row[0] !== "N/A") {
        existingDocNumbers.add(row[0].toString().trim());
      }
    });
  }
  console.log(`[Setup] Loaded ${existingDocNumbers.size} existing document numbers from the sheet.`);
  // -----------------------------------------------------------

  const searchQuery = 'subject:"Your receipt from Apple." from:no_reply@email.apple.com';
  const threads = GmailApp.search(searchQuery);
  const formatCutoffDate = new Date('2024-08-24T00:00:00');
  
  let importedCount = 0;
  let duplicateCount = 0;
  
  threads.forEach(thread => {
    const messages = thread.getMessages();
    
    messages.forEach(message => {
      const body = message.getPlainBody();
      const msgDate = message.getDate();
      const isNewFormat = msgDate >= formatCutoffDate;
      const isRefund = /refund/i.test(body);
      
      // --- 1. CORE DATA EXTRACTION ---
      const orderIdMatch = body.match(/ORDER ID:?\s*([A-Z0-9]+)/i);
      const docNoMatch = body.match(/DOCUMENT(?: NO\.?)?:?\s*(\d+)/i);
      const accountMatch = body.match(/([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,})/);
      
      const orderId = orderIdMatch ? orderIdMatch[1] : "N/A";
      const docNo = docNoMatch ? docNoMatch[1].trim() : "N/A";
      const email = accountMatch ? accountMatch[0] : "N/A";
      
      // --- DUPLICATE CHECK & LOGGING ---
      if (docNo !== "N/A" && existingDocNumbers.has(docNo)) {
        console.log(`[DUPLICATE] Order ID: ${orderId} | Doc No: ${docNo} - Already exists. Skipping.`);
        duplicateCount++;
        return; // Skip to the next message
      }
      
      // If it passes the check, it's a new import
      console.log(`[IMPORTING] Order ID: ${orderId} | Doc No: ${docNo} - New receipt found.`);
      importedCount++;
      
      const dateStr = Utilities.formatDate(msgDate, Session.getScriptTimeZone(), "MM/dd/yyyy");
      const timeStr = Utilities.formatDate(msgDate, Session.getScriptTimeZone(), "HH:mm:ss");
      
      // Robust Financial Extraction
      let subtotal = "N/A";
      let subMatches = [...body.matchAll(/(?:Subtotal|SUBTOTAL)[^\$]{0,150}?\$((?:\d{1,3},)?\d{1,3}\.\d{2})/g)];
      if (subMatches.length > 0) subtotal = "$" + subMatches[subMatches.length - 1][1];
      
      let tax = "N/A";
      let taxMatches = [...body.matchAll(/(?:Tax|TAX)[^\$]{0,150}?\$((?:\d{1,3},)?\d{1,3}\.\d{2})/g)];
      if (taxMatches.length > 0) tax = "$" + taxMatches[taxMatches.length - 1][1];
      
      let total = "N/A";
      let totalMatches = [...body.matchAll(/(?:TOTAL|Total|Amount Billed|Amount Charged)[^\$]{0,150}?\$((?:\d{1,3},)?\d{1,3}\.\d{2})/g)];
      
      if (totalMatches.length > 0) {
          total = "$" + totalMatches[totalMatches.length - 1][1];
      } else {
          let allPrices = [...body.matchAll(/\$((?:\d{1,3},)?\d{1,3}\.\d{2})/g)];
          if (allPrices.length > 0) {
              let lastFew = allPrices.slice(-4).map(m => parseFloat(m[1].replace(/,/g, '')));
              total = "$" + Math.max(...lastFew).toFixed(2);
          }
      }

      if (isRefund && total !== "N/A") {
        total = "-" + total; 
      }

      // Append row because it's a new receipt
      sheet.appendRow([
        dateStr, 
        `Apple Order ${orderId}`, 
        total, 
        "Apple", 
        "N/A", 
        email, 
        timeStr, 
        docNo, 
        subtotal, 
        tax, 
        "N/A"
      ]);
      
      // Add it to the Set so subsequent items in this same execution stream don't double-import
      if (docNo !== "N/A") {
        existingDocNumbers.add(docNo);
      }
    });
  });
  
  // Final summary log
  console.log(`[Summary] Execution finished. Imported: ${importedCount} | Duplicates Skipped: ${duplicateCount}`);
}

stephensandlin · May 31, 2026, 8:14pm

That’s a great idea. Up there in Step 4.5 I had originally addressed the issue by changing the query to:

const searchQuery = 'subject:"Your receipt from Apple." from:no_reply@email.apple.com newer_than:2d';

That “newer_than:2d” keeps it from looking for old emails, but I think your solution is the long-term fix! Thanks

jpfieber · May 31, 2026, 8:24pm

It uses the document number to check if it exists, so the only way it would be a problem is if multiple items can have the same document number. Is it possible to purchase multiple items at once, and if so, do they get the same document number? If so, that would be a problem, otherwise, it should work well!

stephensandlin · June 1, 2026, 1:09pm

Yes the document number can be the same for multiple purchases. I believe the receipt is a collection of the day’s purchases (as seen in the image from my post). However, if one of that day’s purchases has already been imported, then all of them have been imported. There wouldn’t ever be a second email, that repeats a document number.

Topic		Replies	Views
Apple Card easy import with Google Apps Script gSheets Templates	4	380	February 15, 2025
Anyone else tracking receipts in your Tiller spreadsheet? Conversation	20	2227	July 28, 2025
Import/remap CSV gSheets Templates amazon , scripts , paypal , venmo , tcs-csv	12	423	August 27, 2025
New Amazon import process just released on Google Marketplace Google Sheets amazon , scripts	68	1118	June 15, 2026
Docs: Import CSV Line Items gSheets Templates amazon , spreadsheet-utility , mint , tcs-addon , tcs-csv	16	27363	April 21, 2020

Apple Billing Receipt Parsing (not Apple card) for Google Sheet Apps Script

Related topics