Gmail to Google Sheets with n8n: Log Receipt Emails

Use this Gmail to Google Sheets workflow in n8n to log receipt emails: select a narrow set of messages, map their IDs and headers, then append or update rows using the message ID as the matching key. This creates a message log, not an expense extractor: it records sender, subject and received time, without reading attachments or inferring an invoice amount.

business automation — visual checklist

Try the free example before connecting your inbox

Download the no-network practice workflow (JSON) and download the Google Sheets header row (CSV). The workflow contains a manual trigger and two Code nodes with fictional messages. It makes no Google or AI request and contains no credentials. Import it into a new workflow using the n8n import instructions.

Run the unchanged practice workflow. Map message metadata should return two records: demo-receipt-001 and demo-receipt-002. A third input repeats the first message ID and is dropped within that execution. The two unique messages deliberately share a thread ID, showing why thread ID is the wrong key for a message-by-message log.

Prerequisites for the Gmail to Google Sheets workflow

  • An existing n8n workspace with permission to create workflows.
  • A Gmail credential authorized to read the selected mailbox, and a Google Sheets credential with access to your destination file. Use the Google credential instructions; self-hosted n8n uses custom OAuth setup rather than n8n Cloud’s managed sign-in.
  • A private test spreadsheet with a tab named Receipts and these exact headings in A1:D1: message_id, from, subject, received_at_utc.
  • One real, non-sensitive test email you are permitted to process. Work manually first; do not add a schedule until the repeat-run check passes.

Use one mailbox per log in this starter design. For several mailboxes, create a composite key that includes the mailbox identifier. Keep the destination limited to people who need it; our Google Drive sharing audit helps review access to a spreadsheet.

1. Select a small, inspectable message set

  1. Create a new workflow with a Manual Trigger and Gmail node. Choose Resource: Message and Operation: Get Many. Select your Gmail credential.
  2. Turn Return All off and start with Limit 5. Turn Simplify off for the raw response shape used by the mapping code below.
  3. Filter to your receipt label, or enter a narrow Gmail search you have already checked. For example, replace the fictional sender in from:billing@example.com subject:receipt with your real test sender.
  4. Set Read Status to Unread and read emails if the log must include messages you opened already. The documented default is unread only; opening a receipt should not silently remove it from this selection.
  5. Run the Gmail node and inspect one item. It must contain id and payload.headers. If your node version returns only IDs, add Gmail Get for each message ID before mapping.

These selection controls are documented under n8n Gmail message operations. For a label-only rule in Gmail, see our receipt and invoice filter guide.

2. Map raw metadata into four named fields

Add a Code node, choose JavaScript and Run Once for All Items, and paste this code. It uses only the message ID, two headers and the Gmail internal timestamp. It does not include the email body, attachments or credential data.

const seen = new Set();
const out = [];
for (const [index, item] of $input.all().entries()) {
  const message = item.json;
  const id = String(message.id || '').trim();
  if (!id) throw new Error('A Gmail message ID is required; inspect the input.');
  const headers = message.payload?.headers;
  if (!Array.isArray(headers)) {
    throw new Error('Expected raw Gmail payload.headers. Turn Simplify off, or Get each message by ID first.');
  }
  const header = name => String(headers.find(h => String(h.name).toLowerCase() === name)?.value || '');
  let receivedAt = '';
  if (message.internalDate !== undefined && message.internalDate !== null && message.internalDate !== '') {
    const value = message.internalDate;
    const text = typeof value === 'string' || typeof value === 'number' ? String(value) : '';
    if (!/^\d+$/.test(text)) throw new Error('Invalid Gmail internalDate for ' + id);
    const milliseconds = Number(text);
    if (!Number.isFinite(milliseconds) || milliseconds < 0) throw new Error('Invalid Gmail internalDate for ' + id);
    const date = new Date(milliseconds);
    if (Number.isNaN(date.getTime())) throw new Error('Gmail internalDate is outside the supported date range for ' + id);
    receivedAt = date.toISOString();
  }
  if (seen.has(id)) continue;
  seen.add(id);
  out.push({
    json: {
      message_id: id,
      from: header('from'),
      subject: header('subject'),
      received_at_utc: receivedAt
    },
    pairedItem: { item: index }
  });
}
return out;

Google defines id as the immutable message ID and internalDate as an epoch-millisecond timestamp used for mailbox ordering. That timestamp is preferable to trusting a sender’s Date header, though imported mail can differ. The mapper leaves the received-time field empty when it is absent instead of inventing a date. See the Gmail message fields. A missing ID, missing raw header structure or invalid supplied timestamp stops this mapping step for inspection.

3. Append or update the matching row in Sheets

  1. Add Google Sheets after the Code node. Select Resource: Sheet Within Document and Operation: Append or Update Row, then choose your test document and Receipts tab.
  2. Use manual column mapping. Choose message_id as the matching column and map each heading to the corresponding current input field, such as {{ $json.message_id }} and {{ $json.subject }}. Verify the other two mappings before running.
  3. Under Cell Format, choose Let n8n format to keep string inputs as strings. Inspect a harmless subject such as =1+1 in a test row: it should stay text, not become a formula. The node labels can vary by version.
  4. Keep one consistent header row, no duplicate message IDs and no gaps splitting the table. Run one item first and compare all four saved values with the Code output.

The Google Sheets node reference describes append/update and column mapping. Reusing a message ID lets a later sequential run update its row instead of always appending. A sheet lookup is not an atomic uniqueness constraint: two overlapping writers can still race. Keep this starter workflow to one writer and inspect repeats before scheduling it.

Run the checks that matter

CheckExpected resultWhat it establishes
Run the fictional workflowThree inputs become two rows; IDs remain distinctLocal mapping and in-run duplicate handling only
Run one real test emailOne saved row matches its ID, sender, subject and UTC timeThis credential, mailbox and destination work for that input
Run the same real selection again, sequentiallyThe same key updates; no extra row for that messageRepeat behavior for this single-writer test
Try a harmless subject =1+1 in the test copyText remains textCell formatting does not evaluate the test string
Select an opened test receiptIt appears when read+unread is chosenRead status does not accidentally exclude it

Fix empty results, duplicate rows and failed writes

  • No messages: check the authorized mailbox, label, search and Read Status. Test the search in Gmail before broadening it.
  • payload.headers is missing: check Simplify and the actual node output. Fetch the full message by ID if necessary; do not guess field names from another version.
  • Duplicate rows on a repeat: check that you used Append or Update Row, selected message_id as the key, and have one row per key. Append Row and the API append endpoint always append; neither alone deduplicates.
  • Headers are not detected: confirm the four headings are in row 1 and reselect the tab or refresh the node’s column mapping after changing headers.
  • A write times out: inspect the sheet for the message ID before retrying. A timeout does not establish that no row was saved. Add a separate n8n error workflow before moving to automatic runs.

Limits and alternative outputs

The practice JSON has been checked by executing its Code scripts locally with fictional data, including repeated IDs and invalid inputs. It has not been run against a Gmail account or a Google Sheet here. Your OAuth permissions, node version and destination must pass the real-message checks above.

Limit 5 is a learning cap, not a complete backfill or delivery guarantee. If more eligible messages arrive between runs, the same small selection can leave older messages unprocessed. Plan the capture trigger or a bounded backfill separately and compare message counts before making the workflow automatic.

If you use the Sheets API directly instead of this node, its append method adds rows and does not deduplicate; valueInputOption=RAW stores submitted values without parsing. Google defines that behavior in ValueInputOption. A lookup/update or durable uniqueness design is still required for repeats.

A message log does not save attachment files or verify that a receipt is genuine. If you need only a manual attachment save, the separate Dropbox for Gmail documentation describes its add-on; it is a different task from this spreadsheet log. To plan a larger source, review and output process, continue with the free n8n orchestration example.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top

Privacy and cookie settings