Google script to fetch Trello data and save on Google Sheets

index.gs

// trello variables
/**
The spreadsheet sheet with the raw data from trello is called 'Raw'
List prefixes:
  (P) = Product Backlog
  (S) = Sprint Backlog
  (U) = Urgente/volta de homologação - Não planejado!!
  (F) = Feito - pronto para homologação/em homologação/em produção
*/
var api_key = "z2x1c65a1c3z1x65c1a61c32z1xc561a6s5c1";
var api_token = "z5x1c32a1sc61zx3c1a968scz256x1c968asc";
var TRELLO_URL = "https://api.trello.com/1/";
var SPREADSHEET = null;
var SHEET_RAW = null;
var SHEET_CONFIG = null;

function trello_get(path, parameters) {
  
  if (!parameters || typeof(parameters) === "undefined") {
    parameters = {};
  }
  
  parameters['key'] = api_key;
  parameters['token'] = api_token;
  
  var queryString = "?";
  for (var k in parameters) {
    if (queryString != "?") {
      queryString += "&";
    }
    queryString += k + "=" + encodeURIComponent(parameters[k]);
  }
  
  return UrlFetchApp.fetch(TRELLO_URL + path + queryString);
  
}

function fetchBoardList() {
//  var ss = SpreadsheetApp.getActiveSheet().clear();
//  ss.appendRow(["Date", "Task", "Who", "List", "Link"]);
  var response = trello_get("members/me/boards");
//  var response = UrlFetchApp.fetch(url + "boards/" + board_id + "/lists?cards=all&" + key_and_token);
  var boards = JSON.parse((response.getContentText()));

  var simplifiedBoardList = boards.map(function(board) {
    return {
      id: board.id,
      name: board.name,
      shortLink: board.shortLink
    };
  });
  
  return simplifiedBoardList;
}

function fetchBoardLabels(boardId) {
  var requestOptions = {
    label_fields: 'color,idBoard,name'
  };
  var response = trello_get("boards/" + boardId + "/labels", requestOptions);
  var labels = JSON.parse((response.getContentText()));

  return labels;
}

function fetchBoardLists(boardId) {
  // Then we get the list of cards
  var requestOptions = {
    cards: 'open',
    card_fields: 'id,name,shortLink,shortUrl,url,labels,idMembers',
    filter: 'open',
    fields: 'all'
  };
  var response = trello_get("boards/" + boardId + "/lists", requestOptions);
  var lists = JSON.parse((response.getContentText()));
  
  return lists;
}

function fetchBoardMembers(boardId) {
  var requestOptions = {
    member_fields: 'id,avatarHash,avatarUrl,initials,fullName,username,confirmed,memberType'
  };
  var response = trello_get("boards/" + boardId + "/members", requestOptions);
  var members = JSON.parse((response.getContentText()));
  
  return members;
}

function mapFlatBoardListToCards(boardList, boardMembers, boardLabels) {
  var listId = boardList.id;
  // Get the board type
  var regExp = /\(([^)]+)\)/;
  var matches = regExp.exec(boardList.name);
  var listType = null;
  if (matches.length > 1) {
    listType = matches[1];
  }
  var listName = boardList.name;
  var cards = boardList.cards.map(function(card) {
    // Get the card points
    var regExp = /\(([^)]+)\)/;
    var matches = regExp.exec(card.name);
    var cardPoints = null;
    if (matches && matches.length > 1) {
      cardPoints = parseInt(matches[1]) || null;
    }
    return {
      daystamp: new Date(),
      list_id: listId,
      list_type: listType,
      list_name: listName,
      card_id: card.id,
      card_points: cardPoints,
      card_shortlink: card.shortUrl,
      card_members: card.idMembers.map(function(idMember) {
        var boardMember = null;
        for (var memberIdx in boardMembers) {
          if (boardMembers[memberIdx].id === idMember) {
            boardMember = boardMembers[memberIdx];
            break;
          }
        }
        return typeof(boardMember) !== "undefined" && boardMember ? boardMember.username : null;
      }).join(','),
      card_labels: card.idLabels.map(function(idLabel) {
        var boardLabel = null;
        for (var labelIdx in boardLabels) {
          if (boardLabels[labelIdx].id === idLabel) {
            boardLabel = boardLabels[labelIdx];
            break;
          }
        }
        return typeof(boardLabel) !== "undefined" && boardLabel ? boardLabel.name : null;
      }).join(','),
      card_name: card.name
    };
  });
  return cards;
}

function deleteRowsOfToday() {
  var rows = SHEET_RAW.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  
  var today = new Date();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    var dateColumn = row[0];
    if ((dateColumn instanceof Date) && dateColumn.toDateString() === today.toDateString()) {
      SHEET_RAW.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
  return rowsDeleted;
}

function loadConfigsFromSpreadsheet() {
  var rows = SHEET_CONFIG.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  
  var configs = {};
  
  for (var i = 0; i<= numRows - 1; i++) {
    var row = values[i];
    if (row.length > 1) {
      configs[row[0]] = row[1];
    }
  }
  return configs;
}

function main() {
  SPREADSHEET = SpreadsheetApp.getActiveSpreadsheet();
  SHEET_RAW = SPREADSHEET.getSheetByName('Raw');
  SHEET_CONFIG = SPREADSHEET.getSheetByName('Config');
  
  var configs = loadConfigsFromSpreadsheet();
  
  var boardId = configs.boardId;
  // daystamp, list_id, list_type, list_name, 
  // card_id, card_points, card_shortlink, card_members, card_name
  
  // First we have to get the list of members to hydrate each card list item
  var boardMembers = fetchBoardMembers(boardId);
  // Then we load the board lists
  var boardLists = fetchBoardLists(boardId);
  var boardLabels = fetchBoardLabels(boardId);
  var rawCardsRegistries = [];
  for (var listIndex in boardLists) {
    var boardList = boardLists[listIndex];
    var cards = mapFlatBoardListToCards(boardList, boardMembers, boardLabels);
    for (var cardIndex in cards) {
      rawCardsRegistries.push(cards[cardIndex]);
    }
  }
  
  var rowsDeleted = deleteRowsOfToday();
  
  for (rowIdx in rawCardsRegistries) {
    var row = rawCardsRegistries[rowIdx];
    SHEET_RAW.appendRow([
      row.daystamp,
      row.list_id,
      row.list_type,
      row.list_name,
      row.card_id,
      row.card_points,
      row.card_shortlink,
      row.card_members,
      row.card_name,
      row.card_labels
    ])
  }
  
  Logger.log('Registered ' + rawCardsRegistries.length + ' to Raw sheet ');
  
}