Just wanted to share this development journey with you trying to build a template. I wanted to create a WordPress connector for DataShopTalk so that I could download all the articles and get a good idea of the content of the blog. Connecting directly to the MySQL database was not really ideal, I wanted to setup an API from WordPress so that a data function could connect to it.

Here’s a high-level list of the things we need:

  • A REST API opened up on datashoptalk.com that spits out JSONs.
  • A data function that can send authenticated GET requests to the REST API and can parse JSON content.
  • A Spotfire file which hosts the data function and the inputs for credentials

REST API

First I needed an exposed API on the WordPress site. I setup the plugin WP REST API so that I could get JSON results back from an authenticated query. Here is the developer handbook for connecting to the interface: http://v2.wp-api.org/.

Today we’re only going to grab a list of posts from the API. Theoretically, this API is two-directional, so you could post to WordPress using Spotfire. However let’s look at the posts endpoint:

$ curl -X OPTIONS -i http://demo.wp-api.org/wp-json/wp/v2/posts

This is what we are given to connect to the API to retrieve a list of posts. This will be enough to get a list and then we can find out the author, the status, and the date. Here are the objects we’ll be interested in:

date
string, datetime (ISO8601)
The date the object was published, in the site’s timezone.
Context: vieweditembed
status
string
A named status for the object.
Context: edit
One of: publishfuturedraftpendingprivate
title
object
The title for the object.
Context: vieweditembed
author
integer
The ID for the author of the object.
Context: vieweditembed

Source: https://developer.wordpress.org/rest-api/reference/posts/#retrieve-a-post

There is plenty more to explore in that schema so take a look yourself.

Data Function

First, the script:

library(httr)
library(jsonlite)
fullDomain <- paste("http://",dfDomain,"/wp-json/wp/v2/posts",sep="")
fullCreds <- paste("Basic",dfCreds,sep="")
r <- GET(fullDomain, add_headers(Authentication = fullCreds,per_page = 200))

document <- fromJSON(content(r,"text"))
result <- cbind(title=document$title$rendered, date=document$date, author=document$author, status=document$status)

Ok, so here we are importing httr. We are going to use httr to make the request to the server. We have one hitch though, we need to be able for users in Spotfire to input their credentials so they can log into the server. Furthermore, using httr with Basic Authentication requires you to encode your credentials in Base64. To get this to work we need to pass it something that looks like this: “BasicYWRtaW46dGVzdHRlc3Q=” which is an encoded version of “username:password”. Here is an example of the inputs in Spotfire:

To encode the credentials in Base64 was a doozy. I couldn’t figure out how to make R’s base64enc package do it. That meant we would have to encode the credentials before the data function, we’ll have to do that in Spotfire.

Spotfire

First I tried using IronPython, because the document properties have an OnChange functionality that I could leverage. (Everytime the username or password input was changed, the base64 encoded credentials would update). To my chagrin, the package for base64 in Python was not available. The last resort was JavaScript which had a Unicode to Base64 method available.

Here is the full HTML for the configuration text box in Spotfire:

</pre>
<script>

$( "#01156c1564f940f796fbd615907385b2" ).keyup(encodeCreds);

$( "#38a171fba4c9495bafb8f14bee175437" ).keyup(encodeCreds);

function encodeCreds() {

dfUserName = $( "#01156c1564f940f796fbd615907385b2" ).val()

dfPassword = $( "#38a171fba4c9495bafb8f14bee175437" ).val()

value = dfUserName + ":" + dfPassword

$("#e330bf78c7854461be588606939b26e3").val(btoa(value));

$("#e330bf78c7854461be588606939b26e3").keyup();

}

</script>

<h2>Wordpress Connector</h2>

<p>This data function uses the wp-json plugin for WordPress to connect to posts and other objects on the WordPress platform. Use the document properties below to connect to your WordPress instance.</p>

<p>WP REST API Documentation: <a href="http://v2.wp-api.org/">http://v2.wp-api.org/</a>.</p>

<p>Requirements:</p>

<ul><li>R package: httr</li><li>R package: jsonlite</li></ul>

<p>You can install these from the menu above via Tools > TERR Tools > Package Management.</p>

<h2>Configure</h2>

<p><b>Wordpress domain:</b><br><i>http://<SpotfireControl id="6e874f0b35a1414a91d5f511ea50a912" />/wp-json/wp/v2/posts</i></p>

<p><b>Username:</b> <SpotfireControl id="01156c1564f940f796fbd615907385b2" /></p>

<p><b>Password:</b> <SpotfireControl id="38a171fba4c9495bafb8f14bee175437" /></p>

<p><b>Encoded credentials:</b><br>

<SpotfireControl id="e330bf78c7854461be588606939b26e3" /></p>
<pre><p><SpotfireControl id="9147430e218540088d0e588c778e5fae" /></p>

Let me know in the comments if you have questions! Happy to elaborate on any of the components.

Written by Lucas Wood
I am unable to explain to my family and friends what I do for a living. ---DataShopTalk, editor. Exchange.ai, admin. Ruths.ai, data analyst.