Using AI to Populate a Spreadsheet with Content for Hundreds of Blog Posts

If you’re a content creator or marketer, you know that coming up with ideas for blog posts can be a daunting task. It can be especially challenging when you need to create a large volume of content in a short period of time. One solution to this problem is to use artificial intelligence (AI) to generate content ideas and even complete entire blog posts for you.

Google Sheets + AI

We are using Google Sheets + AI to create sections of blog posts in bulk. Think of each row of a spreadsheet being sections of a blog post. Each blog post needs the following information to have any chance of ranking.

  1. Page title
  2. slug (permalink URL)
  3. Meta description
  4. H1 tag
  5. Intro paragraph

Let’s start with these sections for our example but you can apply this same logic and formulas to generate many sections of the blog post for you. A bit more on that later.

Google App Script Function

First up is the Google App Script you will need. Now don’t be scared if you are not a coder. This is easier than you think .

const SECRET_KEY = "YOUR-SECRET-CODE-HERE";
const MAX_TOKENS = 200;

/**
 * Completes your prompt with GPT-3
 *
 * @param {string} prompt Prompt
 * @param {number} temperature (Optional) Temperature. 1 is super creative while 0 is very exact and precise. Defaults to 0.4.
 * @param {string} model (Optional) GPT-3 Model to use. Defaults to "text-davinci-003".
 * @return Completion returned by GPT-3
 * @customfunction
 */
function AI(prompt, temperature = 0.4, model = "text-davinci-003") {
  const url = "https://api.openai.com/v1/completions";
  const payload = {
    model: model,
    prompt: prompt,
    temperature: temperature,
    max_tokens: MAX_TOKENS,
  };
  const options = {
    contentType: "application/json",
    headers: { Authorization: "Bearer " + SECRET_KEY },
    payload: JSON.stringify(payload),
  };
  const res = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
  return res.choices[0].text.trim();
}

The Google App Script provided above is an example of how you can use AI to generate content for your blog posts. The script uses the OpenAI API to access the GPT-3 (Generative Pre-trained Transformer 3) language model, which is a state-of-the-art AI language model that can generate human-like text.

To use this script, you’ll first need to sign up for an OpenAI API key and replace “YOUR-SECRET-KEY-HERE” in the script with your actual API key. You’ll also need to specify the maximum number of tokens (words and punctuation) that you want the GPT-3 model to generate in its response. The more tokens, the longer the content.

Google Sheets AI Formula

Once you’ve set up the script, you can use it to generate content for your blog posts by calling the AI() function and providing it with a prompt. The prompt can be a question, a statement, or any other type of text that you want the GPT-3 model to complete. You can also specify the temperature and model parameters to control the creativity and precision of the generated text. A higher temperature will result in more creative and varied responses, while a lower temperature will result in more precise and accurate responses.

For example, let’s say you want to generate ideas for blog posts about toys for kids. You could use the following formula in a cell in your spreadsheet:

=AI("Create a blog post title about toys for kids", 0.5)

This would generate a response from the GPT-3 model that completes the prompt “Create a blog post title about toys for kids” with a temperature of 0.5. The response might look something like this:

“The Benefits of Toys for Kids: How Play Can Enhance Development”

You can then use this generated text as a starting point for your blog post, or you can use it as is if it’s already relevant and useful to your audience.

Let’s dig a little deeper and set up the spreadsheet.

Creating the spreadsheet for bulk post creation

If you think about your post being cut up into section you can use the AI to generate each of those sections. Now to be fair, you can certainly go do this in a tool like Jasper.ai or the like but why this is so much better is that you can take the “recipe” that you are building and apply it to as many rows as you’d like. This bulk application of AI takes programatic SEO (pSEO) to a whole new level.

The first thing I do is create a column for your target keywords. These keywords should be well researched using a tool like AHREFs.

google sheets ai

Next I isolate the audience you are targeting and variations of those audiences. For this example I am going to target parents with 1 year olds and 2 year olds. I’ve also duplicated the target keywords for each audience.

screely 1672432820320

Now using a simple concatenation formula I create a prompt for the page title. The concatenation formula is as follows:

=CONCATENATE("Create a listicle style blog post title about ",B3," for ",C3)

I am combining the text “Create a listicle style blog post title about ” and the [Target Keyword] and the [Audience]. Now drag that all the way to the bottom to create the content for the rest of the column.

CleanShot 2022 12 30 at 15.45.32

Once we have the prompt for the page title we can use our AI formula to generate the text for the page title in the next column. The formula to use is as follows:

=AI(D3)
screely 1672433591337

Can you see the power of this yet? This isn’t just for page titles though. Here are the prompt ideas that I have been using for some of the other items. We can also simplify this to include the prompt inside of the AI formula. Here are examples of how that would look.

Meta Description

=AI(CONCATENATE("Write a meta description for a blog post titled: ",E3, ". Be sure to use the keywords ",B3))

Intro Paragraph

=AI(CONCATENATE("Write an intro paragraph for a blog post titled: ",E3, ". Be sure to use the keywords ",B3))

The applications of how to incorporate AI into pSEO are seemingly never-ending. I have been using this method to produce a significant amount of content.

Other Ways To Use This Formula

Here are some other areas that this method can be applied to:

  1. SCHEMA markup
  2. H2-H6 sections
  3. FAQs – Simply take the “People also ask” questions for your targeted searches and turn them into the prompt of a new column.
  4. CTA language
  5. Statistics (make sure you verify they are correct)

Using AI to generate content for your blog posts can be a great time-saver and can help you to quickly and easily produce high-quality content at scale. With the Google App Script provided above, you can easily access the power of the GPT-3 model and use it to generate ideas and content for your blog posts.

Enjoy!

Table of Contents
    Add a header to begin generating the table of contents
    About the author

    Mike McKearin is the CEO at WE•DO | SEO Expert | Adventurer | Optimist

     

    Mike has a long history in the world of SEO, studying it and testing various methods for over two decades now. When he's not working on his business or being a dad, Mike loves to go on adventures with his family - they live in the beautiful state of North Carolina and love exploring all that it has to offer. He's an optimist who believes that anything is possible if you set your mind to it, and he wants to help others achieve their dreams through WE•DO

    Free WordPress SEO Analysis

    Want more traffic to your WordPress website? Get a free WordPress SEO Analysis and see how you can improve your traffic to get more sales online.

    SEO Analysis

    *This is a human SEO audit. Once we review your site we will reach out to schedule a time to review with you.