An example of using Trello API to fill data in Google Sheets

trello2sheets.gs

/** CONSTANTS **/
var BOARD_ID = "XXX";
var APP_KEY = "XXX";
var USER_TOKEN = "XXX";
var NO_LABEL_CATEGORY_NAME = 'No label'
var LIST_2_ID = "XXX"
var LIST_1_ID = "XXX"

// Add a new menu to trigger the sheet update
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Update data', functionName: 'updateDataMenuItemOnClickHandler'}
  ];
  spreadsheet.addMenu('Trello 2 Sheets Example', menuItems);
}

function updateDataMenuItemOnClickHandler() {
  var products = getProductsFromTrello();
  
  var categories = groupProductsByCategory(products);
  
  drawProductSheets(categories);
  
  date = new Date()
  
  drawOverview(products);
}

function drawOverview(products) {
  var spreadsheet = SpreadsheetApp.getActive();
  
  var overviewSheet = spreadsheet.getSheetByName("Overview");
  
  var lists = groupBy(products, function(product){ return product.list; });
  
  var provisionTotalSum = 0;
  var provisionCompletedSum = 0;
  
  var migrationTotalSum = 0;
  var migrationCompletedSum = 0;
  
  for(var i in products) {
    var product = products[i];
    
    for(var j in product.objectives) {
      var objective = product.objectives[j];
      
      if(objective.name === "Migration") {
        migrationTotalSum += objective.total;
        migrationCompletedSum += objective.completed;
      } else {
        provisionTotalSum += objective.total;
        provisionCompletedSum += objective.completed; 
      }
    }
  }
  
  var rowData = [
    date.getDate()+"/"+(date.getMonth()+1)+"/"+date.getFullYear(),
    provisionCompletedSum + "/" + provisionTotalSum,
    Math.round(provisionCompletedSum * 100 / provisionTotalSum)+'%',
    migrationCompletedSum + "/" + migrationTotalSum,
    Math.round(migrationCompletedSum * 100 / migrationTotalSum)+'%'
  ]
  var relevantLists = [
    LIST_1_ID, 
    LIST_2_ID
  ]
  for(var i = 0; i < relevantLists.length; i++) {
    var listId = relevantLists[i];
    rowData.push(lists[listId] ? lists[listId].length : 0);
  }
  
  overviewSheet.appendRow(rowData)
}

function drawProductSheets(categories) {
  var spreadsheet = SpreadsheetApp.getActive();
  
  for(var categoryName in categories){
    var sheet = spreadsheet.getSheetByName(categoryName);
    if (sheet) {
      sheet.clear();
    } else {
      sheet = spreadsheet.insertSheet(categoryName, spreadsheet.getNumSheets());
      if (categoryName !== NO_LABEL_CATEGORY_NAME) {
        var category = categories[categoryName][0].category;
        sheet.setTabColor(category.color);
      }
    }
    
    drawProductsOnSheet(sheet, categories[categoryName]);
  }
}

function drawProductsOnSheet(sheet, products) {
  var nextColumnIndex = 1;
  
  for(var i in products) {
    var product = products[i];

    columnData = [[product.name, ""]]
    
    var totalSum = 0;
    var completedSum = 0;
    for(var j in product.objectives) {
        var objective = product.objectives[j];
        totalSum += objective.total;
        completedSum += objective.completed;
    }
    
    columnData.push([completedSum+'/'+totalSum, ""])
    
    for(var j in product.objectives){
      var objective = product.objectives[j];
      columnData.push([objective.name, objective.completed+'/'+objective.total]);
    }
    
    range = sheet.getRange(1, nextColumnIndex, columnData.length, 2);
    range.setValues(columnData).setBorder(true, true, true, true, false, false);
    
    sheet.getRange(1, nextColumnIndex, 1, 2).merge().setHorizontalAlignment("center");
    sheet.getRange(2, nextColumnIndex, 1, 2).merge().setHorizontalAlignment("center");
    sheet.getRange(3, nextColumnIndex+1, columnData.length-2, 1).setHorizontalAlignment("center");
    
    sheet.autoResizeColumns(nextColumnIndex, 2);
    
    sheet.getRange(2, nextColumnIndex, 1, 2).setBackground(getColorForObjective(completedSum, totalSum))
    
    var objectiveNum = 0;
    for(var j in product.objectives){
      var objective = product.objectives[j];
      sheet.getRange(3+objectiveNum, nextColumnIndex+1, 1, 1).setBackground(getColorForObjective(objective.completed, objective.total));
      columnData.push([objective.name, objective.completed+'/'+objective.total]);
      objectiveNum += 1;
    }
    
    sheet.setHiddenGridlines(true);
    
    nextColumnIndex += 2;
  }
}

function getColorForObjective(completed, total) {
  if (completed === total) {
    return "#8fff62";
  } else if (completed > 0) {
    return "#33c4c4";
  } else {
    return "#d9d9d9"
  }
}

function groupProductsByCategory(products) {
  var categories = groupBy(products, function(product){ 
    if(product.category) return product.category.name;
    return NO_LABEL_CATEGORY_NAME;
  })
  
  return categories;
}

function groupBy(list, getKeyCallback) {
  var result = {};
  
  for(var i = 0; i < list.length; i++) {
    var item = list[i];
    var key = getKeyCallback(item);
    if(!result[key]) result[key] = []
    
    result[key].push(item)
  }
  
  return result;
}

function getProductsFromTrello(){

  var completeUrl = "https://api.trello.com/1/boards/" + BOARD_ID + "/cards?key=" + APP_KEY + "&token=" + USER_TOKEN + "&filter=visible&fields=name,idList,labels&checklists=all";

  var jsondata = UrlFetchApp.fetch(completeUrl);
  var cards = JSON.parse(jsondata.getContentText());

  var productCards = cards.filter(filterProductCards)
  
  validateCardLabels(productCards)
  
  var products = productCards.map(mapCardToProduct)
  
  // Logger.log(products[0])
  
  return products;
}

function validateCardLabels(cards) {
  for(var i in cards) {
    var card = cards[i];
    var foundLabelWithColor = false;
    
    for(var j in card.labels) {
      var label = card.labels[j];
      if (label.color !== null) {
        if(foundLabelWithColor) {
          var message = Utilities.formatString('Card "%s" is not valid because it has multiple labels with color.', card.name);
          Browser.msgBox('Error', message, Browser.Buttons.OK);
          throw message;
        }
        
        foundLabelWithColor = true;
      }
    }
    
    if(!foundLabelWithColor) {
      var message = Utilities.formatString('Card "%s" is not valid because it has no labels with color.', card.name);
      Browser.msgBox('Error', message, Browser.Buttons.OK);
      throw message;
    }
  }
}

function filterProductCards(card) {
  return card.idList != LIST_1_ID;
}

function mapCardToProduct(card) {
  
  var cardNameParts = card.name.split(' - ')
  if(cardNameParts.length != 2 || cardNameParts[0].trim() == "" || cardNameParts[1].trim() == "") {
    var teamName = 'Unknown';
    var productName = card.name;
  } else {
    var teamName = cardNameParts[0];
    var productName = cardNameParts[1];
  }
  
  var listId = card.idList;
  
  var category = mapLabelToCategory(card)
  
  var objectives = mapChecklistsToObjectives(card);
  
  var product = {
    'name': productName,
    'list': listId,
    'teamName': teamName,
    'category': category,
    'objectives': objectives
  }
  
  return product
}

function mapLabelToCategory(card) {
  for(var i in card.labels) { // because of the validation on the getProductsFromTrello, there will always be just one colored label
    var label = card.labels[i];
    if (label.color !== null) {
      var category = {
        'name': label.name,
        'color': label.color
      }
      
      break;
    }
  }
  
  return category;
}

function mapChecklistsToObjectives(card) {
  var objectives = []
  for(var i in card.checklists){
    var checklist = card.checklists[i];
    
    var objective = {
      'name': checklist.name, 
      'completed': 0, 
      'total': 0
    }
    
    for(var j in checklist.checkItems) {
      var checkItem = checklist.checkItems[j];
      objective.total += 1
      if(checkItem.state == "complete"){
        objective.completed += 1
      }
    }
    
    objectives.push(objective)
  }
  
  return objectives;
}