Get post data from Blogger into a google Spreadsheet with the Google Blogger API

If you use blogger.com you may want to get some data about your posts into a spreadsheet for analysis. For example, the d3 concept browser analyses the Excel Liberation site and blog each night. There is a blogger API which makes this very straightforward. As well as the blogger API, for convenience,  I’m using various functions such as sheet caching and using scriptDB as a lockbox from the mcpher library which you can include in your code.

In this case, while i was researching the half life of blog posts, I needed to get list of all my posts and the date they were published. The final output starts like this

The code

function onOpen() {
  var ss = SpreadsheetApp.getActive();
  var items = [
    {name: 'Get Blog details', functionName: 'getPostDetails'}
  ];
  ss.addMenu('Blog details', items);
}



function getPostDetails() {
  var url = initBlogger ("excelRamblings");
  var results = getBlogResults(url);
  var cache = mcpher.sheetCache(SpreadsheetApp.getActiveSpreadsheet().getActiveSheet());
  cache.clearCache().extend(results.length,2);
  for (var i = 0; i < results.length; i++) {
    cache.setValue(results[i].published,i+2,1);
    cache.setValue(results[i].title,i+2,2);
  }
  cache.setValue('Published',1,1);
  cache.setValue('Title',1,2);
  cache.close();
}
function initBlogger(source) {
  // this will get my secret stuff for accessing the blogger
  var bloggerDetails = getApiKey("bloggerapi");
  // now I can get all the posts
  return "https://www.googleapis.com/blogger/v3/blogs/" + 
    bloggerDetails + "/posts?key=" + 
    bloggerDetails.consumerKey;

}
function getBlogResults(url) {
  // this only returns a small number of posts, so we need to keep going
    var cb = new mcpher.cBrowser();
    var p, items=[];
    
    while (!p || p.nextPageToken) {
      p = JSON.parse(cb.get(url + ( p ? '&pageToken='+p.nextPageToken : '')));
      if (p && p.items) items = items.concat (p.items);
      if (!p) break;
    }
    return items;
}


function getApiKey(api) {
  // my private store
  var myKey = mcpher.getMyStuff(api, myStuff.myStuffDb());
  if (myKey.status.code == "good") {
    return myKey.result.myStuff;
  }
  else {
    mcpher.MsgBox("unable to find your key for " + api);
    return null;
  }
}