Order confirmation emails rarely use a consistent subject format, but the order number itself usually follows a predictable pattern like ORD-12345 or A98213. This tutorial shows how to pull that pattern out with a regular expression and route it into its own column.
The script searches a Gmail label for order emails, runs a regex against each subject line, and writes the matched order ID next to the sender and date in a dedicated sheet.
Subjects that do not match the expected pattern are not silently dropped; they land in the same sheet with an empty order ID column so you can spot formatting changes from a vendor.
This pattern generalizes well beyond order IDs. Once you understand how the regex and capture group work, you can swap in any pattern you need to lift structured data out of unstructured subject text.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Resource | Name / value | Purpose |
|---|---|---|
| Gmail label | orders | Filters vendor order emails |
| Sheet | Order Log | Columns Order ID, Subject, From, Date |
| Regex | Order #(\d+) | Extracts order id from subject |
What It Does
The script pulls every message under a Gmail label, applies a regular expression against the subject line, and captures the order ID into its own column alongside the raw subject.
Messages are marked with a processed label after being parsed once, so re-running the script does not create duplicate spreadsheet rows for the same order confirmation.
Prerequisites
You need a Gmail label already applied to order confirmation emails, either manually or through an existing Gmail filter, so the search query has something specific to target.
Look at a handful of real subject lines from your vendor first, since the regex pattern in this script assumes order IDs start with a short letter prefix followed by digits.
Walkthrough
Create the Order Log sheet with four header columns, then paste the parseOrderSubjects function into the bound script editor and adjust the ORDER_ID_PATTERN constant to match your vendor's format.
Run the function manually against a small batch of test emails first, checking that the captured order ID column lines up with what you would expect a human to read off the subject.
Once the pattern is confirmed, add a time-driven trigger so new order emails get parsed automatically without anyone opening the script editor again.
Edge Cases
Some vendors send a follow-up subject like RE: Order ORD-12345 shipped, so the regex is written to search anywhere in the string rather than anchoring to the start.
If a subject contains two numbers that could plausibly be an order ID, the script always takes the first regex match, so unusual formats may need a more specific pattern.
Testing
Send yourself three test emails with subjects that vary slightly in wording but keep the same order ID format, then confirm all three rows capture the identical ID correctly.
Add one deliberately malformed subject without any order number and verify the row still appears with a blank order ID rather than causing the script to throw an error.
Hardening
Wrap the regex exec call in a null check before reading the capture group, since a subject with zero matches returns null and calling a property on it would stop the script.
Log any subject that fails to match into a separate Needs Review sheet so a vendor's subtle formatting change gets noticed quickly instead of silently producing empty cells.
Variations
If order confirmations arrive from several vendors with different ID formats, keep a small lookup table of sender domain to regex pattern and pick the right pattern per message.
Combine this script with the mail merge tutorial to automatically look up each parsed order ID in an external system before sending a status update back to the customer.
parseOrderSubjects.gs
parseOrderSubjects pulls every unlabeled order email, runs a regex against the subject to capture an order ID, and writes both the ID and the raw subject to the Order Log sheet.
// Extract order IDs from Gmail subject lines with regex
function parseOrderSubjects() {
var LABEL_NAME = 'orders';
var DONE_LABEL = 'orders-parsed';
var SHEET_NAME = 'Order Log';
var ORDER_ID_PATTERN = /\b([A-Z]{2,4}-?\d{4,8})\b/;
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
var doneLabel = GmailApp.getUserLabelByName(DONE_LABEL) || GmailApp.createLabel(DONE_LABEL);
var threads = GmailApp.search('label:' + LABEL_NAME + ' -label:' + DONE_LABEL, 0, 50);
for (var i = 0; i < threads.length; i++) {
var messages = threads[i].getMessages();
for (var j = 0; j < messages.length; j++) {
var subject = messages[j].getSubject();
var match = ORDER_ID_PATTERN.exec(subject);
var orderId = match ? match[1] : '';
sheet.appendRow([
orderId,
subject,
messages[j].getFrom(),
messages[j].getDate()
]);
}
threads[i].addLabel(doneLabel);
}
}- Line 6: The pattern looks for two to four uppercase letters, an optional dash, and four to eight digits, matched anywhere in the subject.
- Line 10: Searching with both the target label and an exclusion for the done label means already-parsed threads are skipped automatically.
- Line 16: exec returns null when no match is found, which is why the very next line checks match before reading a capture group.
- Line 17: The ternary falls back to an empty string instead of a match on failure, so a missing order ID never breaks appendRow.
- Line 25: Adding the done label after parsing is what keeps a scheduled re-run from creating duplicate rows for the same order email.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: parse gmail subject to sheet
- 1Gmail label applied to order confirmation emails
- 2Order Log sheet created with four header columns
- 3Regex pattern tested against several real subject lines
- 4Processed label created for marking parsed messages
- 5Function run manually against existing matching emails
- 6Malformed subject tested to confirm graceful handling
- 7Time-driven trigger added for ongoing parsing
Frequently asked questions
Matching subjects will start showing a blank order ID column, which is your signal to review the Needs Review sheet suggested in the hardening section and update the pattern.
Yes, add more capture groups to ORDER_ID_PATTERN and push each group into its own column when appendRow is called.
Generally yes, since the pattern only targets the letter-and-digit order ID segment and ignores surrounding characters.
A label lets you combine several vendor senders and forwarding rules under one consistent Gmail filter instead of listing addresses in the script itself.
The example pattern expects uppercase letters in the ID prefix; add the case-insensitive flag if a vendor sometimes sends lowercase order codes.
Yes, paste a few sample subjects and your pattern into any JavaScript regex tester to confirm the capture group behaves as expected before wiring it into Gmail.