• Compound Leverage
  • Posts
  • Write hundreds of personalized emails in minutes with Google Sheets and Open AI

Write hundreds of personalized emails in minutes with Google Sheets and Open AI

Create hundreds personalized emails in minutes with Google Sheets + Open AI. It's easy and cheap!!

Compound Leverage

Issue #001

Stoic Wisdom of the Day

"The chief task in life is simply this: to identify and separate matters so that I can say to myself, which are externals not under my control, and which have to do with the choice of my will. And I will worry no more about things which are not in my power."

- Epictetus, Discourses, 2.5.1-2

Writing prospecting emails is one of those activities that can be a real chore.

When I talk to other entrepreneurs, I hear a lot of reasons why they don’t do it and never personalize the outreach. Typically, they say one of these three things.

  • I don’t know what to personalize.

  • I don’t want to hire a copywriter, or they are too expensive.

  • I don’t know if I have time to write the messages.

If any of these statements sound like you, I have the answer: write emails faster and personalize them for better engagement; you can use Google Sheets and OpenAI to be the rescue.

It’s a simple process that takes a few minutes to set up. The biggest time investment is deciding the trigger you plan to use to write the personalized message for your prospects. Instead of spending hours and hours writing customized emails to large blocks of prospects, you can spend less than a dollar for low-friction and scalable solution.

This system is essential because personalization is key to increasing engagement and conversion rates.

But email personalization doesn’t have to be that time-consuming. A simple and sound system can save you time and effort.

So today, I’m showing you how to write tens or hundreds of personalized emails with a simple copy and paste of formulas.

Here's the step-by-step:


Step 1: Create a Google Sheet

The first step in the process is to open Google Sheets and create the fields you need to build your prompt to write the emails.

These are your building blocks.

For example, suppose you know people who received Series A funding are likely to have interest in your consulting offer. In that case, you may want to set up a Google Alert to track those events and use that as a column in your spreadsheet for personalization. Other fields like

  • The role you are acting as

  • The task you want to perform

  • Recipient’s name

  • Sender’s name

  • Trigger

  • Something about you

  • The remaining content for the email composition


Step 2: Setup your fields

Once you have your prompt fields, enter them into your Google Sheet to build your prompt.

To simplify this task, I like to focus on who I am reaching out to, the reason I am reaching out (an event, hiring, article, something in their social media profile), and a quick one-sentence statement about me.

You will need a column to combine the individual fields columns in a long text string. Here is a short example of a concatenated formula.

=A2& ""&B2 &". You are writing a congratulations emails to " & C2 & " from " &D2& " about their recent building purchase of "& E2 & " from " & F2


Step 3: Create an Open AI (ChatGPT) API Account and Setup your API Key

Now that your spreadsheet is set up, you must create an Open AI key to enter into the Google extension.

Visit the Open AI website and create or log in. Then, sign in to your account and select API.

​Once you select API, you will see this screen.

Go to the upper right corner, select your profile icon, then choose View API Keys. Select the Create API Key button.

Copy your API key to a document to reference later. You must paste it into the Google Chrome extension in Step 4.

Note: There is a small charge to use the API. It is a fraction of a cent, and Open AI gives you a $5 credit with an expiration. Below are charts summarizing the GPT 3.5 and GPT 4 fees.

GPT 3.5 fees are in the chart below.

GPT 4 fees are in the chart below.

It's time to start creating personalized emails.

​Step 4: Install the GPT for Sheets and Doc extension

Return to your Google Sheet and select Extensions > Add-Ons > Get Add-ons.

Note: The extension is available for Excel and Google Docs.

Type in GPT for Sheets. The extension GPT for Sheets and Docs will appear in the search list. Select it. Then, select the icon to install it.

Note: Ensure you are logged into one Google account while setting up the extension, or you will encounter an error during setup.

Follow the steps to install the extension. During the setup process, it will ask you for your API key. Copy and paste the key you saved into the input field.

Once you are done, you will see the extension in the list. Activate it for your spreadsheet. The extension provides you with a list of functions you can create.

Step 5: Add your API Key to the GPT for Sheets and Doc Extension

Once you are done, you can add the key later if you did not do it in Step 4 by selecting the Set API key under the GPT for Sheets and Docs extension menu.

Step 6: Add the GPT formulas to your spreadsheet columns

Create a formula that says = GPT(Prompt cell reference). Here is an example below.

=GPT(G2)

Review the message output to ensure it is what you expect. One thing I like to do before building my spreadsheet is run a test in ChatGPT to ensure the prompt I am making will give me the results I expect. This will save you time on the front end of the process.

If you run into problems or want to learn about formulas, you can create with the extension select Help from the extension menu. Access the extension help documentation by selecting this link.

Step 7: Copy your formulas down the rows

Once you are happy with the prompt output, copy your formulas down the rows of your Google Sheet. Review the response, and once you are satisfied with it, highlight the cell and copy the formulas for each prospect in your Google Sheet.

Pro tip: You can use the extension to do a mail merge for Gmail or use Google App Script. To build it in App Script, review the Google Developer documentation and video to see how.

You can compound your lead generation once you’ve used this system to write and send personalized emails.

Workflow systems with Generative AI workflows and processes will shift the trajectory of your lead generation and outreach process.

Feel free to take this system I’ve shared today - tweak and modify it for your business or copy it.

Happy email personalization.

Whenever you're ready, here are three ways I can assist you:

 1. You want a prompt design or AI workflow productivity tool or template to get more of your work done faster. Visit my website and view the Productivity Tools section. Click here to send a request. I am always looking to create new tools for subscribers and website visitors. (free and paid)

 2. You want to have a one-on-one session to work on a prompt design or AI workflow to increase your productivity ($200 for one hour)

 3. You want to automate more of your work​ by discussing a prebuilt or customized AI bot or prompt design workflow (free)

Join the conversation

or to participate.