Project progress tracking with Google Sheets and Trello

TrelloProjectTracking.js

// Original code and guide from Kevin Pelgrims
// (http://kevinpelgrims.com/blog/2012/03/06/project-progress-tracking-with-google-docs-and-trello)
// Create a new Google Sheet and add this code in Tools-->Script editor
// Then create trigger (under Resources menu) to run automatically, if you want

// Main function. Call this first (and from any triggers).
// (Revised functionality provided by anonymous commenter)
function getTrelloListCounts() {
  var data;
  //topBuffer is the distance from the top of the document to where we want to
  //start populating the document with data.
  var topBuffer = 4;
  var leftBuffer = 1;
  //identify the sheet we are going to write on (referenced by name)
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  //This routine looks for an empty row.
  var counter = topBuffer + 1;
  for (; sheet.getRange(counter, leftBuffer).getValue() != 0; counter++){}
  //These variables control where the data will be placed.
  var baserow = counter;
  var basecolumn = 2;
  //Place the current date into the first cell of this data row
  sheet.getRange(baserow, basecolumn - 1).setValue(new Date());
  //if this is our first entry into the spreadsheet, we should also put the
  //names of the columns at top of the columns. Either way, also print the
  //data retrieved from trello.
  if (counter == topBuffer + 1) {
    data = getBoardListCount(true);
    sheet.getRange(baserow - 1, basecolumn, 2, data[0].length).setValues(data);
  }
  else {
    data = getBoardListCount(false);
    sheet.getRange(baserow, basecolumn, 1, data[0].length).setValues(data);
  }
}


function getBoardListCount(isFirstRun) {
  var boardid = "<your board ID here>"; // board ID is shown at the top of the Trello board JSON export
  var url_lists = "boards/" + boardid +"/lists";
  var trello_lists = trelloFetch(url_lists); // go get JSON data

  var lists = [];
  var listnames = [];
  var listcounts = [];

  for (var i = 0; i < trello_lists.length; i++) {
    var listid = trello_lists[i].id;
    var url_cards = "lists/" + listid +"/cards";
    var trello_cards = trelloFetch(url_cards);
    var count = 0;

    for (var j = 0; j < trello_cards.length; j++) {
      count++;
    }

    listnames[i] = trello_lists[i].name;
    listcounts[i] = count;
  }

  if (isFirstRun) {
    lists[0] = listnames;
    lists[1] = listcounts;
  }
  else {
    lists[0] = listcounts;
  }

  return lists;  
}

// Gets JSON data from Trello API
function trelloFetch(url) {
  // Your developer key
  // see https://trello.com/1/appKey/generate
  var key = "<your developer key here>";
  
  // Unique token needs generating to allow access from application
  // see https://trello.com/docs/gettingstarted/index.html#getting-an-application-key
  var token = "<your application token here>";
  
  var completeUrl = "https://api.trello.com/1/" + url + "?key=" + key + "&token=" + token;
  var jsondata = UrlFetchApp.fetch(completeUrl);
  var object = Utilities.jsonParse(jsondata.getContentText());
  return object;
}