WordPress – Comments to Spreadsheet

Image from page 981 of “A system of instruction in X-ray methods and medical uses of light, hot-air, vibration and high-frequency currents : a pictorial system of teaching by clinical instruction plates with explanatory text : a series of photographic cli flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons)

This is a bit odd but if you work in education it’s quite possible it’ll come up . . .

Sometimes people just want the blog comments in a nice friendly CSV file. That might be for grading, it might be for further analysis in Voyant or something like that. What I need to do is write a little plugin for the export like Alan did for posts but until I get around to that I took advantage of another plugin Alan wrote to expand comments to the 100 most recent comments.

So since I have lots of comments available in the feed, I can just use the =IMPORTXML function to port right into Google Sheets.

There’s probably a smart way to parse out multiple XML fields at a time but this seems to work ok. All the functions I used are broken down in the sheet embedded below. Most are simply some version of IMPORTXML(b1,”//title”). B1 is the URL for the feed and the second variable is the element you want. I’m using comments from the front page of rampages for this example so the URL is https://rampages.us/comments/feed/.

That’s all fine and good in most cases and it’ll work for most of the main elements. What you’ll notice when trying to get to some elements is that they’re named with a colon-

<dc:creator><![CDATA[Tom Woodward]]></dc:creator></span>

. This means that the XML is in a different name space. There may be a decent way to deal with namespaces in Google Sheets (without resorting to scripting) but I didn’t figure it out right away so I cheated and just used a function to parse the name from data I could get. You can see that in column F.

Comments on this post

  1. CogDog said on January 23, 2017 at 11:51 am

    Looks like a nice way to do a comment viewer. I still like having them all bundled in an RSS Viewer. I need it for my new class, and will see if I can resurrect a script I did for Thought Vectors that goes through the feeds in a given category, and if the sources are wordpress or blogger (the platforms that process the feeds) to generate an OPML for comment feeds.

    • Tom Woodward said on January 24, 2017 at 3:08 pm

      I don’t think the intent here is to read them in this view. Their goal was to have an archive to throw into stats/data viz stuff.