trello_burndown.gs

//TODO in this script
//1. Add board id in url used in TrelloAPI method (line xx
//2. Add your email in sendChartsByEmail
//3. Add your Trello keys from https://trello.com/1/appKey/generate to authorizeTrello
//4. Setup daily trigger in script editor that runs TrelloAPI()
//5. Run once in google apps script editor to authorize trello for access
//6. Lookup values and aggValues are hardcoded to the labels I use in Trello
//
//In the Trello board use scrum for trello syntax (http://scrumfortrello.com/) 
//Example title: <name of task> (estimate hours) [work delivered in hours]
//
//Google apps spreadsheet template to add this script
//https://docs.google.com/spreadsheet/ccc?key=0AhHvmBJSJ-P3dF9pRHdZVnA1MThjX2loLUZGdWRmWHc&usp=sharing



function TrelloAPI(){
   authorizeToTrello();
  
  var trelloRequestData = {
    "method": "GET",
    "oAuthServiceName": "trello",
    "oAuthUseToken": "always"
  };
  var url= "https://api.trello.com/1/boards/<TODO your trello board>?cards=open&card_fields=name,idList,labels"
  var jsondata = UrlFetchApp.fetch(url, trelloRequestData);
  var object   = JSON.parse(jsondata.getContentText());
  
  //Logger.log(object);
  
  processCards(object);
  sendChartsByEmail();
}

function sendChartsByEmail(){
 var sheet = SpreadsheetApp.getActiveSheet();
 var charts = sheet.getCharts();
 
 var burndownBlob;
 var areaBlob;
  
  if(charts.length==2){
    
    burndownBlob = charts[0].getAs("image/png").setName("burndownBlob");
    areaBlob = charts[1].getBlob().setName("areaBlob");
    
    MailApp.sendEmail({
     to: "<TODO your email>",
     subject: "Status",
     htmlBody: "Burndown<br><img src='cid:burndown'><br>" +
               "Area trend<br>s<img src='cid:area'>",
     inlineImages:
       {
         burndown: burndownBlob,
         area: areaBlob
       }
   });
    
  }
  
 
}

function processCards(trelloResponse){
  var cards = trelloResponse.cards;
  var aggValues = {tasks_sum:0, tasks_remaining:0,enterprise_sum:0,enterprise_remaining:0,general_sum:0,general_remaining:0,temp_sum:0,temp_remaining:0,mass_sum:0,mass_remaining:0,nice_to_have_sum:0,nice_to_have_remaining:0, nolabel_sum:0, nolabel_remaining:0};
  
  //map between trello label name and aggValues property names
  var lookupAttributes = {
    "General": function(card){
      return ["general_sum", "general_remaining"];
    },
    "Enterprise": function(card){
      return ["enterprise_sum", "enterprise_remaining"];
    },
    "Temporary": function(card){
      return ["temp_sum", "temp_remaining"];
    },
    "Mass update": function(card){
      return ["mass_sum", "mass_remaining"];
    },
    "Nice to have": function(card){
      return ["nice_to_have_sum", "nice_to_have_remaining"];
    },"nolabel": function(card){
      return ["nolabel_sum", "nolabel_remaining"];
    },"": function(card){
      return ["nolabel_sum", "nolabel_remaining"];
    },
    
  }
  
  //let's add estimate,remaining and label
  for(var i=0; i< cards.length; i++){
    var card = cards[i];
    card.label="nolabel";
    card.estimate= getEstimateFromTrelloString(card.name);
    card.remaining = getRemainingFromTrelloString(card.name);
    try{
      card.label=card.labels[0].name;
    }catch(ex){
      Logger.log("Found no label" + card);
    }
    
    
    //add to overall sum
    aggValues["tasks_sum"] += card.estimate;
    aggValues["tasks_remaining"] += card.remaining;
    
    //add to trello label specific sum
    try {
      
      var aggAttributes = lookupAttributes[card.label]();
      if(aggAttributes!=null && aggAttributes.length==2){
        aggValues[aggAttributes[0]] += card.estimate;
        aggValues[aggAttributes[1]] += card.remaining;
      }
    }catch (ex){
     Logger.log(ex + "Failed to lookup trello label") ;
    }
    
  }
  
  Logger.log(aggValues);
  
  writeObjectsToSpreasheet(aggValues);
}

function writeObjectsToSpreasheet(aggValues){
  var today = new Date();
  today.setHours(0,0,0,0);
  //output structure
  var rowValues = [[today,"" , aggValues.tasks_remaining,aggValues.tasks_sum,aggValues.enterprise_sum,aggValues.enterprise_remaining,aggValues.temp_sum,aggValues.temp_remaining,aggValues.mass_sum,aggValues.mass_remaining,aggValues.general_sum,aggValues.general_remaining,aggValues.nice_to_have_sum,aggValues.nice_to_have_remaining,aggValues.nolabel_sum,aggValues.nolabel_remaining]];
  
  Logger.log(rowValues);
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  
  
  Logger.log("Last row " + lastRow + " today " + today);
  var data_write = sheet.getRange(lastRow+1, 1, 1, rowValues[0].length).setValues(rowValues);
}


function getEstimateFromTrelloString(title){
 var regex = /\(([0-9]+)\)/;
 var matches = title.match(regex);
  if(matches != null){
    return Number(matches[1]);
  }else {
    return 0;
  }
}

function getUsedFromTrelloString(title){
  var regex = /\[([0-9]+)\]/;
  var matches = title.match(regex);
  if(matches != null){
    return Number(matches[1]);
  }else {
    return 0;
  }
}

function getRemainingFromTrelloString(title){
  var estimate = getEstimateFromTrelloString(title);
  if(estimate!=0){
    var used = getUsedFromTrelloString(title);
    return estimate-used;
  }else {
    return 0;
  }
  
}

function authorizeToTrello() {
  var oauthConfig = UrlFetchApp.addOAuthService("trello");
  oauthConfig.setAccessTokenUrl("https://trello.com/1/OAuthGetAccessToken");
  oauthConfig.setRequestTokenUrl("https://trello.com/1/OAuthGetRequestToken");
  oauthConfig.setAuthorizationUrl("https://trello.com/1/OAuthAuthorizeToken");

  // Replace these with the values you get from 
  // https://trello.com/1/appKey/generate
  oauthConfig.setConsumerKey("<TODO KEY>");
  oauthConfig.setConsumerSecret("<TODO KEY>");
}


function testGetEstimateFromTrelloString(text){
 var estimate = getEstimateFromTrelloString(text);
  Logger.log("Estimate:" + estimate);
}

function testGetUsedFromTrelloString(text){
 var used = getUsedFromTrelloString(text);
  Logger.log("Used:" + used);
}

function testGeRemainingFromTrelloString(text){
 var remaining = getRemainingFromTrelloString(text);
  Logger.log("Remaining:" + remaining);
}

function test(){
  var testTitle1 = "This is a task description with 3 hours estimate,2 hours done and 1 hour remaining  (3) [2]";
  testGetEstimateFromTrelloString(testTitle1);
  testGetUsedFromTrelloString(testTitle1);
  testGeRemainingFromTrelloString(testTitle1);

  var testTitle2 = "title with nothing in it";
  testGetEstimateFromTrelloString(testTitle2);
  testGetUsedFromTrelloString(testTitle2);
  testGeRemainingFromTrelloString(testTitle2);  
}