// trello variables
var api_key = "1c2891af596cffe63623d7365e0363b8";
var api_token = "7dfbdb0cd68d92d4b575854f8a686455ba210f481e6ba036d864d5936e015f21";
var board_id = "5c98cc2e8591c757ed23cadf"; //https://trello.com/b/cPC4jGzZ/project-manager-sample-board
var sheetName = "Sheet1"
var enableStackdriverLogging = true;
var logingName = "Demo Trello";
/**
* Loads the details from a Trello board using the Trello API v1, into a google sheet.
*
* Each time it is run the sheet gets cleared and the following data are downloaded from the Trello board
*"Date", "Task", "Desc", "Who", "List", "Link", "Labels","Label Colors","Checklists", "Due Date", "Due Complete"
*
* @Param {string} api_key The Trello API Key (Get it from https://trello.com/app-key)
* @Param {string} api_token The Trello API token (Get it from https://trello.com/app-key)
* @Param {string} board_id The Trello Board ID who's cards will be downloaded (Get it from adding ".json" at the end of the board url)
* @Param {string} sheetName The name of the sheet in the active spreadsheet to update it's rows
* @Param {boolean} enableStackdriverLogging True to enable Stackdriver Logging. Default is false
* @Param {string} logingName logging name to be appended in the message. Default is ""
* @return {void} Not applicable.
*/
function loadFromTrello() {
try {
if (enableStackdriverLogging) console.time(logingName + " - loadTrello");
if (enableStackdriverLogging) console.log(logingName + " - Loading from Trello STARTED");
var url = "https://api.trello.com/1/";
var key_and_token = "key="+api_key+"&token="+api_token;
var cr = 2;
// get sheet with name Trello, clear all contents, add titles
var ss = SpreadsheetApp.getActive().getSheetByName(sheetName).clear();
ss.appendRow(["Date", "Task", "Desc", "Who", "List", "Link", "Labels","Label Colors","Checklists", "Due Date", "Due Complete"]);
ss.getRange(1,1,1,11).setFontWeight("Bold");
//Get all lists from Trello API
var response = UrlFetchApp.fetch(url + "boards/" + board_id + "/lists?cards=all&" + key_and_token);
var lists = JSON.parse((response.getContentText()));
// for all lists
for (var i=0; i < lists.length; i++) {
var list = lists[i];
// Get all cards from Trello API
var response = UrlFetchApp.fetch(url + "list/" + list.id + "/cards?" + key_and_token);
var cards = JSON.parse(response.getContentText());
if(!cards) continue;
// for all cards
for (var j=0; j < cards.length; j++) {
var card = cards[j];
//Get all details of card from Trello API
var response = UrlFetchApp.fetch(url + "cards/" + card.id + "/?actions=all&" + key_and_token);
var carddetails = JSON.parse(response.getContentText()).actions;
if(!carddetails) continue;
//Get all checklists of card from Trello API
var response = UrlFetchApp.fetch(url + "cards/" + card.id + "/checklists?action=all&" + key_and_token);
var cardchecklists = JSON.parse(response.getContentText());
var checkliststr = "";
// For all checklists get Name
for (var m=0; m < cardchecklists.length; m++) {
checkliststr = checkliststr + (checkliststr == "" ? "" : "\n\n") + cardchecklists[m].name + "\n --------- \n";
// For all checklists get Items
for (var n=0; n < cardchecklists[m].checkItems.length; n++) {
checkliststr = checkliststr + (checkliststr == "" ? "" : "\n") + (cardchecklists[m].checkItems[n].state == 'complete' ? "[x] ":"[ ] " ) + cardchecklists[m].checkItems[n].name;
}
}
for (var k=0; k < carddetails.length; k++) {
// Get the rest of the card data
var dato = carddetails[k].date;
var fullname = carddetails[k].memberCreator.fullName;
var cell = Utilities.formatDate(carddetails[k].date,new Date(), "GMT+1", "MM/dd/yyyy")
var link = card.shortUrl;
var listname = list.name;
var desc = list.name;
var duedate = card.due;
var duecomplete = (card.dueComplete == true ? 'YES' : 'NO');
var labels = "";
var labelsColors = "";
for (var l=0; l < card.labels.length; l++) {
labels = labels + (labels == "" ? "" : "\n") + card.labels[l].name;
labelsColors = labelsColors + (labelsColors == "" ? "" : "\n") + card.labels[l].color;
}
}
//Append row with data
ss.appendRow([dato, cell, desc, fullname, listname, link, labels, labelsColors, checkliststr, duedate, duecomplete ]);
//change labels color ---
var labelsColor = labelsColors.split('\n');
if (labelsColor[0] == "sky") {
ss.getRange(cr, 8).setBackground("#87CEFA");
} else {
ss.getRange(cr, 8).setBackground(labelsColor[0]);
if ((labelsColor[0] == "red") || (labelsColor[0] == "black") || (labelsColor[0] == "purple") || (labelsColor[0] == "green") || (labelsColor[0] == "blue")) {
ss.getRange(cr, 8).setFontColor("white");
}
}
//change labels color END ---
cr++;
}
}
} catch (e) {
if (enableStackdriverLogging) console.error(logingName + " ERROR: " + e);
} finally {
if (enableStackdriverLogging) console.log(logingName + " - Loading from Trello ENDED");
if (enableStackdriverLogging) console.timeEnd(logingName + " - loadTrello");
}
}
Hi!
What is this script?
Cheers,
Gonchik Tsymzhitov
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.