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);
    

    log.insertRowAfter(1);
    log.getRange('A2').setValue(id);
    log.getRange('B2').setValue(title);
    log.getRange('C2').setValue(url);
    log.getRange('D2').setValue(words);
    log.getRange('E2').setValue(countUrl);
    log.getRange('F2').setValue(imgs);
    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;
  }else{
   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

3 thoughts on “WP JSON to Google Sheets – Reflective Data

  1. Tom, why ten items? Is this a limit imposed by Google? WP? Could you plausibly do a call of ALL of your posts? I am sure that I am missing something here. Will continue to think about it.

  2. Ten is the default response. You can change the URL parameters up to 99 posts/pages without additional work. The following URL would return 30 posts.

    https://bionicteaching.com/wp-json/wp/v2/posts?_embed&per_page=30

    The limit is imposed because those responses could be quite large and take a long time and/or make your server unhappy. Building pagination or progressive loading of additional posts is pretty straight forward with these parameters as a guide. You can do offsets or pagination based on the per_page count. The original version of the API let you call everything but they changed their mind on v2.

  3. Additionally, in this scenario, you could incrementally (first 10, next 10, etc. etc.) write all the content to the spreadsheet without much additional effort.

Comments are closed.