Trelloで完了したチケットをGASで自動集計

複数の会社やプロジェクトに属しているが、自分がどれだけそれぞれの所属先に対して価値を提供しているのかを見える化してみたくなりました。

個人的に作業タスクの管理は、Trelloを使っているので完了になったチケットの、

• 価値（フィボナッチ数列のポイント）
• 作業時間（予想）
• 作業時間（実績）

をスプレッドシートに自動集計してみることにしました。

作業時間の予想と実績の両方を記録しているのは、データがある程度溜まってきたときに、自分の見積もり制度を精査してみたいからです。

Trelloの使い方

ラベル

プロジェクトごとにラベルを作っています。ラベル毎に集計をするので私の場合は、1カードに1ラベルをつけるようにしています。また、プロジェクトには関係ないですが、「スキル」と「LifeHack」のラベルも用意しています。

リスト

私の場合チームではなく個人で使っているため、リストを以下のように分けています。

• 起票（IceBoxに近い、思いついたらとりあえず起票しておく）
• 作業中（InProgress、相手があったりして待ち状態だったりしているが着手はしているもの）
• 今週中に終わらせる（今週で成果を出したいもの）
• 習慣化中（作業ではなく毎日のルーティンに取り込みたい良い行動、週間になったら完了にする）
• 完了（Done）

カードの項目

デフォルトのままだと、「ポイント」「作業時間（予想）」「作業時間（実績）」を入力できないため、CustomFilterを追加しています。多分これは有料版でなくても使えたような気がします。

スプレッドシートで見える化（集計）

Trelloで完了のリストに入ったカードを取得してきて、スプレッドシードに列データにします。以下はそのデータをグラフで集計しているものになります。グラフ自身はGASではなくデータシートをもとに、スプレットシートの表機能で表示しているだけです。

まだ動かしたばかりですので、少しさみしいですが、うーん、ライフハックばっかりやってる。。。

GAS

TrelloAPIを使って、スプレットシートへデータを転機します。

プロジェクトのトリガーは、時間手動で2時間毎に設定をしています。

以下が、コードになります。

TrelloのAPIKeyやTokenは、こちらから取得できます。
https://trello.com/1/appKey/generate

const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('データ');

const TRELLO_API_KEY = '[api key]'
const TRELLO_API_TOKEN = '[api token]'
const BOAD_ID = '[boad id]'

const POINT_CUSTOM_FILED_ID = '[カスタムフィールド（ポイント）のID]'
const EXPECTED_WORKING_HOURS_CUSTOM_FILED_ID = '[カスタムフィールド（作業時間・予想）のID]'
const PERFORM_WORKING_HOURS_CUSTOM_FILED_ID = '[カスタムフィールド（作業時間・実績）のID]'

const LIST_NAME = '完了'
const LABELS = ['スキル', 'LifeHack.', 'プロジェクト１', 'プロジェクト２', 'プロジェクト３', 'プロジェクト４' ]

const ID_COL = 1
const LABEL_COL = 2
const NAME_COL = 3
const CLOSED_DATE_COL = 4
const POINT_COL = 5
const EXPECTED_WORKING_HOURS_COL = 6
const ERFORM_WORKING_HOURS_COL = 7

const INIT_ROW = 2

function myFunction() {
  let list = get_list()
  let trelloCardIds = get_card_ids(list)
  let sheetCardIds = SHEET.getRange(1, 1, SHEET.getDataRange().getLastRow()).getValues().flat()

  let newClosedCardIds = trelloCardIds.filter(i => sheetCardIds.indexOf(i) == -1)
  
  newClosedCardIds.forEach(function(cardId) {
    lastRow = SHEET.getDataRange().getLastRow()
    
    if (lastRow === 1) {
      create_workLog(cardId, INIT_ROW)
    } else {
      create_workLog(cardId, lastRow+1)
    }
  })
}

function get_list() {
  let listUrl = 'https://api.trello.com/1/boards/' + BOAD_ID + '/lists?key=' + TRELLO_API_KEY + '&token=' + TRELLO_API_TOKEN + '&fields=name'
  let jsonLists = UrlFetchApp.fetch(listUrl, {'method':'get'})
  let lists = JSON.parse(jsonLists.getContentText())
  
  let list = lists.find(function(l) {
    return l.name === LIST_NAME
  })
  
  return list
}

function get_card_ids(list) {
  let cardUrl = "https://trello.com/1/lists/" + list.id + "/cards?key=" + TRELLO_API_KEY + "&token=" + TRELLO_API_TOKEN + "&fields=name"
  let jsonCards = UrlFetchApp.fetch(cardUrl, {'method':'get'})
  let cards = JSON.parse(jsonCards.getContentText())
  
  let card_ids = Array()
  cards.forEach(function(card) {
    card_ids.push(card.id)
  })
  
  return card_ids
}

function create_workLog(card_id, row) {
  Logger.log(card_id)
  
  let today = new Date();
  
  let cardUrl = "https://trello.com/1/cards/" + card_id + "?key=" + TRELLO_API_KEY + "&token=" + TRELLO_API_TOKEN + "&fields=name,dateLastActivity,labels&customFieldItems=true"
  let jsonCard = UrlFetchApp.fetch(cardUrl, {'method':'get'})
  let card = JSON.parse(jsonCard.getContentText())
  
  let labels = ''
  card.labels.forEach(function(label) {
    labels += label['name'] + ', '
  })
  
  SHEET.getRange(row, ID_COL).setValue(card.id);
  SHEET.getRange(row, LABEL_COL).setValue(labels);
  SHEET.getRange(row, NAME_COL).setValue(card.name);
  SHEET.getRange(row, CLOSED_DATE_COL).setValue(today);

  card.customFieldItems.forEach(function(item) {
    if (item['idCustomField'] === POINT_CUSTOM_FILED_ID) {
      SHEET.getRange(row, POINT_COL).setValue(item['value']['number'])
      
    } else if (item['idCustomField'] === EXPECTED_WORKING_HOURS_CUSTOM_FILED_ID) {
      SHEET.getRange(row, EXPECTED_WORKING_HOURS_COL).setValue(item['value']['number'])
      
    } else if (item['idCustomField'] === PERFORM_WORKING_HOURS_CUSTOM_FILED_ID) {
      SHEET.getRange(row, ERFORM_WORKING_HOURS_COL).setValue(item['value']['number'])
      
    }
  })
}

まとめ

この例は、使い方が特殊過ぎてあまり多くの人には使われないと思いますが、サーバーレスでさくっとスクリプトを書いて、少しでも日々の作業のカイゼンができると、個人的にはモチベーションがあがります。