Welcome new clients from their intake form

Every hour, your client intake sheet is read and a personalised welcome email is written for each new client from what they told you on the form. Each email comes to you as an approval request with the address it would go to: approve it and it goes out under your name; reject it, or leave it, and it is saved as a Gmail draft for you to edit.

Nothing reaches a client without a person approving it, and nobody is welcomed twice, even when a new run starts while another is still waiting for your answer. A row missing a name, email address or notes is skipped rather than guessed at, and the intake sheet itself is only ever read. If an approved email cannot be sent, it is saved as a draft where possible and the run is marked as failed with the client's name and the reason; if the intake tab's columns are not named Name, Email and Notes, the run stops and tells you which columns it found.

You need Google Sheets, Gmail and Anthropic connected, an intake tab with 'Name', 'Email' and 'Notes' columns, a second tab headed 'Email', 'Name', 'Outcome' and 'Welcomed at', and a short description of your firm, what it does and how you like to sound.

Run every hour.Run every hour.

Set intakeSheetUrl to blank.Set intakeSheetUrl to blank.

Set intakeTabName to blank.Set intakeTabName to blank.

Set welcomedTabName to blank.Set welcomedTabName to blank.

Set firmName to blank.Set firmName to blank.

Set firmContext to blank.Set firmContext to blank.

Set maxClientsPerRun to 10.Set maxClientsPerRun to 10.

Google SheetsRead column 'A' of welcomedTabName in intakeSheetUrl. Save it as welcomedRead.Read column 'A' of welcomedTabName in intakeSheetUrl. Save it as welcomedRead.

Set welcomedKeys to an empty list.Set welcomedKeys to an empty list.

For each loggedAddress in welcomedRead.values:For each loggedAddress in welcomedRead.values:

If a step below fails, save the error as loggedErrorIf a step below fails, save the error as loggedError

Try

Add loggedAddress in lowercase to the end of welcomedKeys and save it as welcomedKeys.Add loggedAddress in lowercase to the end of welcomedKeys and save it as welcomedKeys.

On error

Show in the output: Ignored an entry in the '@welcomedTabName' tab that could not be read as an email address.Show in the output: Ignored an entry in the '@welcomedTabName' tab that could not be read as an email address.

Google SheetsRead the rows of intakeSheetUrl. Save it as intake.Read the rows of intakeSheetUrl. Save it as intake.

If intake.headers is emptyIf intake.headers is empty

Yes

Stop and report a problem: The '@intakeTabName' tab is empty. Its first row must be a header row with columns named Name, Email and Notes.Stop and report a problem: The '@intakeTabName' tab is empty. Its first row must be a header row with columns named Name, Email and Notes.

Else if @intake.headers doesn't contain "Name" or @intake.h…

Join intake.headers with , and save it as foundHeaders.Join intake.headers with , and save it as foundHeaders.

Stop and report a problem: The '@intakeTabName' tab needs columns named exactly Name, Email and Notes in its first row, but it has: foundHeaders. Rename those header cells (for a Google Form, rename the questions) to Name, Email and Notes, then run it again.Stop and report a problem: The '@intakeTabName' tab needs columns named exactly Name, Email and Notes in its first row, but it has: foundHeaders. Rename those header cells (for a Google Form, rename the questions) to Name, Email and Notes, then run it again.

Otherwise

No steps

Set welcomedThisRun to 0.Set welcomedThisRun to 0.

Set stoppedAtLimit to false.Set stoppedAtLimit to false.

Set failures to an empty list.Set failures to an empty list.

For each row in intake.rows:For each row in intake.rows:

If welcomedThisRun is greater than or equal to maxClientsPerRunIf welcomedThisRun is greater than or equal to maxClientsPerRun

Yes

Change stoppedAtLimit to true.Change stoppedAtLimit to true.

Stop repeating.Stop repeating.

No

No steps

Set rowLabel to row row.row_number as text.Set rowLabel to row row.row_number as text.

Set clientName to blank.Set clientName to blank.

Set clientEmail to blank.Set clientEmail to blank.

Set clientNotes to blank.Set clientNotes to blank.

Set rowIsComplete to false.Set rowIsComplete to false.

If a step below fails, save the error as missingColumnIf a step below fails, save the error as missingColumn

Try

Trim row.Name and save it as clientName.Trim row.Name and save it as clientName.

Trim row.Email and save it as clientEmail.Trim row.Email and save it as clientEmail.

Trim row.Notes and save it as clientNotes.Trim row.Notes and save it as clientNotes.

Change rowIsComplete to the length of clientName is greater than 0 and the length of clientEmail is greater than 0 and the length of clientNotes is greater than 0.Change rowIsComplete to the length of clientName is greater than 0 and the length of clientEmail is greater than 0 and the length of clientNotes is greater than 0.

On error

Show in the output: Skipping rowLabel - the intake tab needs a Name, an Email and a Notes column.Show in the output: Skipping rowLabel - the intake tab needs a Name, an Email and a Notes column.

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

If rowIsComplete is noIf rowIsComplete is no

Yes

Show in the output: Skipping rowLabel - Name, Email and Notes must all be filled in before a welcome email can be written.Show in the output: Skipping rowLabel - Name, Email and Notes must all be filled in before a welcome email can be written.

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

No

No steps

If not clientEmail is a valid email addressIf not clientEmail is a valid email address

Yes

Show in the output: Skipping rowLabel - '@clientEmail' is not a valid email address.Show in the output: Skipping rowLabel - '@clientEmail' is not a valid email address.

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

No

No steps

Make clientEmail lowercase and save it as emailKey.Make clientEmail lowercase and save it as emailKey.

If welcomedKeys contains emailKeyIf welcomedKeys contains emailKey

Yes

Show in the output: clientEmail has already been welcomed - leaving rowLabel alone.Show in the output: clientEmail has already been welcomed - leaving rowLabel alone.

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

No

No steps

If a step below fails, save the error as welcomeErrorIf a step below fails, save the error as welcomeError

Try

Set prompt to You are writing the body of a welcome email to a brand new client of a professional services firm. Write only the body of the email: no subject line, no placeholder brackets such as [Name], and nothing you cannot support from the details below. Greet the client by name, refer to what they asked for in their own terms, say what happens next, and sign off from the firm. Keep it under 200 words and do not invent dates, prices, or people.Set prompt to You are writing the body of a welcome email to a brand new client of a professional services firm. Write only the body of the email: no subject line, no placeholder brackets such as [Name], and nothing you cannot support from the details below. Greet the client by name, refer to what they asked for in their own terms, say what happens next, and sign off from the firm. Keep it under 200 words and do not invent dates, prices, or people. About the firm: firmName firmContext The new client: Name: clientName What they told us: clientNotes.

ClaudeAsk Claude: prompt. Save it as written.Ask Claude: prompt. Save it as written. Maximum response length: 700

Trim written.response_text and save it as welcomeBody.Trim written.response_text and save it as welcomeBody.

If welcomeBody isn't emptyIf welcomeBody isn't empty

Yes

Set subject to Welcome to firmName.Set subject to Welcome to firmName.

Google SheetsRead column 'A' of welcomedTabName in intakeSheetUrl. Save it as welcomedNow.Read column 'A' of welcomedTabName in intakeSheetUrl. Save it as welcomedNow.

If welcomedNow.values contains emailKeyIf welcomedNow.values contains emailKey

Yes

Show in the output: clientEmail is already waiting on another run's approval - leaving rowLabel to it.Show in the output: clientEmail is already waiting on another run's approval - leaving rowLabel to it.

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

No

No steps

Google SheetsAdd a row to welcomedTabName in intakeSheetUrl.Add a row to welcomedTabName in intakeSheetUrl.

Add emailKey to the end of welcomedKeys and save it as welcomedKeys.Add emailKey to the end of welcomedKeys and save it as welcomedKeys.

Add 1 to welcomedThisRun.Add 1 to welcomedThisRun.

Request approval: Send this welcome email to clientName <clientEmail>? Save it as review.Request approval: Send this welcome email to clientName <clientEmail>? Save it as review.Approve and it goes to the client now, under your name. Reject it, or leave it, and it is saved as a Gmail draft for you to edit. Subject: subject welcomeBody

Set outcome to blank.Set outcome to blank.

If review.approved is yesIf review.approved is yes

Yes

If a step below fails, save the error as sendErrorIf a step below fails, save the error as sendError

Try

GmailSend an email to clientEmail.Send an email to clientEmail. Subject: subjectwelcomeBody

Change outcome to Sent.Change outcome to Sent.

On error

Change outcome to Send failed.Change outcome to Send failed.

If a step below fails, save the error as draftAfterSendErrorIf a step below fails, save the error as draftAfterSendError

Try

GmailDraft an email to clientEmail.Draft an email to clientEmail. Subject: subjectwelcomeBody

Change outcome to Send failed, saved as a draft.Change outcome to Send failed, saved as a draft.

On error

No steps

Add clientName <clientEmail>: you approved the welcome email but it could not be sent (sendError.message). Recorded as '@outcome' on the '@welcomedTabName' tab. to the end of failures and save it as failures.Add clientName <clientEmail>: you approved the welcome email but it could not be sent (sendError.message). Recorded as '@outcome' on the '@welcomedTabName' tab. to the end of failures and save it as failures.

No

If a step below fails, save the error as draftErrorIf a step below fails, save the error as draftError

Try

GmailDraft an email to clientEmail.Draft an email to clientEmail. Subject: subjectwelcomeBody

Change outcome to Drafted.Change outcome to Drafted.

On error

Change outcome to Draft failed.Change outcome to Draft failed.

Add clientName <clientEmail>: the welcome email could not be saved as a draft (draftError.message). Recorded as 'Draft failed' on the '@welcomedTabName' tab. to the end of failures and save it as failures.Add clientName <clientEmail>: the welcome email could not be saved as a draft (draftError.message). Recorded as 'Draft failed' on the '@welcomedTabName' tab. to the end of failures and save it as failures.

If a step below fails, save the error as recordErrorIf a step below fails, save the error as recordError

Try

Google SheetsAdd a row to welcomedTabName in intakeSheetUrl.Add a row to welcomedTabName in intakeSheetUrl.

On error

Add clientName <clientEmail>: outcome, but that could not be written to the '@welcomedTabName' tab (recordError.message). to the end of failures and save it as failures.Add clientName <clientEmail>: outcome, but that could not be written to the '@welcomedTabName' tab (recordError.message). to the end of failures and save it as failures.

No

Show in the output: No welcome email was written for clientName, so rowLabel is left for the next run.Show in the output: No welcome email was written for clientName, so rowLabel is left for the next run.

On error

Add Could not welcome clientEmail: welcomeError.message. If they show as 'Awaiting approval' on the '@welcomedTabName' tab, delete that row to have them offered again. to the end of failures and save it as failures.Add Could not welcome clientEmail: welcomeError.message. If they show as 'Awaiting approval' on the '@welcomedTabName' tab, delete that row to have them offered again. to the end of failures and save it as failures.

Set limitNote to blank.Set limitNote to blank.

If stoppedAtLimit is yesIf stoppedAtLimit is yes

Yes

Build the text This run asked about @maxClientsPerRun clients, the most 'maxClientsPerRun' allows; anyone else is offered on the next run. and save it as limitNote.Build the text This run asked about @maxClientsPerRun clients, the most 'maxClientsPerRun' allows; anyone else is offered on the next run. and save it as limitNote.

No

No steps

If failures isn't emptyIf failures isn't empty

Yes

Join failures with a new line and save it as failureList.Join failures with a new line and save it as failureList.

Stop and report a problem: Some welcome emails did not go out as planned:Stop and report a problem: Some welcome emails did not go out as planned: failureList@limitNote.

No

No steps

Show in the output: Welcome emails handled this run: welcomedThisRun@limitNote.Show in the output: Welcome emails handled this run: welcomedThisRun@limitNote.