How to Automate an Up-to-Date List of Blog Posts

I want to show you a quick automation that will help you keep a current list of blog posts in a Google Sheets file.

I get it, it doesn’t sound all that exciting, but having a list of all your blog posts is super-useful. And once you get rolling, it gets harder to keep up with. Automating the process will make sure that when you’re ready to promote a post or or build some social media links.

The inspiration for this comes from Jimmy Rose, who knows all things Zapier. In one of his presentations, he talked about creating and maintaining a list of blog posts with Zapier. My problem was, I already had a significant number of posts that I wanted in a list, and his solution was a start from scratch solution.

Featured photo by Nick Morrison on Unsplash.

Why Bother Automating a List of Blog Posts?

If you don’t have a ton of posts, then it might seem silly that you need a list of posts. But once you get a few written, you’d be surprised how easy it is to forget exactly what you have & where to find it!

Having a sort-able and searchable spreadsheet with a list of blog posts, their URLs, and their categories can make finding & using them much faster and easier than searching through your WordPress dashboard.

In addition, you might think it doesn’t take much time to update your list each time you publish. And you’d be right. But I found that I routinely skipped that step or put it off. Then when I needed a complete list, I had to go back and do the updates before I could move on with my task. Automating the process just makes things easier!

You might want a list of blog posts to:

1. Answer frequently asked questions from your audience.
2. Quickly judge which categories need some more posts.
3. Easily copy and paste URLs when you’re building Pinterest Pins.
4. Save time when you reference your articles in social media posts.
5. Use a list of posts to decide what to feature in ads.
6. Include older posts in your newsletter and keep track of when/what you’ve featured.
7. Add value to nurturing email sequences by including blog posts and keep track of what you’ve sent out.

How to Quickly Make a List of Existing Blog Posts

If you have more than just a few blog posts, you probably want to automate the building of the list of existing posts. I certainly did.

Luckily, the WordPress Plugin Repository has a plugin to help with this, Export All URLs. As of August 2020, the plugin is current and maintained.

The video below walks through the steps, and those steps are listed out below the video for reference.

Steps:

1. Within your WordPress dashboard, go to the Plugins tab.
2. Click on “Add New” and search for “Export All URLs.”
3. When you find it, click on the “Install” button.
4. Once installed, click on the “Activate” button.
5. The plugin will automatically open to a screen with choices. Select what you want in your list, and how you want to get the list. I recommend a .CSV file.
6. Once created, click the download link at the top of your screen.
7. Once downloaded, click the delete link to delete the list from your web server.
8. Upload the .CSV file to Google Drive.
9. Open with Google Sheets. You now have a list of your current posts (or whatever post types you have selected)!
10. For my purposes, I just use 3 columns, Title, URL, and Category. I also recommend changing the name of the Google Sheet to something more descriptive.

This list won’t update beyond what’s in your current list. So you now have to use another method to keep it updated. Keep reading below to learn how.

And, pin this to your automation ideas Pinterest board so you can find it again later!

How to Automatically Update Your List of Blog Posts

Now that you have a current list of blog posts, to keep it up to date, you can keep it up to date automatically using Zapier. I’ve made sure that you can do this with the free plan of Zapier, but I’d bet that you become addicted quickly.

Steps:

1. Add the Zapier plugin to your site.
   1. Within your WordPress dashboard, go to the Plugins tab.
   2. Click on “Add New” and search for “Zapier for WordPress.”
   3. When you find it, click on the “Install” button.
   4. Once installed, click on the “Activate” button.
2. Within WordPress, create a new user for Zapier. Use the “Editor” permissions.
3. Go to Zapier.com and sign up for an account. Log in.
4. Click the “Create a new Zap” button.
5. Choose the application – WordPress.
6. Choose the event that will trigger the Zap – new post.
7. Click Continue.
8. Now, you have to allow Zapier to log into your WordPress site.
   1. Click Add a New Account.
   2. Enter your website’s URL.
   3. Enter the Zapier user id that you created in step 2.
   4. Enter the Zapier password that you created in step 2.
   5. Click Continue.
9. Decide which posts will be added to your list.
   1. Status = published
   2. Type = post
   3. Click the button to test the connection. The connection is good if it pulls data from your most recent blog post.
   4. Click Continue
10. Now it’s time to tell Zapier what to do with this post information by choosing an app and an event.
11. Choose the application – Google Sheets.
12. Choose the event – Create a spreadsheet row.
13. Click Continue.
14. Now, let Zapier log into your Google drive. Allow Zapier to access your account.
15. Select the right location for the new post row.
    1. Select your drive – My Google Drive
    2. Select the name of your spreadsheet
    3. Select the workbook within that spreadsheet
16. Map the columns of your spreadsheet to the data from the blog post. Here are my suggestions based on the spreadsheet we created with the plugin above.
17. Title = Title Rendered (you can see actual data from your connection test next to each blog post data field, which is super-helpful!)
18. ULR = Link
19. Category = Category (In the feed from Zapier, this is the Category ID number. See below to learn how to map to actual category names).
20. Click Continue
21. Click Test and Review to see your Zap in action.
22. When Zapier tells you the Zap was completed x seconds ago, head over to your spreadsheet.
23. You should see a new row with your latest post at the bottom of the data on the spreadsheet!
24. Click Turn on Your Zap!

And there you have it. You no longer have to do this manually, and if it saves you just a tiny bit of time and frustration each week, it’s worth the 20 minutes it takes to set this all up!

How to map category names to ID numbers

1. Log into WordPress
2. Click on the Posts tab
3. Underneath the Posts tab, click on Categories
4. See the list of categories? Hover over the name of a category and click the Edit link that pops up.
5. In the address bar of your browser, the URL will contain the ID for the category. “wp-admin/term.php?taxonomy=category&tag_ID=9&post_type=post”
6. Do this for each of your categories to create a map.

Now you have an automated list of your blog posts that should always be up-to-date!