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

はじめまして、SmartHR デジタルマーケの清水 ( @katz_sk2 ) です。
この記事は SmartHR Advent Calendar 2019 の19日目の記事です。

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

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

GoogleAppsScriptを書こうと思った背景

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

▼ デフォルトのTrello通知機能 ▼

こうやって素敵な世界を作ったよ

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

▼ 素敵な世界の登場人物と役割 ▼

書いたGASはこんな感じ

• 各ツールのIDやトークンを「xxxxx」に置き換えているので、コピーしてもそのまま使えません。ご了承ください。

/////  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#[e-mail]');
  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#[e-mail]');
  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#[e-mail]');
  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のすべてのカードの情報をスプレッドシートに出力する(ひとまず版)
• Google Apps ScriptでTrelloのボードに登録しているメンバー情報を取り出す
• Trello API Docs

スプレットシート ➔ Slack

• GoogleSpreadSheetからSlackへメッセージと飛ばしてみよう

実行結果

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

明日期限のタスクがある場合、Bot-Aがやさしく教えてくれる

期限切れのタスクがある場合、Bot-Bに怒られる

新たにタスクが完了した場合、Bot-Cがゆるい感じに教えてくれる

さいごに

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