Skip to content

Lesson 1 – AI Content Generation: Populate a Spreadsheet with Content Ideas in Just a Few Seconds

Welcome to the course! I want to start by introducing myself.

I’m Shawn, I’ve been casually creating websites and web applications for around 18 years. I’m a brutally honest person, and so I’m going to just be honest with you right now..

I created this system for myself, I was really just going about trying to solve my own problems.

I had no urgent need to write or share this course but I’ve worked really hard to simplify it so that anyone can learn and benefit from all the trial and error that I had to go through to produce this, and then obsessively simplify it.

It is true! GPT did help me create this System and the course, but I need you to understand;

This wasn’t some one-off request.

To get this to work I have a massive 3 week chat history with GPT-4 of 10-20 threads a day.

Version 1.0 of this course had you setting up a development environment and coding with python…

None of that is Necessary!

I realized quickly that python was way too inaccessible for a lot of people, and so I went back, and refined it.

I would hit my limit on GPT 4 and move on to other tasks until it came back. Mostly I was trying to find easier options to do everything that I wanted. I also spent a lot of messages back and forth troubleshooting where I had to catch it changing the names of my variables and functions over and over again…

After nearly 3 straight weeks of “this isn’t good enough, give me more options” and cutting everything optional out of the process. I have reduced the entire thing, to a little bit of copy and paste. You will need to sign up for some applications and get some keys but other than that everything has been massively simplified.

This is the 5th iteration of this course, I think it’s ready to share.

I have provided examples that you can copy for each step. They require the minimum of your keys and urls to function. I have done a lot of work to make this process as easy and straightforward as I could.

For an accessible example we are going to rely on google app script as much as possible. This allows me to share examples in Google Sheets that do not require any extensive editing of code. You will need to register and input your own variables but we have simplified it as much as possible for this example.

A Quick Outline of Our Goals in This Course

What we are trying to do it fairly simple in concept. We are trying to source content ideas, and sent them to Open AI API with a prompt to write a blog article. Then we wait for the “completion” and send it to a Blog as a Draft.

I think that most people can understand that, but the process is a tiny bit more complex. Try not to get overwhelmed, the process might look complex but it’s really handled by only two main functions.

The ParseRSS() Function is populating the sheet, and the GenerateContent() function is doing the rest.

Let me quickly explain Parse and RSS in case you need to understand these terms.

RSS is a standard format for delivering web content. Many news-related sites, weblogs and other online publishers syndicate their content as an RSS Feed to whoever wants it. This provides a way for you to subscribe to updates from websites automatically.

Parsing involves taking the data from the RSS feed (which is in XML format) and translating it into a format that’s more useful for us (like writing the data into a Google Sheet). It’s about extracting the key parts of the data – in this case, the title and description of each item in the feed – so that we can use them.

Essentially we have to click two buttons, it could be one but I personally prefer to QA before we send to the API.

A visual of the process can hopefully explain this a lot better than text. So here is an example: The Green Parts are what we will cover in this course, and the Blue are potential customizations that you could consider depending on your own needs.

Note: These customizations are not limited to what is here, and we will discuss our options for scaling and potential for inputs and other integration towards the end of the course.

So we have our Inputs on the left that we import with the ParseRSS() function. Sheets acts like a database that we could use to further analyze sentiment or keywords if we wanted to. Once we are satisfied with the data we can Run GenerateContent() which pushes each row to OpenAI API as a completion request.

When we get the completion it gets posted as a draft, and we move on to the next article.

This is quite customizable from the inputs to analysis to the prompt and the specific model. Also can manipulate how and which data is sent and how it is organized. For this example, we’re just going to go with whatever is easiest to demonstrate. I hope that You can learn some skills in AI Integration as well as get your hands on a pretty cool content publishing system in the process. So are you ready to get started?

Lesson 1: Let’s Parse this RSS feed into a Spreadsheet with one click!

In this first example we’re going to use the feed from the Open AI blog to populate a spreadsheet with posts.

Step 1: Prepare Your Google Sheet

First, you need to create a Google Sheet where you will input your RSS feed URLs and where the script will write the parsed data.

  1. Open Google Sheets (https://sheets.google.com)
  2. Click on “Blank” to create a new spreadsheet.
  3. Name your sheet appropriately, like “Elite Sheet”.
  4. In cell A1, type “Input”, and in B1 and C1, type “Title” and “Body” respectively.

Step 2: Input your RSS feed URLs

In column A below the “Feed URLs” header, add the URLs of the RSS feeds that you want to parse. Each URL should be in its own cell, starting from A2.

For this example we can use https://openai.com/blog/rss.xml Unless you have your own to try. There are other options at the end of this lesson but maybe just lets get it to work for you first.

Step 3: Add the Script

Now, let’s add the script that will parse the RSS feeds:

  1. Click on “Extensions” in the menu, then “Apps Script.”
  2. Delete any code in the script editor and replace it with the code provided.
  3. Name your project. This can be anything you like, such as “RSS Parser.”
  4. Click on the disk icon or select “File > Save” to save the script.
function parseFeeds() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var urls = sheet.getRange(2, 1, sheet.getLastRow()).getValues();
  
  // Clear existing content
  sheet.getRange('B2:C' + sheet.getLastRow()).clearContent();

  var maxItemsPerFeed = 5;  // Limit the number of items fetched from each feed

  var data = [];

  for (var i = 0; i < urls.length; i++) {
    var url = urls[i][0];

    if (url && url.startsWith('http')) {
      try {
        var xml = UrlFetchApp.fetch(url).getContentText();
        var document = XmlService.parse(xml);
        var root = document.getRootElement();

        if (root.getName() == 'rss') {
          // Parse as RSS
          data = data.concat(parseRSS(root, maxItemsPerFeed));
        } else if (root.getName() == 'feed') {
          // Parse as Atom
          data = data.concat(parseAtom(root, maxItemsPerFeed));
        }
      } catch (error) {
        console.error('Failed to parse feed at ' + url + ': ' + error.message);
      }
    }
  }
  // Write the data to the sheet, starting at row 2, column 2 (B2)
  sheet.getRange(2, 2, data.length, data[0].length).setValues(data);
}
function parseRSS(rss, maxItems) {
  var channel = rss.getChild('channel');
  var items = channel.getChildren('item');

  var data = [];

  // Loop through each item and extract data, up to maxItems
  for (var i = 0; i < Math.min(items.length, maxItems); i++) {
    var title = items[i].getChildText('title');
    var description = items[i].getChildText('description');

    data.push([title, description]);
  }
  return data;
}
function parseAtom(feed, maxItems) {
  var entries = feed.getChildren('entry');

  var data = [];

  // Loop through each entry and extract data, up to maxItems
  for (var i = 0; i < Math.min(entries.length, maxItems); i++) {
    var title = entries[i].getChildText('title');
    var content = entries[i].getChildText('content');

    data.push([title, content]);
  }
  return data;
}

If you end up having trouble with step 3 and 4 I have provided a sheet that is already setup with the script. You should really try to do it yourself, it’s just copy paste and save! Either way, in case you get stuck you can make a copy of this sheet here, add your RSS feeds and try step 4 again.

Step 4: Run the Script

About the Security Warning

When you first run this script, you’ll see a security warning. This is because the script is newly copied and is now your own unverified version of it. It’s merely facilitating communication between your own accounts but until you verify it you will see the warning, about once a day. It’s fine to proceed it’s safe to use.

To proceed, click on “Advanced” in the warning screen, then “Go to project (unsafe)”. Finally, click “Allow” to activate the script. This allows the script to operate on your data within your own account. If you want take the time to create an app ID and verify the scopes for it. They will have to review the app and verify it. https://console.cloud.google.com/

Finally, let’s run the script:

  1. To run the script, you will have to authorize it first. From the App Script Project Editor, click on the “Select function” drop-down and choose the parseFeeds function.
  2. Click on the play button (▶️) next to the dropdown to run the function.
  3. The first time you run the script, it will ask for permissions to access your Google Sheets and connect to an external service (to fetch the RSS feeds). Click on “Review Permissions,” choose your account, and then click on “Allow.”
  4. Once you’ve done this, run the script again by clicking the play button.

That’s it! The script should now fetch the latest items from each of your RSS feeds and insert them into your Google Sheet. You can refresh the data anytime by running the script again.

There you have it! You’ve successfully parsed your sourced RSS feeds into Google Sheets. This structured data now gives you an accessible view of trending topics. As we’ve demonstrated, even without prior scripting knowledge,

Google Apps Script provides an approachable way to automate data extraction from RSS feeds.

Notes:

  1. The script is set to fetch a maximum of 5 items from each feed. If you want more (careful!) or less, you can change the maxItemsPerFeed variable in the script to your desired number.
  2. If you add, remove, or change RSS feed URLs, just run the script again to update the data in the sheet.

Before we wrap up I’m just going to throw out a bunch of other options for inputs for both RSS and for API. Just so that we understand how many different options we have to source content from.

Absolutely, I can present the same information in paragraph format:

Google Alerts is a tool that can monitor the web for new content of interest. This service allows you to set alerts for specific keywords, and then Google Alerts will send you an email whenever it finds new content—such as webpages, newspaper articles, blogs—that match your search terms. The alerts can also be delivered in the form of an RSS feed.

For an analysis of the popularity of top search queries in Google Search across various regions and languages, you could use Google Trends. The data can be accessed directly through the website or via unofficial APIs and Python libraries like pytrends, which allow you to pull trending searches data.

A similar tool to Google Alerts is Talkwalker Alerts, a free brand monitoring system for beginners and experts alike. With Talkwalker Alerts, you can track mentions of your brand, a hashtag, or a keyword across the web. The updates can be sent directly to your email inbox or as an RSS feed.

In the domain of RSS readers, Inoreader’s Active Search stands out as a useful feature. It allows you to create an RSS feed from a search query within all the feeds Inoreader tracks, which can be incredibly useful for tracking topics across a broad range of sources.

The Microsoft Bing News Search API offers a powerful search experience for news topics. By specifying a topic in the search query, the API will return news articles related to the topic. The search results can be returned in an RSS format.

For monitoring specific communities, Reddit is a great source of information. Reddit is a large online community where users post, vote, and comment on content. Reddit’s API lets you access the site’s vast amount of data, and Reddit also provides an RSS feed of the top posts in any subreddit.

Lastly, the Twitter API is an excellent resource for tracking trends. This API provides a list of trending topics for various geographical locations. The data from Twitter can be used to identify popular topics and then generate content around those.

All these tools provide a unique angle for sourcing trends and can be used independently or combined for a more comprehensive overview. Please remember to check and respect the terms of use for each tool and service.

In our next lesson, we’ll delve into how to use this data to generate engaging content by sending it to the OpenAI API with a prompt. Remember, there are numerous other options for sourcing trending topics, and as you grow more comfortable with this process, you may explore other sources, scripts, or APIs to meet your unique requirements.