はじめに

ダッシュボードに心を動かされている＆
Trelloをよい感じに可視化したかったので、やってみた。
ちなみに、GASとData Portalをきちんと触るのは、今回が初めて…。

作成したダッシュボード
https://datastudio.google.com/reporting/c62fdb64-5167-420b-a956-404d920834d7

なお、こちらの記事を大いに参考にさせて頂いた。
http://pandorina463.hatenablog.com/entry/2019/08/28/233428

流れ

全体的な流れ

Trello → GASでspreadsheet→ Data Portalに連携

うむ、とてもシンプルです。

Step:1 TrelloのAPIなど取得

こちらを参照して、Step2に必要となる各種情報を収集。

Google Apps ScriptでTrelloを操作するはじめの一歩
https://tonari-it.com/gas-get-trello-api-key/

必要となる情報①

TrelloのAPIキー
Trelloのトークン
TrelloのUSERNAME

必要となる情報②

上記3つから、ボードIDを取得する。
GASで取得。別にGASでなくても良い。

TrelloのボードID

function getBoard() {
  var trelloKey   = "tb3asr55h5hasdfaghr0038398804ba841";  // ダミーです
  var trelloToken = "tb3asr55h5hasdfaghr0038c804ba841a5h5hasdsr55h5hasdfr00383988"; //tokenを入力してください
  var userName = "aaaaaaaaaa";//user nameを入力してください

  var url = 'https://trello.com/1/members/' + userName + '/boards?key=' + trelloKey + '&token=' + trelloToken + '&fields=name';
  res = UrlFetchApp.fetch(url, {'method':'get'});
  Logger.log(res);
}

必要となる情報③

そしたら、そのボードIDも利用して、以下URLよりリストIDを取得する。
リスト名とIDが表示されるからテキストに保持。

https://api.trello.com/1/boards/<ココにボードID>/lists?key=<ココにkey>&token=<ココにトークン>&fields=name

Step:2 GASでTrello情報取得

参考サイトのほぼパクリ。
spreadsheetのSettingシートに、事前に取得したリストIDを羅列しておき、
それを読込んで順にカードのラベルとタイトルを取得していく流れです。

アクティブシートがうまく動作しなかったので、明示的にspreadsheetのIDを指定。
日次実行の記録も保持しておきたかったので、Calculationシート(関数付き)に実行時の集計を行い、
それをTimelineシートの最終レコードに付与していく風に変更。動けばいいのです。

// triggerで回す関数
function main() {
  writeCards()
}

// 取得したカード情報をspreadsheetに転記
function writeCards(){
  const SHEET_NAME = "Data";
  var spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = spreadsheet.getSheetByName(SHEET_NAME);

  sheet.getRange('A2:D1000').clearContent()
  var list_ids = getTrelloListIds()
  var countnum = list_ids.length-2
  for (var i=0; i<list_ids.length-2; i++) {
    var json = getTrelloCards(list_ids[i])
    for (var j=0; j<json.length; j++) {
      sheet.appendRow([
        convertListId2ListName(json[j]['idList'])
        , json[j]['name']
        , json[j]['labels'][0]['name']
        , convertListId2Stage(json[j]['idList'])
      ]);
    }
  }

  var timeline = spreadsheet.getSheetByName('Timeline')
  var calc = spreadsheet.getSheetByName('Calculation')
  var lr = timeline.getLastRow()
  calc.getRange(2,1,1,5).copyTo(timeline.getRange(lr+1,1),{contentsOnly:true})
}

// リストidを指定し、trello cardを取得する
function getTrelloCards(list_id){
  const Trello_KEY   = "ここTrelloキー"
  const Trello_TOKEN = "ここTrelloトークン"
  var url = 'https://api.trello.com/1/lists/' + list_id + '/cards?key=' + Trello_KEY + '&token=' + Trello_TOKEN;
  var response = UrlFetchApp.fetch(url);
  var json = JSON.parse(response.getContentText());
  return json
}

// リストidを取得(spreadsheetのSettingシートに羅列したものを取ってくる)
function getTrelloListIds() {
  var spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = spreadsheet.getSheetByName('Setting');
  ids = sheet.getRange(2, 2, 18)
  return ids.getValues()
}

// リストidからリストの名前とか持ってくる（個人的に欲しかった）
function convertListId2ListName(id) {
  var spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = spreadsheet.getSheetByName('Setting');
  for (var i=2; i<18; i++)
    if (id == sheet.getRange(i, 2).getValue()) 
      return sheet.getRange(i, 1).getValue()
}

// Stageとか持ってくる（個人的に欲しかった）
function convertListId2Stage(id) {
  var spreadsheet = SpreadsheetApp.openById("スプレッドシートのID");
  var sheet = spreadsheet.getSheetByName('Setting');
  for (var i=2; i<18; i++)
    if (id == sheet.getRange(i, 2).getValue()) 
      return sheet.getRange(i, 3).getValue()
}

一番ここが時間かかったかもしれない…。
ほぼVBAじゃん！って思ったけど、地味にGASの関数が分からなかったり。

Step3: Data Portalにデータ追加

spreadsheetにTrello情報が記録されるので、それをData Portalに食わせます。
ここは特に難しいところはなく、感覚で充分操作できちゃう。あー、すごい。
分からない部分あってもチュートリとか参考にすれば、スムーズにできそう。

完成

この瞬間がやっぱりデータ可視化のたまらないところ。

スカスカだったりするけど、それはこれからカスタマイズしていこう。
これを可視化することの目的？？個人で利用しているので、モチベアップのみと言って良いでしょう！！

おわりに

色々と追加でやってみよう！

他の複数人の共有ボードでやってみる。担当者別に見たい。
期限切れも取得して、期限切れ件数とかを見えるようにしたい。
複数ラベルに対応させたい。

Trello→GAS→Google Data Portal で日々のタスク状況を可視化してみた