WP All Import and Google Sheets - Best Buddies, and Worth Mastering

One of, if not my favorite, plugin for WordPress is WP All Import. Specifically, I love that it's able to slurp a Google Spreadsheet into a series of well formatted posts. WP All Import doesn't care if the data was hand entered into a Google Sheet, or if it was entered via a Google Form. The latter allows you to create a crude data entry interface and then publish that data in a WordPress site with minimal effort.

Here, let me show you what I mean.

WP All Import in Action

Suppose I wanted to publish the data collected in this Cool Tools survey. Getting the data into a Google Spreadsheet is trivial. I did that here. And publishing that data so that the world can see it is easy, too:

Jumping over to the WordPress side of things, you can import this data into posts with relative ease:

The above sequence shows entering the download URL, then selecting entry as the element that WP All Import should use to demarcate posts. And finally, you construct the post by dragging and dropping the available fields in to the editor. There are tons of options during the process, so you can attach an image, add categories and tags and choose whether posts should be drafted or published automatically. It takes some time to learn, but with a little practice you can do really clever things without having to write a line of code.

And Now, The Catch

If you were paying attention to the above process, you'll notice that there was one key step I glossed over. That is, where the heck does the download URL come from?

This URL is a special one, and not at all obvious. You can use command line tools to figure it out, but that put this solution out of reach for many users. In short, the entire process of converting a spreadsheet to a series of posts is code-free, *except* for figuring out the download URL. Until now.

The Solution

This question of what a sheet's download URL is, came up often enough that I developed a web page to answer this question. Check it out here (and the source code is here, enjoy!). And here it is, in action:

This web page requires that you enter the public URL to the spreadsheet, in this case https://docs.google.com/spreadsheets/d/1eyDNkv5BfsgzTEQZA5Sprgwj5mjyBhviXX2IN590ux0/edit#gid=563082240, and it does the reset.

I can see from this, that the data URL is: https://spreadsheets.google.com/feeds/list/1eyDNkv5BfsgzTEQZA5Sprgwj5mjyBhviXX2IN590ux0/o9b8tei/public/full.

If your spreadsheet has multiple tabs on it, then each tab will get its own download URL, which the tool will report.

Really, This is Cool

If you haven't considered populating posts from Google Sheets, let me give you one more example. A while back I collected up various testimonials from clients. As they came in, I dropped them into a Google Spreadsheet as that was the natural place to track them. We're working on a website re-design, and I wanted to incorporate those testimonials into a custom post type. Could I have hand entered each testimonial into WordPress? Of course, but it was far faster to just slurp in the sheet full of testimonials. By establishing a unique identifier during the import process, it's even possible to re-import updated data later on. This makes sense for my testimonials, as I can edit these small bits of text far faster in a Google sheet than going one by one in WordPress.

The bottom line: WP All Import rocks, you just need to keep your eyes open for where managing data in a spreadsheet is faster than managing it directly in WordPress.