Skip to content

Instantly share code, notes, and snippets.

@elsewhat
Created December 12, 2013 14:03
Show Gist options
  • Save elsewhat/7928402 to your computer and use it in GitHub Desktop.
Save elsewhat/7928402 to your computer and use it in GitHub Desktop.
Google apps script for generating daily burndown chart from trello tasks. Sends it by email (set up a trigger)
//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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment