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

# はじめに

ダッシュボードに心を動かされている ＆ 
Trelloをよい感じに可視化したかったので、やってみた。
ちなみに、GASとData Portalをきちんと触るのは、今回が初めて…。
![visual.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/384185/621075a9-4d6d-7391-47ab-a98f2773c698.gif)

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



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


# 流れ
## 全体的な流れ
### Trello → GASでspreadsheet→ Data Portalに連携
![v2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/384185/e95e919f-739f-3516-a4d3-e832345223c5.png)
うむ、とてもシンプルです。

## 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に食わせます。
ここは特に難しいところはなく、感覚で充分操作できちゃう。あー、すごい。
分からない部分あってもチュートリとか参考にすれば、スムーズにできそう。

![v3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/384185/b244d372-5513-9553-4adb-e639de88d981.png)




# 完成

この瞬間がやっぱりデータ可視化のたまらないところ。
![v1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/384185/b4daff66-84d3-5685-b167-7f0f9cca2f98.png)


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



# おわりに

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

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