Your nightly job pulls vendor receipts out of Gmail into a sheet. Run it twice and you get every receipt twice. getUnprocessedThreads(query, labelName) runs GmailApp.search with -label:"labelName" appended, so threads you already handled are filtered out by Gmail itself. It pages in batches because a single GmailApp.search call returns at most 500 threads. markThreadsProcessed(threads, labelName) gets or creates the label and applies it, and you call it only after the write succeeds. The label works as a receipt: no label means the thread gets picked up again on the next run.
When to use
- Importing receipts, form notifications, or alerts from Gmail into a sheet on a trigger
- Any Gmail job that has to be safe to re-run (it crashed halfway, someone clicked Run again)
- Backfills where the matching threads may number more than 500
How to use this snippet
/**
* Threads matching `query` that do NOT carry `labelName` yet.
* Pages through GmailApp.search (max 500 threads per call).
*
* @param {string} query Gmail search, e.g. 'from:billing@vendor.com newer_than:30d'
* @param {string} labelName receipt label, e.g. 'Imported/Receipts'
* @param {number=} maxThreads optional safety cap for one run (default 2000)
* @return {GmailApp.GmailThread[]}
*/
function getUnprocessedThreads(query, labelName, maxThreads) {
var PAGE = 500;
var limit = maxThreads == null ? 2000 : Number(maxThreads);
var q = (query ? query + ' ' : '') + '-label:"' + labelName + '"';
var threads = [];
for (var start = 0; threads.length < limit; start += PAGE) {
var size = Math.min(PAGE, limit - threads.length);
var batch = GmailApp.search(q, start, size);
threads = threads.concat(batch);
if (batch.length < size) break; // last page
}
return threads;
}
/**
* Apply the receipt label. Creates it the first time.
* Call ONLY after the downstream write succeeded.
*
* @param {GmailApp.GmailThread[]} threads
* @param {string} labelName
* @return {number} threads labelled
*/
function markThreadsProcessed(threads, labelName) {
if (!threads || !threads.length) return 0;
var label = GmailApp.getUserLabelByName(labelName) || GmailApp.createLabel(labelName);
for (var i = 0; i < threads.length; i += 100) {
label.addToThreads(threads.slice(i, i + 100)); // addToThreads takes max 100
}
return threads.length;
}
Example
var RECEIPT_LABEL = 'Imported/Receipts';
function importVendorReceipts() {
var threads = getUnprocessedThreads('from:billing@vendor.com has:attachment', RECEIPT_LABEL, 300);
if (!threads.length) return;
var rows = threads.map(function (t) {
var m = t.getMessages()[0];
return [t.getId(), m.getDate(), m.getFrom(), m.getSubject()];
});
var sheet = SpreadsheetApp.getActive().getSheetByName('Receipts');
sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
SpreadsheetApp.flush(); // make sure the write landed before we stamp the receipt
markThreadsProcessed(threads, RECEIPT_LABEL); // not reached if setValues threw
}
Tips:
- Order matters: write, then label. Label first and a failed write silently drops those threads forever.
- Fetch everything for the run first, then label. Labelling between pages changes what
-label:matches and shifts thestartoffsets. - Keep
maxThreadssmall enough for one run (a few hundred). Unlabelled leftovers just get picked up by the next trigger. - Store the thread id in the sheet too, so a write that succeeded right before a crash (label never applied) can be de-duped on the next run.
- Use one dedicated receipt label per job and keep its name simple (no spaces). Search for it in Gmail once by hand to confirm
-label:matches what you expect.
Tip: NitroGAS Co-Pilot can wrap your existing Gmail import loop with getUnprocessedThreads / markThreadsProcessed and move the label call after your setValues. Paste the function and say which label name to use.
Happy Coding!
