A Guide to Turning Google Sheets into A Cold Email Machine

Preface: This masterpiece comes with a few prerequisites:

• An OpenAI API Key
• The ability to copy and paste code
• A willingness to fiddling with stuff that will never work on the first go (or fail brilliantly producing hilarious results)

Once we've cleared that hurdle, let's dive into hacking together some adorably janky apps in Google Sheets!

I recently stumbled upon a post showcasing a Google Sheet template ingeniously designed to take emails from a column and then churn out a concise summary of the corresponding company. This tool can be a game-changer for outreach efforts, and all the genius credit goes to Andrew P.

Andrew P. was thoughtful enough to share this template with the LinkedIn folks, allowing the curious to use it straight away if you dropped him a line . You can dodge the tedious labor, dive right into launching cold emails, and now that you're practically a company connoisseur from reading the summaries, you'll be adding value in no time. (Seriously go check out his page, find the original post here and use that version if you want something that actually does the job).

In contrast, I don't do much in the realm of cold emailing. I was much more concerned with how this magical system works. And why go through all the effort of commenting on someone else's neatly packaged, fully functional template, when you can undertake the horrific goal of crafting your own gloriously flawed version? Then, to really add to it turn it into a guide so others can hopefully build much better versions of what I am about to present. And so we find ourselves here...

I am going to be honest, this thing is far from peak performance, but it was easy. It was thrown together in the span of an hour, more effective methods exist out there, and I am basically live blogging myself Googling shit. But, it does have the charm of being functional (mostly), it wasn't a huge time sink, and my hope is that by the end, you'll be left with:

1. Proof that this isn't as terrifying or mystifying as it first appears,
2. Some inspiration to refine this into a more potent tool to boost your productivity,
3. The fundamental logic to construct more awe-inspiring gadgets that I can subsequently add to a list of tools I say I will use and never do (thanks in advance!).

So How Does it Work?

That was my question too, this is neat but what is actually going on. I will admit despite writing way more VBA than I care to and spending more time in Excel than with my family, I wasn't familiar with custom Google Sheets functions.

Fortunately these are pretty easy, Open Google Sheets, click on "Extensions" > "Apps Script".

A new tab will open with a script editor looking like so

Now, some of you might be looking at this, concerned already. But worst case, you're going to just copy and paste into here and edit a single line or two. What this is, is a scripting platform developed for application development in Google Workspace. If you have spent a lot of time in Excel, think of it as the macro development area.

All the logic we need to make a function will go in here, so later on, all you have to do is the familiar "=function(A1, A2)" in your sheet, and it should just work.

Logic

Alright, now to map out what we're actually trying to do here, so we can throw together our own sleek functions. At a high level this is what I guessed was going on:

1. Pull an email input and wrangle out the domain.
2. Investigate said domain and scrape some sweet data.
3. Unleash the OpenAI API on that data, asking it for a succinct company summary.
4. And because I'm not exactly inclined to write the email either, we've masterminded a second function. This thing should take the neatly summarized company profile, a catalogue of services you offer, and draft a ready-to-send outreach email.

Email Parsing

Let's kick things off on the simpler side with the email parsing bit. We're going to jot down a few lines of code that will take a given email and neatly snip out just the domain. You can nix that pre-provided function and paste this sparkling new one into your blank canvas:

function getEmailDomain(email) {
  var domain = email.split('@')[1];
  return domain;
}        

It's not the most glamorous, but it gets the job done. Now you could saunter over to your Google Sheet, pop into cell A2, type "=getEmailDomain(A1)", and if A1 contains [email protected] , you should find website.com grinning back at you from A2. Congrats, you've just created something that works by copying code off the internet, you're a dev.

(If you don't really care about the cogs and gears powering this, feel free to skip ahead to the next header)

But for the bored, here's the rundown. You're leveraging the power of the JavaScript .split() method on the parameter you passed the function (the email sitting pretty in cell A1). This split method carves up the input into two chunks, using a reference point known as a delimiter ("@" in our case):

• Everything before "@": example
• Everything after "@": website.com
• and because "@" was our delimiter we treat it like garbage and toss it

We're then handing over the second chunk (website.com) to a variable called "domain". I say 'second' because the count begins at 0, so ["example", "website.com"] equates to [0] = "example" and [1] = "website.com".

Finally, we return the variable "domain", and that's the result Google Sheets places into the cell from which you called the "=getEmailDomain(A1)".

Domain Info

Now that we've extracted a company domain, it's time to unearth some information about it. This is the crossroads where you could branch off and create something already better and go and find a search ready GPT based API. Given that OpenAI's GPT 3.5 API hasn't yet evolved to handle web searches, and my lack of interest to sign up for an already search-compatible tool using GPT, I had to duck tape a solution that used the tools I already had.

Enter Cheerio. Not the heart-friendly breakfast cereal, drop the 's' and you get Cheerio a brilliant tool for web scraping. You can delve into the Cheerio docs here , but here's the short: Cheerio eases the process of playing with HTML or XML documents by utilizing a jQuery-like syntax. It equips you with the power to select, manipulate, traverse, and extract data from these documents, making it basically really good at getting data off a webpage.

If all of that sailed over your head, no worries. All you need to know is that Cheerio is your tool for extracting data from a website, like the URL you just ripped out of an email.

To add Cheerio to our Google Apps Script project, follow these simple steps:

1. Navigate to the "Library" section in your Apps Script project settings (on the left click on the "+ " button beside Libraries).
2. In the "Script ID" field, paste the following identifier which corresponds to the Cheerio library: 1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0
3. Choose the latest version of the library and select the identifier you prefer to use in your code. For simplicity, we will stick to 'Cheerio'.
4. Click on "Add". Now, the Cheerio library is ready for use in your script.

Here's the code

function scrapeDomain(domain) {
  var url = 'https://www.' + domain;


  try {
    var response = UrlFetchApp.fetch(url);
    var htmlContent = response.getContentText();


    // Load the HTML content with cheerio
    var $ = Cheerio.load(htmlContent);


    // Extract relevant information from the HTML content
    var relevantInfo = '';


    // Modify the logic to extract the desired information from the HTML content using cheerio selectors
    // This example is to extract all paragraph text:
    $('p').each(function() {
      var paragraphText = $(this).text();
      relevantInfo += paragraphText + '\n';
    });


    return relevantInfo;
  } catch (error) {
    console.error('Error:', error);
    return null;
  }
}         

The code snippet above takes the domain extracted from the email, crafts a neat URL out of it, and then skims all the paragraphs from the page, bundling them into the "relevantInfo" variable. While there are numerous ways to refine this process, it generally does a fine job (depending on the site). Now, you have a heaping pile of text gleaned from the website's main page that we can shape into a descriptive company profile.

OpenAI for the heavy lifting

The last segment served us a nice bundle of raw text straight from the homepage of the website corresponding to the email domain. Now, it's time to take this info-dump and hand it over to OpenAI's API, politely ask it to create a concise summary from it. This is where your OpenAI API steps into the limelight, paste that into line 3 between the quotes.

function getOpenAISummarize(prompt) {
  // Use your API Key here
  var apiKey = 'PASTE_API_KEY_HERE_BETWEEN_QUOTES';
  
  // Change the API URL here if you want to use a different API
  var url = 'https://api.openai.com/v1/chat/completions';
  
  // Change the model here
  var model = 'gpt-3.5-turbo';
  
  // Request along with the parameters
  var request = {
    model: model,
    messages: [{
      role: "system", 
      content: prompt
    }],
    temperature: 0.7,
    top_p: 1,
    max_tokens: 256,
    frequency_penalty: 0,
    presence_penalty: 0
  };
  
  // Headers
  var options = {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + apiKey
    },
    payload: JSON.stringify(request)
  };
  
  // Connect to the OpenAI API, sending the headers and parse the JSON response
  var response = UrlFetchApp.fetch(url, options);
  var jsonResponse = JSON.parse(response.getContentText());
  
  // Get the response message
  var content = jsonResponse.choices[0].message.content;
  
  return content;
}        

The code above accepts an input prompt and channels it to OpenAI's API, which in turn responds with an output as good as what goes into it. It might seem intimidating at first glance, but imagine it as simply typing into the ChatGPT text box. What you type in there is the "prompt" attribute you pass on. This will all fall neatly into place in the upcoming step where we bring all these elements together.

Assembling the Pieces

With our building blocks at hand, it's time to piece them all together and conjure up some compelling company descriptions and a cold email ready to hit the inbox.

First things first, let's build our company description function.

function corpSummary(email) {
  var domain = getEmailDomain(email);
  var domainInfo = scrapeDomain(domain);
  var summary = getOpenAISummarize('please create a brief company summary based on the following: ' + domainInfo);


  return summary;
}        

We now have something that should do a few other things, here's how that should play out:

1. We call the first piece of code we wrote, feed it the email, and tuck away the response (the URL) under the alias 'domain'.
2. Next, we pass that 'domain' to the 'scrapeDomain' function we scripted earlier, which employs Cheerio to scoop up all the paragraph text from the webpage and returns it as raw text. We dub that bad boy 'domainInfo'.
3. We then heap the raw text to the OpenAI API, pairing it with a prompt detailing what we're asking it to do – in this case, 'please create a brief company summary'. It uses the raw text scraped from the domain to concoct its summary. This is also the point where things could go awry. If the text yanked off the domain is shallow or doesn't contain sufficient info to inform the AI model about the company's operations, you might end up with some peculiar descriptions. For instance, if you had an email like [email protected] , the URL you'd get would be https://www.apple.com . This URL, however, doesn't offer much insight into what the company does because the main page is chock-full of promotions and product names. So, bear in mind this potential pitfall of my method and why you might be better off using web search compatible GPT APIs. You could also play with the prompt to get better results as well chances are if the company is big enough to have no info on the homepage GPT probably knows about it anyway or it has a terrible site and you can turn your cold email into an SEO pitch.

With this function, you're ready to generate concise company summaries using raw information extracted directly from the domain corresponding to the provided email. Next stop, crafting cold emails!

Now if you go back to your sheet and call your

And you should get something like

Cherry on Top, Cold Email Creation

To finish it off we've whipped up a function that crafts a crisp, cold outreach email. This function leverages 99% of the logic we've already set up and tailors an email based on the services we outline in a cell and a business description housed in another cell.

Let's take a look at how this works.

function coldEmail(summary, services) {
? var emailDraft = getOpenAISummarize('Draft a short and polite cold outreach email that discusses the services our company offers and how it could be a good fit based on their company description. Services we offer: ' + services + '. Customer Company description: ' + summary);


? return emailDraft;
}        

In the same vein as before, we utilize the OpenAI API to compose a succinct and polite cold email. The prompt we provide details what we need - a well-crafted email that discusses the services we offer and draws parallels between our services and the customer's company, based on the summary we've procured.

The function requires two inputs: a summary (produced by our 'corpSummary' function that is now in whichever cell you just used that) and a list of services that our company offers (which you can input in a cell in your Google Sheet). The function then crafts a personalized outreach email, all set to be dispatched to your prospective client!

Above the function taking the summary as the first parameter and services you outline as the second. Below the result.

The beauty of this function lies in its scalability and adaptability. With minor tweaks, you can personalize this function to reflect your company's tone and style, ensuring that your outreach emails always hit the right note. You can edit this to auto pull the name based on value [0] in the email parse, etc.

And there you have it! A janky (but functional) Google Sheets application that uses OpenAI's API to generate company summaries and draft cold emails. It's proof that you don't always need expensive and sophisticated tools to automate tasks.

Full Code

Here is all of it in one place so you can just copy it all and start messing around.

function getEmailDomain(email) {
  var domain = email.split('@')[1];
  return domain;
}

function scrapeDomain(domain) {
  var url = 'https://www.' + domain;


  try {
    var response = UrlFetchApp.fetch(url);
    var htmlContent = response.getContentText();


    // Load the HTML content with cheerio
    var $ = Cheerio.load(htmlContent);


    // Extract relevant information from the HTML content
    var relevantInfo = '';


    // Modify the logic to extract the desired information from the HTML content using cheerio selectors
    // For example, to extract all paragraph text, you can use:
    $('p').each(function() {
      var paragraphText = $(this).text();
      relevantInfo += paragraphText + '\n';
    });


    return relevantInfo;
  } catch (error) {
    console.error('Error:', error);
    return null;
  }
}

function getOpenAISummarize(prompt) {
  // Use your API Key here
  var apiKey = 'PASTE_API_KEY_HERE_BETWEEN_QUOTES';
  
  // Change the API URL here if you want to use a different API
  var url = 'https://api.openai.com/v1/chat/completions';
  
  // Change the model here
  var model = 'gpt-3.5-turbo';
  
  // Request along with the parameters
  var request = {
    model: model,
    messages: [{
      role: "system", 
      content: prompt
    }],
    temperature: 0.7,
    top_p: 1,
    max_tokens: 256,
    frequency_penalty: 0,
    presence_penalty: 0
  };
  
  // Headers
  var options = {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + apiKey
    },
    payload: JSON.stringify(request)
  };
  
  // Connect to the OpenAI API, sending the headers and parse the JSON response
  var response = UrlFetchApp.fetch(url, options);
  var jsonResponse = JSON.parse(response.getContentText());
  
  // Get the response message
  var content = jsonResponse.choices[0].message.content;
  
  return content;
}


function corpSummary(email) {
  var domain = getEmailDomain(email);
  var domainInfo = scrapeDomain(domain);
  var summary = getOpenAISummarize('please create a brief company summary based on the following: ' + domainInfo);


  return summary;
}


function coldEmail(summary, services) {
  var emaildraft = getOpenAISummarize('Draft a short and polite cold outreach email that discusses the services our company offers and how it could be a good fit based on their company discription. Services we offer :' + services + 'customer Company description: ' + summary)


  return emaildraft
}        

Congratulations! You've created an automated Google Sheets application. We've covered how to extract a domain from an email, scrape useful information from a company's webpage, and use OpenAI's API to generate a brief summary of the company. We've even crafted a tailored cold outreach email that utilizes this summary to engage potential clients.

As with anything, what we've built is by no means perfect, and there's plenty of room for improvement. Whether you're an experienced developer or a curious beginner, I hope this has sparked your imagination and given you the tools to create something much better and more inline with the original inspiration.