Google Apps Scriptを使ってtrelloのカードリストとメンバ一覧を取得してスプレッドシートに保存する

はじめに

Trelloというかんばんツールをタスク管理に使っているのですが、タスクを自動で通知したりしたいなと思い、Google Apps Scriptで一覧を取得してみました。

事前準備

参考にしたのは以下の記事です。
http://blog.serverworks.co.jp/tech/2016/05/16/trello-api/

こちらの記事を参考に、KEY、TOKEN、BOARD ID、LIST IDを取得します。

sample

trello.js

function main() {
   //Trello
   var key   = <your key>;
   var token = <your token>;
   var board = <board id>;
   var list  = <list id>;

   var cardsUrl = "https://trello.com/1/lists/" + list + "/cards?key=" + key + "&token=" + token;
   var membersUrl = "https://trello.com/1/boards/" + board + "/members?key=" + key + "&token=" + token;

   var cardsJson = fetchTrelloData(cardsUrl);
   var arrCards = parseCardsJson(cardsJson);

   var columnsCard = ["name", "due", "idMembers", "dataLastActivity", "shortUrl"];
   var sheetCards = initSheet("cards", columnsCard);
   setDataToSheet(sheetCards, arrCards);

   var membersJson = fetchTrelloData(membersUrl);
   var arrMembers = parseMembersJson(membersJson);
   var columnsMembers = ["id", "fullName", "username"];
   var sheetMembers = initSheet("members", columnsMembers);
   setDataToSheet(sheetMembers, arrMembers);
}

function fetchTrelloData(url) {
    var res = UrlFetchApp.fetch(url);
    var json = JSON.parse(res.getContentText());
    return json;
}

function parseCardsJson(json){
  var maxRows = json.length;
  var cards =[];
  for(var i = 0; i < maxRows; i++){
    var name = json[i].name;
    var due = json[i].due;
    var idMembers = json[i].idMembers;
    var dateLastActivity = json[i].dateLastActivity;
    var shortUrl = json[i].shortUrl;
    var card = [name, due, idMembers, dateLastActivity, shortUrl];
    cards.push(card);
    }
    return cards;
}

function parseMembersJson(json){
  var maxRows = json.length;
  var members =[];
  for(var i = 0; i < maxRows; i++){
    var id = json[i].id;
    var fullName = json[i].fullName;
    var username = json[i].username;
    var member = [id, fullName, username];
    members.push(member);
    }
    return members;
}

function initSheet(sheetName,columns){
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  try{
     spreadsheet.insertSheet(sheetName);
     var sheet = spreadsheet.getSheetByName(sheetName); 
  }catch(e){
     var sheet = spreadsheet.getSheetByName(sheetName); 
     sheet.clear();
  }
  var column = columns.length;
  var tmp = [];
  tmp.push(columns) //二次元配列にしている
  sheet.getRange(1,1,1,column).setValues(tmp);
  return sheet;
}

function setDataToSheet(sheet,array){
  var lastRow = array.length;
  var column = array[0].length;
  sheet.getRange(2,1,lastRow,column).setValues(array);
}