Save matching emails to a spreadsheet

Whenever new mail arrives in Gmail, the workflow looks for the messages your search matches, such as everything from a billing address, and writes one row per message into your spreadsheet. Each row records when the message was sent, who sent it, who it went to, the subject, an excerpt of the body, the Gmail message id and a link that opens the message in Gmail.

Nothing is saved twice. Each message is labelled 'Saved to sheet' the moment it is picked up and the search excludes that label, so a message already saved is never fetched again. The label goes on before the row is written on purpose: if the sheet cannot be written to, the label comes off again, the message is retried next run and the run ends in failure so you can see it. Several matching messages landing at once are all saved, and a backlog is worked through a batch at a time.

Before the first run you need a spreadsheet tab with headings in that column order, and a Gmail search that picks out the mail worth saving.

GmailWhen an email arrives.When an email arrives.

Set emailSearch to blank.Set emailSearch to blank.

Set spreadsheetUrl to blank.Set spreadsheetUrl to blank.

Set sheetTabName to Emails.Set sheetTabName to Emails.

Set savedLabel to Saved to sheet.Set savedLabel to Saved to sheet.

Set maxEmailsPerRun to 10.Set maxEmailsPerRun to 10.

Set excerptLength to 300.Set excerptLength to 300.

Set gmailAccountIndex to 0.Set gmailAccountIndex to 0.

Set emailLimit to maxEmailsPerRun multiplied by 1.Set emailLimit to maxEmailsPerRun multiplied by 1.

If not emailLimit is greater than or equal to 1 and emailLimit is less than or…If not emailLimit is greater than or equal to 1 and emailLimit is less than or…

Yes

Stop and report a problem: Set 'maxEmailsPerRun' to a whole number from 1 to 500: it caps how many emails one run saves.Stop and report a problem: Set 'maxEmailsPerRun' to a whole number from 1 to 500: it caps how many emails one run saves.

No

No steps

Set excerptLimit to excerptLength multiplied by 1.Set excerptLimit to excerptLength multiplied by 1.

If not excerptLimit is greater than or equal to 0 and excerptLimit is less than…If not excerptLimit is greater than or equal to 0 and excerptLimit is less than…

Yes

Stop and report a problem: Set 'excerptLength' to a whole number from 0 to 50000: it is how many characters of each message body go into the Excerpt column, and 0 leaves the column empty.Stop and report a problem: Set 'excerptLength' to a whole number from 0 to 50000: it is how many characters of each message body go into the Excerpt column, and 0 leaves the column empty.

No

No steps

Set accountIndex to gmailAccountIndex multiplied by 1.Set accountIndex to gmailAccountIndex multiplied by 1.

If not accountIndex is greater than or equal to 0 and accountIndex is less than…If not accountIndex is greater than or equal to 0 and accountIndex is less than…

Yes

Show in the output: 'gmailAccountIndex' is not a whole number from 0 up, so the links in the sheet will open in the first Google account you are signed in to..Show in the output: 'gmailAccountIndex' is not a whole number from 0 up, so the links in the sheet will open in the first Google account you are signed in to..

Change accountIndex to 0.Change accountIndex to 0.

No

No steps

Set searchQuery to emailSearch -label:@savedLabel.Set searchQuery to emailSearch -label:@savedLabel.

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

Set matchCount to 0.Set matchCount to 0.

If a step below fails, save the error as listErrorIf a step below fails, save the error as listError

Try

Count the items in found.emails and save it as matchCount.Count the items in found.emails and save it as matchCount.

On error

Stop and report a problem: Gmail did not return a readable list of messages for the search '@emailSearch', so nothing was saved this run. Details: listError as text.Stop and report a problem: Gmail did not return a readable list of messages for the search '@emailSearch', so nothing was saved this run. Details: listError as text.

Set processedCount to 0.Set processedCount to 0.

Set savedCount to 0.Set savedCount to 0.

Set failedCount to 0.Set failedCount to 0.

For each message in found.emails:For each message in found.emails:

If processedCount is greater than or equal to emailLimitIf processedCount is greater than or equal to emailLimit

Yes

Stop repeating.Stop repeating.

No

No steps

If a step below fails, save the error as messageErrorIf a step below fails, save the error as messageError

Try

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

If messageId is emptyIf messageId is empty

Yes

Add 1 to failedCount.Add 1 to failedCount.

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

No

GmailAdd label savedLabel to email messageId.Add label savedLabel to email messageId.

Add 1 to processedCount.Add 1 to processedCount.

Set subject to (no subject).Set subject to (no subject).

If a step below fails, save the error as missingFieldIf a step below fails, save the error as missingField

Try

Trim message.subject and save it as fieldValue.Trim message.subject and save it as fieldValue.

If fieldValue isn't emptyIf fieldValue isn't empty

Yes

Change subject to fieldValue.Change subject to fieldValue.

No

No steps

On error

No steps

Set sender to (unknown sender).Set sender to (unknown sender).

If a step below fails, save the error as missingFieldIf a step below fails, save the error as missingField

Try

Trim message.from and save it as fieldValue.Trim message.from and save it as fieldValue.

If fieldValue isn't emptyIf fieldValue isn't empty

Yes

Change sender to fieldValue.Change sender to fieldValue.

No

No steps

On error

No steps

Set recipient to blank.Set recipient to blank.

Get the current date and time and save it as sentAt.Get the current date and time and save it as sentAt.

Set headers to an empty record.Set headers to an empty record.

If a step below fails, save the error as missingHeadersIf a step below fails, save the error as missingHeaders

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.

On error

Show in the output: Could not read this message's headers; the row records the time it was saved instead of the time it was sent, and leaves the To column empty..Show in the output: Could not read this message's headers; the row records the time it was saved instead of the time it was sent, and leaves the To column empty..

If a step below fails, save the error as missingDateIf a step below fails, save the error as missingDate

Try

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

If dateHeader isn't emptyIf dateHeader isn't empty

Yes

Change sentAt to dateHeader.Change sentAt to dateHeader.

No

No steps

On error

No steps

If a step below fails, save the error as missingRecipientIf a step below fails, save the error as missingRecipient

Try

Trim headers.To and save it as toHeader.Trim headers.To and save it as toHeader.

If toHeader isn't emptyIf toHeader isn't empty

Yes

Change recipient to toHeader.Change recipient to toHeader.

No

No steps

On error

No steps

Set excerpt to blank.Set excerpt to blank.

If a step below fails, save the error as missingTextIf a step below fails, save the error as missingText

Try

Trim message.body and save it as sourceText.Trim message.body and save it as sourceText.

Shorten sourceText to excerptLimit characters and save it as excerpt.Shorten sourceText to excerptLimit characters and save it as excerpt.

On error

No steps

If excerpt is emptyIf excerpt is empty

Yes

If a step below fails, save the error as missingTextIf a step below fails, save the error as missingText

Try

Trim message.snippet and save it as sourceText.Trim message.snippet and save it as sourceText.

Shorten sourceText to excerptLimit characters and save it as excerpt.Shorten sourceText to excerptLimit characters and save it as excerpt.

On error

No steps

No

No steps

Set messageLink to https://mail.google.com/mail/u/@accountIndex/#all/@messageId.Set messageLink to https://mail.google.com/mail/u/@accountIndex/#all/@messageId.

If a step below fails, save the error as rowErrorIf a step below fails, save the error as rowError

Try

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

Add 1 to savedCount.Add 1 to savedCount.

On error

Add 1 to failedCount.Add 1 to failedCount.

If a step below fails, save the error as unlabelErrorIf a step below fails, save the error as unlabelError

Try

GmailRemove label savedLabel from email messageId.Remove label savedLabel from email messageId.

On error

Show in the output: Could not take the '@savedLabel' label back off message messageId after its row failed - remove it by hand so the message is saved on the next run.Show in the output: Could not take the '@savedLabel' label back off message messageId after its row failed - remove it by hand so the message is saved on the next run.

Show in the output: Could not save one email to the sheet; it will be tried again on the next run: rowError as text.Show in the output: Could not save one email to the sheet; it will be tried again on the next run: rowError as text.

On error

Add 1 to failedCount.Add 1 to failedCount.

Show in the output: Could not process one email: messageError as text.Show in the output: Could not process one email: messageError as text.

If failedCount is greater than 0If failedCount is greater than 0

Yes

Stop and report a problem: Saved savedCount email(s) to the sheet, but failedCount could not be saved - the messages above say why. A message that is not saved is tried again on the next run.Stop and report a problem: Saved savedCount email(s) to the sheet, but failedCount could not be saved - the messages above say why. A message that is not saved is tried again on the next run.

No

No steps

If savedCount is 0If savedCount is 0

Yes

Show in the output: No new email matching the search to save..Show in the output: No new email matching the search to save..

No

Show in the output: Saved savedCount email(s) to the sheet.Show in the output: Saved savedCount email(s) to the sheet.