WP JSON to Google Sheets – Reflective Data

Image from page 86 of “Refraction and motility of the eye, with chapters on color blindness and the field of vision” (1920) flickr photo by Internet Archive Book Images shared with no copyright restriction (Flickr Commons)

Way back in 2015, I wrote a little plugin1 to count URLs, get the word count and do other stuff so I could reflect on my blog posts.

Given some (k)new knowledge2, I figured I could make a version that runs in Google Sheets and indeed I can. The reason I like this as an alternative to the plugin is that it works for anyone who has access to Google Sheets even if they can’t install plugins. Google Sheets also offers a lower barrier to messing with your own data once you get start capturing it. You can count the !s, or a variety of emoticons, or how often you use the word “spaces,” or whatever you want- all without the ability to program in php or javascript.

I think it starts to open up different doors for students3 to gather their own kind of data for reflection and amusement. It starts to get at the DIY ethos inherent in the quantified self communities.

The sheet is here. I’m going to build it out into something a bit more robust and plug/play in the near future but figured I’d throw this up in the interest of transparency.

//builds the URL based on the base URL in A1 on a sheet named URL 
function getJson() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('URL');
  var url = sheet.getRange('A1').getValue();
  var feed = url +'/wp-json/wp/v2/posts/';
  var fetch =  UrlFetchApp.fetch(feed);
  var data = JSON.parse(fetch.getContentText());

//writes data to a sheet named data
  var log = ss.getSheetByName('data');
  var row = log.getLastRow();
  var len = data.length;
  var i = 0;
  while (i < len){
    var id = theId(data,i);
    var title = theName(data,i);
    var url = theUrl(data,i);
    var date = theDate(data,i);    
    var countUrl = countUrls(data,i);    
    var words = countWords(data,i);  
    var imgs = countImgs(data,i);


function theId(data, i){
  var id = data[i].id;
  return id;

function theName(data, i){
  var title = data[i].title.rendered;
  return title;

function theUrl(data, i){
  var url = data[i].link;
  return url;

function theDate(data, i){
  var date = data[i].date;
  return date;

function countUrls(data, i){
  var data = data[i].content.rendered;
  var count = data.match(/(href="http:|href="https:)+/g).length;
  return count;

function countWords(data, i){
  var data = data[i].content.rendered;
  var count = data.split(' ').length;
  return count;

//should probably do this if false stuff for all of them . . . 
function countImgs(data, i){
  var data = data[i].content.rendered;
  var count = data.match(/(<img )+/g);
  if (count) {
  return count.length;
   return 0; 

1 and a blog post or I’d never be able to find it

2 Kickin’ the new k-nowledge . . . an MC to a degree that you can’t get in college

3 and other humans

Comments on this post

No comments.

Leave a Reply

Trackbacks and Pingbacks on this post

No trackbacks.

TrackBack URL