Recently, I found myself setting up a new WordPress site, but with an enormous legacy of content that needed to be uploaded in the form of posts and custom post types. We had about 1500 posts just in one category and a few hundred posts under different custom post types like ‘videos’ and ‘events’.

To make it even crazier, the site itself once ran on WordPress, then was transferred over to a new platform, NationBuilder, during a redesign by an outside agency, and we were finding our selves missing WordPress. NationBuilder seemed like it could be a great platform for driving engagement and fundraising, but we decided we’d rather take advantage of the WordPress’s ability to handle massive amounts of content and the potential of the WP Rest API to connect that data to our mobile app, instead of having all of our content stored in different areas. Perhaps I’ll review my experiences with NationBuilder and what led us back to WordPress in another post.

So that left us with disparate patches of data. I could have gone back to our old backups of WordPress and just worked on top of those, importing them via the WordPress importer, but I decided that I really wanted to start fresh. Also, it gave me a chance to rearrange some of the data into custom post types with custom fields, which I could then access more cleanly via the Rest API. I discuss how that all works in another series on this site, Super WP Theming. Needless to say, starting fresh with mountains of archives to sort through is both overwhelming and freeing at the same time.

Alternative Methods and Warnings

In order to massively import content into WordPress, there are a number of ways to go. I’ve had experience pasting the data into spreadsheets and importing the CSV files with plugins like WP Ultimate CSV Importer and WP CSV. This is definitely a viable option. The downsides are that you end up doing a lot of copy/pasting into a CSV file that has a LOT of columns. More than once, I’ve made a few mistakes dealing with such a WIDE file.

It goes without saying, but another method is to simply manually ‘Add Post’ over and over. If you’re dealing with less than a dozen posts, this is probably the quickest way to go.

Just to be clear, anytime we’re dealing with massive amounts of importing/exporting, there are risks involved. Do not do this on a production site. Ultimately, we’ll be creating a file that will be automatically flooding our database with information, this should ideally be done on a development site, preferably on your local machine, before your transfer that data to a live site.

For example, I’m setting up a site from scratch locally with ServerPress, then I’ll probably transfer the completed site over with All-in-One WP Migration, which happens to be my favorite plugin to transfer a site wholesale to a new installation.

Another warning is that this post does not discuss uploading any media attachments. In order to add something like a post thumbnail, you’ll need to either be dealing with images already uploaded into the media gallery or you’ll need to write a mechanism for uploading the images, both of which are outside of the scope of this article.

I can say that if I were to choose a method, I’d probably upload the files to the media gallery through the WP Admin, add the media file IDs as a new column in our list of posts, and then use set_post_thumbnail() (link) while doing the wp_insert_post() (link) process. Here’s a post on StackOverflow that discusses this further.

As programmers, we love creating automated systems that solve tedious and time-consuming problems, so I was excited the first time I saw this actually work for me. Without further ado, let’s discuss the magic that is wp_insert_post().

From Spreadsheet to JSON Feed

If you have your data as a JSON feed or file already, skip to Step 2 below.

The basic outline is that we’re taking a JSON object and turning it into a PHP array. Then we’re simply looping through that array and calling wp_insert_post() to let WordPress generate the post (and all associated metadata) for us.

We’re working with JSON because it’s popular and universal. If you’re data is currently accessible via an API, you probably have it as JSON already. If you’re a developer and you want to write a bunch of organized data quickly, you’ll probably use JSON. And if you have all of the data in a spreadsheets, you can use Google Sheets to export that data as a JSON object. That third method is what we’ll be using for our example.

1. Create a Google Sheet

Start by creating a new Google Sheet, or spreadsheet in your Google Drive. If you aren’t using Google Drive, it’s already included if you use gmail or you’ll need to set up a free account here.

Use the first row to create labels for each column, along the lines of title, content, and any meta fields you might have. Keep your column titles simple, since we’ll be using them soon enough.

Then paste in your information.

I haven’t tried this above a few dozen rows. For me, I’d rather paste in no more than 100 rows, run the script, paste the next 100 rows, etc, so that I can keep testing for success in between batches. Just seems safer to me.

2. Publish Sheet

In order to convert the Sheet to JSON, you’ll need to choose File > Publish to the Web... then select Publish. It should be noted that this makes your data technically publicly available, though not editable.

3. Determine the URL of the JSON Feed

Google doesn’t advertise that their spreadsheets, when published, are available in JSON online, so you won’t see the link anywhere. Luckily, you just need to paste the unique ID of your Sheet into the url below. You can find it in the link pictured – its the long string of characters between /d/ … and … /pubhtml. (You can also find it in the browser, in url of the google sheet itself between /d/ … and … /edit.)

Get that unique ID and add it to the URL below:

https://spreadsheets.google.com/feeds/list/{{UNIQUE-ID}}/od6/public/values?alt=json

Try pasting your URL into the browser to see if it works! I use a Chrome extension called JSONView that nicely formats the data when I look at a JSON file in the browser. Feel free to ignore most of the meta information; what were interested in is under feed followed by entry. This will become more apparent as we work with our retrieved data.

Note: If you’d like, you can save this file to your computer to access it locally. Our example is based on using the feed from our Google Sheet to create our posts, but you can view an alternate example of working from a local file, presumably uploaded as a resource inside of our theme. Otherwise, keep that URL handy.

From JSON Feed to PHP Variables

This is where the magic happens. At this point we’re going to be getting quick and dirty, so I’ll reiterate: don’t do this on a public production site. What we’re basically doing is creating a custom page template, let’s call it page-insert-from-google-sheets.php, and then saving it inside of our theme.

In WP-Admin, create a new page, assign this template to that page, and publish it with an obscure slug. Every time we load that page, this script will automatically run. Because we’re working locally, we can tweak our template file and reload the page as we fine tune our results.

We could certainly hide this functionality behind a function, intentionally called via clicking a button, and secured behind an admin login, but that’s outside of the scope of this tutorial.

Let’s take a look at that template we’ll be making:

1. Preview Mode First

The first thing we need to do is set a default testing mode. This allows us to preview the result of our query (as seen in lines 14 - 28) before actually calling the wp_insert_post() function. We declare our variable $test = true; to run through a test loop that will output the results onto the screen for us. Once we’re satisfied with what comes out, we set $test = false; and get our real results.

2. Understanding Sheets’ Unusual JSON Names

If you have a chance to explore the JSON created by Google Sheets, you’ll notice that it’s a complex mess of layers and labels. Essentially, it uses the first row of your table to generate the properties, labeled as such: gsx${{yourcolumnname}} so that my column named ‘Title’ becomes gsx$title. Then it puts your data into a property of that property called '$t'.

So in our loop, we’re navigating two levels deep into our feed: $array[feed][entry] and iterating through each entry that we’re naming $row. Then we can find all of our data as properties of $row, such as $row['gsx$title']['$t'] and $row['gsx$content']['$t'].

Note: As you can see, we’re including quotes around any of the property names that include a $ so as not to confuse our PHP processes.

3. Running our Loop for Real

When you’re satisfied with what comes out, set $test = false; and reload the page. When the page finishes loading, congratulations! Your posts have just been uploaded all at once!

Check the backend for your posts. Because we haven’t specified a post_status, these posts were all uploaded as drafts, which allows us the opportunity to delete them or select them all and Bulk Edit them to publish them. If you’d like, you can certainly set your posts to publish through the wp_insert_post() function. In fact, you can set just about anything you want with that function.

The Possibilities of wp_insert_post()

This tutorial only scratches the surface of how we can automate data entry into our WordPress installations. Since WordPress 4.4, you can now include custom meta and taxonomy information, making this the perfect method to work hand-in-hand with Custom Post Types and CMB2, If that appeals to you, check out my free ongoing series that covers those topics and more called Super Custom WP.