Record where incoming emails really came from

Every six hours, the inbound mail matching a Gmail search you choose has its headers read, and one row per message is appended to your spreadsheet: what the receiving server logged for SPF, DKIM and DMARC, what the From and Return-Path headers say, which relay addresses appear, and where each of them resolves to.

It reports and does not conclude: no score, no rating, no verdict. Where a header records nothing, the row says so rather than reporting a failure, and a relay address in a data centre is flagged as exactly that, since much ordinary mail travels that way. Only headers are read; the body never reaches the spreadsheet or the AI. The last column is a short AI note describing the fields in that row; if it strays into judging the message it is set aside and the row says so. Each message is labelled 'Headers logged' once written, so nothing is logged twice.

Before the first run you need a spreadsheet tab headed for the columns each row fills.

Run every 6 hours.Run every 6 hours.

Set mailSearch to blank.Set mailSearch to blank.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set tabName to blank.Set tabName to blank.

Set checkedLabel to Headers logged.Set checkedLabel to Headers logged.

Set maxMessages to 10.Set maxMessages to 10.

Set maxRelayAddresses to 10.Set maxRelayAddresses to 10.

Get today's date and save it as checkedOn.Get today's date and save it as checkedOn.

Set searchQuery to mailSearch -label:@checkedLabel.Set searchQuery to mailSearch -label:@checkedLabel.

GmailList emails matching searchQuery. Save it as inbox.List emails matching searchQuery. Save it as inbox.

Set messages to an empty list.Set messages to an empty list.

If inbox.emails isn't emptyIf inbox.emails isn't empty

Yes

Change messages to inbox.emails.Change messages to inbox.emails.

No

No steps

Take the first maxMessages multiplied by 1 items of messages and save it as checkedMessages.Take the first maxMessages multiplied by 1 items of messages and save it as checkedMessages.

If checkedMessages is emptyIf checkedMessages is empty

Yes

Show in the output: No mail matched that search that has not already been logged..Show in the output: No mail matched that search that has not already been logged..

No

No steps

For each message in checkedMessages:For each message in checkedMessages:

Set messageId to blank.Set messageId to blank.

If message.id isn't empty and message.id isn't blankIf message.id isn't empty and message.id isn't blank

Yes

Trim message.id and save it as messageId.Trim message.id and save it as messageId.

No

No steps

If messageId is emptyIf messageId is empty

Yes

Show in the output: Skipped a message that arrived without an id..Show in the output: Skipped a message that arrived without an id..

Skip to the next item.Skip to the next item.

No

No steps

If a step below fails, save the error as headerErrorIf a step below fails, save the error as headerError

Try

GmailGet the sender, subject and date of email messageId. Save it as headers.Get the sender, subject and date of email messageId. Save it as headers.

Set fromLine to no From header.Set fromLine to no From header.

If headers.From isn't empty and headers.From isn't blankIf headers.From isn't empty and headers.From isn't blank

Yes

Trim headers.From and save it as fromLine.Trim headers.From and save it as fromLine.

No

No steps

Set subjectLine to no Subject header.Set subjectLine to no Subject header.

If headers.Subject isn't empty and headers.Subject isn't blankIf headers.Subject isn't empty and headers.Subject isn't blank

Yes

Trim headers.Subject and save it as subjectLine.Trim headers.Subject and save it as subjectLine.

No

No steps

Set dateLine to no Date header.Set dateLine to no Date header.

If headers.Date isn't empty and headers.Date isn't blankIf headers.Date isn't empty and headers.Date isn't blank

Yes

Trim headers.Date and save it as dateLine.Trim headers.Date and save it as dateLine.

No

No steps

Set returnPathLine to no Return-Path header.Set returnPathLine to no Return-Path header.

If headers["Return-Path"] isn't empty and headers["Return-Path"] isn't blankIf headers["Return-Path"] isn't empty and headers["Return-Path"] isn't blank

Yes

Trim headers["Return-Path"] and save it as returnPathLine.Trim headers["Return-Path"] and save it as returnPathLine.

No

No steps

Set authLine to blank.Set authLine to blank.

If headers["Authentication-Results"] isn't empty and…If headers["Authentication-Results"] isn't empty and…

Yes

Trim headers["Authentication-Results"] and save it as authLine.Trim headers["Authentication-Results"] and save it as authLine.

No

No steps

Set receivedLine to blank.Set receivedLine to blank.

If headers.Received isn't empty and headers.Received isn't blankIf headers.Received isn't empty and headers.Received isn't blank

Yes

Trim headers.Received and save it as receivedLine.Trim headers.Received and save it as receivedLine.

No

No steps

Set spfHeaderLine to blank.Set spfHeaderLine to blank.

If headers["Received-SPF"] isn't empty and headers["Received-SPF"] isn't blankIf headers["Received-SPF"] isn't empty and headers["Received-SPF"] isn't blank

Yes

Trim headers["Received-SPF"] and save it as spfHeaderLine.Trim headers["Received-SPF"] and save it as spfHeaderLine.

No

No steps

Set originIpLine to blank.Set originIpLine to blank.

If headers["X-Originating-IP"] isn't empty and headers["X-Originating-IP"] isn't…If headers["X-Originating-IP"] isn't empty and headers["X-Originating-IP"] isn't…

Yes

Trim headers["X-Originating-IP"] and save it as originIpLine.Trim headers["X-Originating-IP"] and save it as originIpLine.

No

No steps

Set authText to authLine spfHeaderLine.Set authText to authLine spfHeaderLine.

Set spfResult to no SPF result recorded in these headers.Set spfResult to no SPF result recorded in these headers.

Extract the first match from authText in lowercase and save it as spfResultMatch.Extract the first match from authText in lowercase and save it as spfResultMatch.

If spfResultMatch isn't emptyIf spfResultMatch isn't empty

Yes

Change spfResult to spfResultMatch.Change spfResult to spfResultMatch.

No

No steps

Set dkimResult to no DKIM result recorded in these headers.Set dkimResult to no DKIM result recorded in these headers.

Extract the first match from authText in lowercase and save it as dkimResultMatch.Extract the first match from authText in lowercase and save it as dkimResultMatch.

If dkimResultMatch isn't emptyIf dkimResultMatch isn't empty

Yes

Change dkimResult to dkimResultMatch.Change dkimResult to dkimResultMatch.

No

No steps

Set dmarcResult to no DMARC result recorded in these headers.Set dmarcResult to no DMARC result recorded in these headers.

Extract the first match from authText in lowercase and save it as dmarcResultMatch.Extract the first match from authText in lowercase and save it as dmarcResultMatch.

If dmarcResultMatch isn't emptyIf dmarcResultMatch isn't empty

Yes

Change dmarcResult to dmarcResultMatch.Change dmarcResult to dmarcResultMatch.

No

No steps

Set scanText to receivedLineSet scanText to receivedLine authText originIpLine.

Extract all matches from scanText and save it as foundAddresses.Extract all matches from scanText and save it as foundAddresses.

Take the first maxRelayAddresses multiplied by 1 items of foundAddresses with duplicates removed and save it as relayAddresses.Take the first maxRelayAddresses multiplied by 1 items of foundAddresses with duplicates removed and save it as relayAddresses.

Set relayList to no relay address appears in these headers.Set relayList to no relay address appears in these headers.

Set relayPlaces to no relay addresses to look up.Set relayPlaces to no relay addresses to look up.

Set locatedRows to an empty list.Set locatedRows to an empty list.

If relayAddresses isn't emptyIf relayAddresses isn't empty

Yes

Join relayAddresses with , and save it as relayList.Join relayAddresses with , and save it as relayList.

If a step below fails, save the error as lookupErrorIf a step below fails, save the error as lookupError

Try

ip-api.comLook up several IP addresses. Save it as located.Look up several IP addresses. Save it as located.

If located.results isn't emptyIf located.results isn't empty

Yes

Change locatedRows to located.results.Change locatedRows to located.results.

No

No steps

On error

Change relayPlaces to the address lookup step did not answer, so these addresses were not looked up.Change relayPlaces to the address lookup step did not answer, so these addresses were not looked up.

No

No steps

Set placeLines to an empty list.Set placeLines to an empty list.

For each relay in locatedRows:For each relay in locatedRows:

Set relayQuery to an address. Set relayQuery to an address.

If relay.query isn't empty If relay.query isn't empty

Yes

Trim relay.query and save it as relayQuery.Trim relay.query and save it as relayQuery.

No

No steps

Set relayPlace to not located. Set relayPlace to not located.

If relay.located is yes If relay.located is yes

Yes

Set placeLabel to no place on record.Set placeLabel to no place on record.

If relay.location_label isn't empty and relay.location_label isn't blankIf relay.location_label isn't empty and relay.location_label isn't blank

Yes

Trim relay.location_label and save it as placeLabel.Trim relay.location_label and save it as placeLabel.

No

No steps

Set ispName to no provider on record.Set ispName to no provider on record.

If relay.isp isn't empty and relay.isp isn't blankIf relay.isp isn't empty and relay.isp isn't blank

Yes

Trim relay.isp and save it as ispName.Trim relay.isp and save it as ispName.

No

No steps

Change relayPlace to placeLabel, run by ispName.Change relayPlace to placeLabel, run by ispName.

No

Set notLocatedReason to no reason given.Set notLocatedReason to no reason given.

If relay.reason isn't empty and relay.reason isn't blankIf relay.reason isn't empty and relay.reason isn't blank

Yes

Trim relay.reason and save it as notLocatedReason.Trim relay.reason and save it as notLocatedReason.

No

No steps

Change relayPlace to not located (notLocatedReason).Change relayPlace to not located (notLocatedReason).

If relay.hosting is yes If relay.hosting is yes

Yes

Change relayPlace to relayPlace - a data centre address, which is what mail sent through a cloud or bulk provider looks like.Change relayPlace to relayPlace - a data centre address, which is what mail sent through a cloud or bulk provider looks like.

No

No steps

Add relayQuery: relayPlace to the end of placeLines and save it as placeLines. Add relayQuery: relayPlace to the end of placeLines and save it as placeLines.

If placeLines isn't emptyIf placeLines isn't empty

Yes

Join placeLines with a new line and save it as relayPlaces.Join placeLines with a new line and save it as relayPlaces.

No

No steps

Extract the first match from fromLine in lowercase and save it as fromDomain.Extract the first match from fromLine in lowercase and save it as fromDomain.

Extract the first match from returnPathLine in lowercase and save it as pathDomain.Extract the first match from returnPathLine in lowercase and save it as pathDomain.

Set envelopeNote to A domain could not be read from both the From header and the Return-Path header..Set envelopeNote to A domain could not be read from both the From header and the Return-Path header..

If fromDomain isn't empty and pathDomain isn't emptyIf fromDomain isn't empty and pathDomain isn't empty

Yes

If fromDomain is pathDomainIf fromDomain is pathDomain

Yes

Change envelopeNote to The From address and the Return-Path are on the same domain (fromDomain).Change envelopeNote to The From address and the Return-Path are on the same domain (fromDomain).

No

Change envelopeNote to The From address is on fromDomain and the Return-Path is on pathDomain. These differ on most mailing lists, newsletters, ticketing systems and forwarded mail.Change envelopeNote to The From address is on fromDomain and the Return-Path is on pathDomain. These differ on most mailing lists, newsletters, ticketing systems and forwarded mail.

No

No steps

Set factsSender to From: fromLineSet factsSender to From: fromLine Return-Path: returnPathLine envelopeNote.

Set factsChecks to SPF: spfResultSet factsChecks to SPF: spfResult DKIM: dkimResult DMARC: dmarcResult.

Set factsRelays to Relay addresses named in the headers: relayListSet factsRelays to Relay addresses named in the headers: relayList Where those addresses resolve: relayPlaces.

Set headerFacts to factsSenderSet headerFacts to factsSender factsChecks factsRelays.

Set notePrompt to You are writing one short note for a log of email header fields.Set notePrompt to You are writing one short note for a log of email header fields. Describe only what the fields below record, in at most three sentences of plain English. Say when a field records nothing. Do not decide anything about the message, do not say what it is, and do not say what should be done about it. Do not mention anything that is not listed below. Relay addresses in other countries are ordinary: mailing lists, forwarding services and cloud providers all route mail through them. Say where an address resolves without implying anything about it. Header fields: headerFacts.

Set replyText to blank.Set replyText to blank.

If a step below fails, save the error as noteErrorIf a step below fails, save the error as noteError

Try

ClaudeAsk Claude: notePrompt. Save it as modelReply.Ask Claude: notePrompt. Save it as modelReply. Maximum response length: 300

If modelReply.response_text isn't emptyIf modelReply.response_text isn't empty

Yes

Trim modelReply.response_text and save it as replyText.Trim modelReply.response_text and save it as replyText.

No

No steps

On error

Show in the output: The written note step did not answer for message messageId.Show in the output: The written note step did not answer for message messageId.

Set note to The written note step returned nothing, so this row carries the header fields on their own..Set note to The written note step returned nothing, so this row carries the header fields on their own..

If replyText isn't emptyIf replyText isn't empty

Yes

Change note to replyText.Change note to replyText.

No

No steps

Match keywords in replyText in lowercase and save it as noteWentBeyondTheFields.Match keywords in replyText in lowercase and save it as noteWentBeyondTheFields.

If noteWentBeyondTheFields.is_match is yesIf noteWentBeyondTheFields.is_match is yes

Yes

Change note to The written note was set aside because it went beyond describing the fields. Read the header fields in this row instead..Change note to The written note was set aside because it went beyond describing the fields. Read the header fields in this row instead..

No

No steps

Google SheetsAdd a row to tabName in spreadsheetUrl.Add a row to tabName in spreadsheetUrl.

GmailAdd label checkedLabel to email messageId.Add label checkedLabel to email messageId.

On error

Show in the output: Could not read the headers for message messageId: headerError as text.Show in the output: Could not read the headers for message messageId: headerError as text.