GAS(GoogleAppsScript)を使ってTrelloとSlackをつなげて、タスク管理を便利にした話


はじめまして、SmartHR デジタルマーケの清水 ( [@katz_sk2](https://twitter.com/katz_sk2) ) です。
この記事は [SmartHR Advent Calendar 2019](https://qiita.com/advent-calendar/2019/smarthr) の19日目の記事です。

今回は業務改善のために、GoogleAppsScript を書いたのですが、Qiitaをはじめとする様々な技術情報共有サービスのおかげで、
非エンジニアの私でも自分の作成したいコードを完成させることができました。

なので、私自身もアウトプットすることで、Qiitaの活性に少しでも貢献したいと思ったので、今回、記事を書きました。
Trello、Slack、GoogleAppsScriptを活用して、タスク管理のモヤモヤを解決した話です。


# GoogleAppsScriptを書こうと思った背景
- 広告代理店とのタスク管理が煩雑になってきたので、Trelloを活用したいと思うようになりました。
- 実際に、WEB広告の運用は、常に10個ほどのタスクが同時に動くので、管理がものすごく大変になります。
    - 関わる人が多い(SmartHR(広告主)、代理店、各ベンダー)
        - 代理店の中でも、営業/運用/デザイナー/オペレーション/テック など幅広いチームがタスクを担当。
     - そして、タスクによっては、着手から完了まで1、2ヶ月くらいかかることも多くなります。
    - なので、ボール持っている人が不明確になりがちになります。
- また、ボールや進捗が不明瞭なタスクについてを毎回、「今、進捗いかがでしょうか？」「期日変更したほうが良いでしょうか？」などと、催促する/されるようなコミュニケーションは、あまり気持ちいいものではありません。
- また、代理店とはSlackを使ってコミュニケーションをとっているのですが、タスクを依頼したSlackのスレッドどこだっけ…？(課題①)となりストレスにもなっていました。
- そこで、Trelloの通知機能で改善できるのではないか！と思いましたが、Trelloのデフォルトの設定ではトリガーの粒度が荒く、タスク管理として、Slack上にメンションが通知されないと見逃す可能性が高い(課題②)ので、満足できず…
- そのため、これらの課題を打破すべく、Trello、SlackをGoogleAppsScriptでつなげることで、素敵な世界を作ってみました。

▼ デフォルトのTrello通知機能 ▼
<img src="https://esa-smarthr.s3-ap-northeast-1.amazonaws.com/uploads/production/attachments/11449/2019/10/23/53613/f20afee5-023a-4589-8936-d3cbe6ee1fb8.png" style="width:400;" height="400">


# こうやって素敵な世界を作ったよ
- TrelloAPIで項目を指定し、GASを使ってTrelloカードに記載してある項目をスプレットシートへ引っ張ってくる
- スプレットシート上で、関数を使い、Slack通知用に、集計/データ整形を行う
- そして、特定の条件になったら、Slackにメッセージを通知する
    - タスク期限の1日前
    - タスク期限切れ
    - タスク完了

▼ 素敵な世界の登場人物と役割 ▼
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/545545/b3d98999-ed06-975b-7681-b8e7f0a0e56f.png" style="width:300;" height="300">


# 書いたGASはこんな感じ
- 各ツールのIDやトークンを「xxxxx」に置き換えているので、コピーしてもそのまま使えません。ご了承ください。


```javascript

/////  Trello ➔ スプレットシート  /////
function torello() {
  var key   = "xxxxx";  //SlackAPIキー
  var token = "xxxxx"; //Slackトークン
  var boardid = "xxxxx"; //TrelloのID
  var sheet = SpreadsheetApp.openById("xxxxx"); // 出力するスプレットシートのID

  //前回の読み込みデータを削除
  sheet.getSheetByName('output').getRange('B2:E100').clear(); 
  sheet.getSheetByName('output').getRange('H2:L100').clear(); 

  // リスト情報をjson形式で取得
  var listurl = "https://trello.com/1/boards/" + boardid + "/lists?key=" + key + "&token=" + token + "&fields=name";
  var listres = UrlFetchApp.fetch(listurl);
  var listjson = JSON.parse(listres.getContentText());
  Logger.log(listres);  // レスポンス確認用
  
  // メンバー情報をjson形式で取得
  var menberurl = 'https://api.trello.com/1/boards/' + boardid + '/members?key=' + key + '&token=' + token;
  var menberres = UrlFetchApp.fetch(menberurl);
  var menberjson = JSON.parse(menberres.getContentText());
  Logger.log(menberres); 
  
  // カード情報をjson形式で取得
  var cardurl = "https://trello.com/1/boards/"+ boardid +"/cards?key="+ key +"&token="+ token;
  var cardres = UrlFetchApp.fetch(cardurl);  
  var cardjson = JSON.parse(cardres.getContentText()); 
  Logger.log(cardres); 

  // カード情報を「output」シートに出力
  for(i = 0; i < cardjson.length; i++){ // カード数分ループ
    sheet.getSheetByName('output').getRange(i+2,2).setValue(cardjson[i]['idList']); // カードが所属するリスト名をシートに記載
    sheet.getSheetByName('output').getRange(i+2,3).setValue(cardjson[i]['name']); // カード名をシートに記載
    sheet.getSheetByName('output').getRange(i+2,4).setValue(cardjson[i]['shortUrl']); // カード名をシートに記載
    sheet.getSheetByName('output').getRange(i+2,5).setValue(cardjson[i]['due']); // カードの期限をシートに記載
    for(j = 0; j < cardjson[i]['idMembers'].length; j++){ // カードの担当者数分ループ
      sheet.getSheetByName('output').getRange(i+2,j+8).setValue(cardjson[i]['idMembers'][j]); // カードの担当者を記載
    }
  }

  // メンバー情報を「list」シートに出力
  for( i = 0; i < menberjson.length; i++){
    sheet.getSheetByName('list').getRange(i+2,1).setValue(menberjson[i]['id']); // メンバーのID
    sheet.getSheetByName('list').getRange(i+2,2).setValue(menberjson[i]['fullName']); // メンバーの名前
  } 
  
  // リスト情報を「list」シートに出力
  for( i = 0; i < listjson.length; i++){
    sheet.getSheetByName('list').getRange(i+2,5).setValue(listjson[i]['id']); // リストのID
    sheet.getSheetByName('list').getRange(i+2,6).setValue(listjson[i]['name']); // リストの名前
  }   
}

/////  スプレットシート ➔ Slack(期限切れ)_Bot-A /////
function slack1() {
  var currentDate = new Date();
  var weekday = currentDate.getDay();
  
  //休日・祝日は動かないようにする
  if (weekday == 0 || weekday == 6) { // 土日だった場合何もせず終了
    return;
  }
  var calendar = CalendarApp.getCalendarById('ja.japanese#holiday@group.v.calendar.google.com');
  if (calendar.getEventsForDay(currentDate, {max: 1}).length > 0) { // 祝日だった場合何もせず終了
    return;
  }
  
  var sheet = SpreadsheetApp.openById('xxxxx'); // 管理表SpreadsheetID
  var messages = [];
  var sheetname = '期限切れカード';
  var cardnum = parseInt(sheet.getSheetByName(sheetname).getRange(2,13).getValue()); // 期限切れのカード枚数
  var member = [];
  
  //メッセージをSlackに送る
  if(cardnum > 0){
    messages.push('バッカモーン!タスクの期限が過ぎとるではないか！');
    for(i = 0; i < cardnum; i++){
      var cardlist = sheet.getSheetByName(sheetname).getRange(i+2,14).getValue(); // カードのリスト名
      var cardname = sheet.getSheetByName(sheetname).getRange(i+2,15).getValue(); // カード名
      var cardurl = sheet.getSheetByName(sheetname).getRange(i+2,16).getValue(); // カードURL
      var carddue = sheet.getSheetByName(sheetname).getRange(i+2,17).getValue(); // カード期限
      var duedate = Utilities.formatDate(new Date(carddue), "JST", "yyyy/MM/dd"); // カードの期限を"yyyy/mm/dd"に変換
      
      messages.push('=========\n- リスト名：'+ cardlist +'\n- カード名：' + cardname +'\n- カードURL：'+ cardurl + '\n- 期限：' + duedate);
      
      var membernum = sheet.getSheetByName(sheetname).getRange(i+2,5).getValue(); // カードの担当者数
 
      for(j = 0; j < membernum; j++){
        var num = j+1;
        var membername = sheet.getSheetByName(sheetname).getRange(i+2,j+18).getValue(); // カードの担当者名
        messages.push('\n- 担当者' + num + '： <@' + membername + '>\n')
      }
    }
  
    var sentence = messages.join('\r\n');
    var payload = {'text' : sentence,};
    var options = {
      'method' : 'post' ,
      'contentType' : 'application/json' ,
      'payload' : JSON.stringify(payload),
      'link_names' : 1,
    };
    
    // 投稿するwebhookURL
    var url = 'xxxxx' ; // 期限切れのBot-A
    
    UrlFetchApp.fetch(url, options);
  }    
}

/////  スプレットシート ➔ Slack(明日期限)_Bot-B /////
function slack2() {
  var currentDate = new Date();
  var weekday = currentDate.getDay();
  
  //休日・祝日は動かないようにする
  if (weekday == 0 || weekday == 6) { // 土日だった場合何もせず終了
    return;
  }
  var calendar = CalendarApp.getCalendarById('ja.japanese#holiday@group.v.calendar.google.com');
  if (calendar.getEventsForDay(currentDate, {max: 1}).length > 0) { // 祝日だった場合何もせず終了
    return;
  }
  
  var sheet = SpreadsheetApp.openById('xxxxx'); // 管理表SpreadsheetID
  var messages = [];
  var sheetname = '明日期限';
  var cardnum = parseInt(sheet.getSheetByName(sheetname).getRange(2,13).getValue()); // 明日期限のカード枚数
  var member = [];
  
  //メッセージをSlackに送る
  if(cardnum > 0){
    messages.push('明日期限のタスクよ！しっかりやりなさいー');
    for(i = 0; i < cardnum; i++){
      var cardlist = sheet.getSheetByName(sheetname).getRange(i+2,14).getValue(); // カードのリスト名
      var cardname = sheet.getSheetByName(sheetname).getRange(i+2,15).getValue(); // カード名
      var cardurl = sheet.getSheetByName(sheetname).getRange(i+2,16).getValue(); // カードURL
      var carddue = sheet.getSheetByName(sheetname).getRange(i+2,17).getValue(); // カード期限
      var duedate = Utilities.formatDate(new Date(carddue), "JST", "yyyy/MM/dd"); // カードの期限を"yyyy/mm/dd"に変換
      
      messages.push('=========\n- リスト名：'+ cardlist +'\n- カード名：' + cardname +'\n- カードURL：'+ cardurl + '\n- 期限：' + duedate);
      
      var membernum = sheet.getSheetByName(sheetname).getRange(i+2,5).getValue(); // カードの担当者数
 
      for(j = 0; j < membernum; j++){
        var num = j+1;
        var membername = sheet.getSheetByName(sheetname).getRange(i+2,j+18).getValue(); // カードの担当者名
        messages.push('\n- 担当者' + num + '： <@' + membername + '>\n')
      }
    }
  
    var sentence = messages.join('\r\n');
    var payload = {'text' : sentence,};
    var options = {
      'method' : 'post' ,
      'contentType' : 'application/json' ,
      'payload' : JSON.stringify(payload),
      'link_names' : 1,
    };
    
    // 投稿するwebhookURL
    var url = 'xxxxx' ; // 明日期限のBot-B
    
    UrlFetchApp.fetch(url, options);
  }
}

/////  スプレットシート ➔ Slack(タスク完了)_Bot-C /////
function slack3() {
  var currentDate = new Date();
  var weekday = currentDate.getDay();
  
  //休日・祝日は動かないようにする
  if (weekday == 0 || weekday == 6) { // 土日だった場合何もせず終了
    return;
  }
  var calendar = CalendarApp.getCalendarById('ja.japanese#holiday@group.v.calendar.google.com');
  if (calendar.getEventsForDay(currentDate, {max: 1}).length > 0) { // 祝日だった場合何もせず終了
    return;
  }
  
  var sheet = SpreadsheetApp.openById('xxxxx'); // 管理表SpreadsheetID
  var messages = [];
  var sheetname = '完了リスト';
  var cardnum = parseInt(sheet.getSheetByName(sheetname).getRange(2,7).getValue()); // 本日タスク完了に移動したカード枚数
  var member = [];
  
  //メッセージをSlackに送る
  if(cardnum > 0){
    messages.push('今日完了になったタスクですぅ〜');
    for(i = 0; i < cardnum; i++){
      var cardname = sheet.getSheetByName(sheetname).getRange(i+2,2).getValue(); // カード名
      
      messages.push('=========\n- カード名：' + cardname );    
    }
  
    var sentence = messages.join('\r\n');
    var payload = {'text' : sentence,};
    var options = {
      'method' : 'post' ,
      'contentType' : 'application/json' ,
      'payload' : JSON.stringify(payload),
      'link_names' : 1,
    };
    
    // 投稿するwebhookURL
    var url = 'xxxxx' ; // タスク完了のBot-C
    
    UrlFetchApp.fetch(url, options);
  }
}
```
# 参考記事
## Trello ➔ スプレットシート
- [Trelloのすべてのカードの情報をスプレッドシートに出力する(ひとまず版)](https://qiita.com/okauchiwani/items/88c9893b7e246d8f77a9)
- [Google Apps ScriptでTrelloのボードに登録しているメンバー情報を取り出す](https://tonari-it.com/gas-get-trello-members-info/)
- [Trello API Docs](https://developers.trello.com/reference#member-object)

## スプレットシート ➔ Slack
- [GoogleSpreadSheetからSlackへメッセージと飛ばしてみよう](https://note.com/noinoi/n/na9a575d5c4fe) 


# 実行結果
- 課題① タスクのボールを持っている人が不明確 & 現状のTrelloの設定だと通知されない
    - タスク担当者の名前でSlackのメンションが通知されるように 🎉
- 課題② 依頼したタスクの詳細(Slackのスレッド)を探すのが手間
    - TrelloカードのURLを表示し、ワンクリックでタスクの詳細を確認できるように 🎉

### 明日期限のタスクがある場合、Bot-Aがやさしく教えてくれる
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/545545/295da0d6-200a-5d9b-77e6-cb5ba4e70f98.png" style="width:300;" height="200">
### 期限切れのタスクがある場合、Bot-Bに怒られる
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/545545/2a3639db-2d56-c378-5e59-524c95cf86e7.png" style="width:300;" height="200">
### 新たにタスクが完了した場合、Bot-Cがゆるい感じに教えてくれる
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/545545/8acacd42-e9d7-299d-8689-178b3e2071a6.png" style="width:250;" height="100">


# さいごに
- 改めて、Qiitaをはじめとする様々な技術情報共有サービスには改めて感謝です。
- 2020年の Advent Calendar も書けるように、日頃からテクノロジーで解決できる課題はないか、のアンテナは常に立てておき、趣味としてツールを漁ったり、GoogleAppsScriptを書いていくぞい！