Created
March 22, 2013 20:51
-
-
Save pdaire/5224638 to your computer and use it in GitHub Desktop.
google script to track time that a card in 'done' spent in a 'doing' list on a trello board.
you need an api key, a token and a google spreadsheet.
you can run this script as frequent as you want using the trigger that google script offer.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function trelloFetch(url, extra_params) { | |
| var key = [your_api_key]; | |
| //how to get a token de https://trello.com/1/connect?key=[your_api_key]&name=[name_for_token]&response_type=token | |
| var token = [your_token]; | |
| var authSufix = "key="+ key + "&token=" + token; | |
| var completeUrl = "https://api.trello.com/1/" + url + "?" + authSufix; | |
| if (extra_params != undefined){ | |
| completeUrl += extra_params; | |
| } | |
| var jsondata = UrlFetchApp.fetch(completeUrl); | |
| //console.log(jsondata); | |
| var object = Utilities.jsonParse(jsondata.getContentText()); | |
| return object; | |
| } | |
| function getCardsFromDone(){ | |
| var list_id = [id_of_your_done_list]; | |
| var url_cards = "lists/" + list_id +"/cards/all"; | |
| var cards = trelloFetch(url_cards); | |
| var card_events; | |
| for (var i=0;i<cards.length; i++){ | |
| var url_cards_events = "cards/" + cards[i].id + "/actions" | |
| card_events = trelloFetch(url_cards_events,"&filter=updateCard,updateCard:desc"); | |
| var active_times = []; | |
| for (j=0; j<card_events.length;j++){ | |
| if (card_events[j].data.listBefore!=undefined && card_events[j].data.listBefore.name == [your_'doing'_list]){ | |
| var tf = card_events[j].date; | |
| continue; | |
| } | |
| if (card_events[j].data.listAfter!=undefined && card_events[j].data.listAfter.name == [your_'doing'_list]){ | |
| var ti = card_events[j].date; | |
| var active_time = {ti: ti, tf: tf}; | |
| active_times.push(active_time); | |
| continue; | |
| } | |
| } | |
| cards[i].active_times = active_times; | |
| continue; | |
| } | |
| var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
| var sheet = ss.getSheets()[0]; | |
| var baserow = 2; | |
| var basecolumn = 1; | |
| var row_counter = 2; | |
| var one_hour=60*60*1000; | |
| for( var i =0;i<cards.length ;i++){ | |
| for(var j=0;j<cards[i].active_times.length;j++){ | |
| var project = cards[i].name.match(/[^\[\]]*(\[.*\])?[^\[\]]*/)[1] | |
| var estimated = 0; | |
| if (cards[i].name.match(/\((\d+)\)/)!=null){ | |
| estimated = cards[i].name.match(/\((\d+)\)/)[1] | |
| } | |
| if (project != undefined){ | |
| sheet.getRange(row_counter, basecolumn).setValue(project.toUpperCase()); | |
| } | |
| sheet.getRange(row_counter, basecolumn+1).setValue(estimated); | |
| sheet.getRange(row_counter, basecolumn+2).setValue(cards[i].name); | |
| var ti = new Date(cards[i].active_times[j].ti); | |
| var tf = new Date(cards[i].active_times[j].tf); | |
| sheet.getRange(row_counter, basecolumn+3).setValue(ti); | |
| sheet.getRange(row_counter, basecolumn+4).setValue(tf); | |
| var diference = (tf.getTime()-ti.getTime())/one_hour; | |
| sheet.getRange(row_counter, basecolumn+5).setValue(diference); | |
| row_counter ++; | |
| //var counter = sheet.getRange(1, 9); | |
| } | |
| } | |
| } |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
it assumes that there is only one member per card, some extra development is required to take in account cards with more members.