GAS+Trello+Slackを利用した進捗管理

# 初めに
みなさま、おはこんばんにちは！
（個人的には某スランプっ子ではなく、とある三人組を想像しながら挨拶しております）

個人的にはまだまだ若輩者だと感じていますが、自社他社問わずユニークで楽しい仲間達に支えられながら日々を過ごしております。

さて今回記事にしたものは、入社一年目に取り組んだことで、Trelloでタスク管理をしていた内容をスプレッドシートに一覧化して進捗率を記載し、Slackに通知するという内容になります。

# ツール紹介
- スプレッドシート（全体の進捗確認用）
- Google Apps Script（通称GASと呼ばれる今回一番頑張る子）
- Trello（タスク管理用）
- Trello API（GASで利用）
- Slack（通知用）
- Slack API（GASで利用 ※Incoming Webhook）
- Postman（APIテスト用 ※あったら便利くらい）

# 準備(アカウント作成等は割愛) 
1. Trello API
2. Slack API

## 1. Trello API
Trello APIは現在利用しているボードやボードに参加しているメンバー、ボード内のリスト、リスト内のカードなどの情報をjson形式で取得できるようになります。

利用するにあたり、API Keyとトークンを取得していきます。

### Trello API Key
まず先に[Trello](https://trello.com/)へログインをしておいてください。
その後[こちらのURL](https://trello.com/1/appKey/generate)からAPIKeyを発行していきます。

利用規約に同意しAPIKeyを発行すると、以下のような画面でAPIKeyが表示されます。（マスキングされている部分）
![3_trello_2_APIKEY発行2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/2678f7db-55a1-16cd-60c7-d9ccdc0077eb.png)
※コピーして控えておいてください

### Trello API トークン
続いてトークンの発行になりますが、先ほどの画面から手動でトークンを生成できます。

トークンの部分がURLになっているためそちらをクリックし、利用規約同意など進めていくと以下のように表示されます。（マスキングされている部分）
![3_trello_3_トークン発行2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/1adc24ac-c148-5e22-312f-4ab1968ff0f3.png)
※コピーして控えておいてください

## 2. Slack API
続いてはSlack APIになります。
こちらは簡単に扱えるIncoming Webhookを利用可能な状態にしていきます。

利用するにあたり、Slack APPの作成と、APPでIncoming Webhookの設定を行っていきます。

### Slack APPを作成
まずはIncoming Webhookkを設定するアプリを作成します。

[こちらのURL](https://api.slack.com/apps/new)をクリックすると、新しいAPPを作成できます。

まずは「最初から」を選択（※Chromeの日本語訳を利用しています）
![2_slack_app_1_作成.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/c76f7077-8911-af27-ce42-5fbb38b89ee8.png)

次にAPP名とアプリを利用するワークスペースを選択します。
![2_slack_app_2_命名.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/2b5570bd-6fa4-b544-690e-1299abb22fb0.png)

最後にSlack上でのAPPの表示名を設定します。
![2_slack_app_4_create_webhook_.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/6bf9bb0b-2d49-cd74-e1c2-d99da5059f19.png)

### Slack Incoming Webhookを作成
続いてAPPでIncoming Webhookを発行していきます。
初めに「着信 Webhook をアクティブ化する」をONにします。
![2_slack_app_4_5_create_webhook_.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/5b59130f-004a-8485-9df5-1469ef523904.png)

次にその下に新しく項目が出てきますので、「新しいWebhookをワークスペースに追加」というボタンをクリックします。
※画像はすでにクリックして設定した後
![2_slack_app_6_create_webhook_.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/9486fdfb-ba12-cd0d-c78c-a54a942602d2.png)

権限をリクエストされるので、投稿先を選択し許可してください。
![2_slack_app_5_create_webhook_.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/c0c46a52-7908-b0c4-859a-4cb6da48ada6.png)
これでSlack APIの設定は完了になります。

再度先ほどの画面を確認するとWebhook URLが追加されていると思いますので、そちらにある「コピー」をクリックして控えておいてください。

# APIをお試し
では発行したKeyやトークン、Incoming Webhook URLを利用して、お試し実行していきましょう。

## Trello API
まずはTrello APIからです。

Postmanをインストールして、以下のように入力していきます。
※APIを実行できるのであればPostmanでなくとも大丈夫です。
https://api.trello.com/1/boards/{ボードID}/lists?key={APIKey}&token={トークン}
![4_trello_1_お試し.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/2a7eef56-efe0-6c51-7b71-9bc15cf0ced6.png)

実行し、成功していれば以下のように返却されてきます。
idがTrelloの各リストのユニークIDとなります。
![4_trello_2_お試し.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/bd40c8b6-8a13-b11e-e05e-a007dfa0290a.png)

今回利用したAPIは以下になります。
https://api.trello.com/1/boards/{ボードID}/lists?key={APIKey}&token={トークン} ※1
https://api.trello.com/1/lists/{リストID}/cards?key={APIKey}&token={トークン}
https://api.trello.com/1/members/{メンバーID}?key={APIKey}&token={トークン} ※2
※1 ボードのAPIはリストのIDを控えるために利用したためGAS内では利用しません。
※2 メンバーIDはリストのAPIレスポンスにある、idMembersの配列内一番初めのIDを利用しています。

それぞれのTrello APIの仕様詳細は[公式](https://developer.atlassian.com/cloud/trello/rest/api-group-actions/)でご確認ください。

## Slack API
さて続いてはSlack API（Incoming Webhook）になります。

以下画像の通り設定と入力をしてください。
![5_slack_1_お試し.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/6ad08f69-9035-85b9-d5fe-702d6cf923ed.png)

実行し、成功していれば以下のようにokと返却されてきます。
![5_slack_2_お試し.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/a6faaf70-91f4-4468-ccda-09103087867c.png)

そして該当するSlackチャンネルを見ると、無事送信されていることが確認できます。
![5_slack_3_お試し.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/7359eca7-6e07-70cc-e4d6-1617ba9bc838.png)


利用できるpayloadの設定はいろいろございますので、こちらも詳細は[公式](https://api.slack.com/reference/messaging/payload)のほうを一度ご確認ください。

# 本編
大変お待たせいたしました、今回の本題にもございます進捗管理の一覧化になります。
まずは一覧を記載するためのスプレッドシートを用意します。
![6_本編_3_スプレッドシートを用意.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/91416437-80cf-ff8b-20a2-0508c8c9b343.png)

続いて、上段にある「拡張機能」から「Apps Script」を選択します。
![6_本編_2_GAS.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/33712836-e2c4-2fba-a500-228786866218.png)

すると以下のようにまっさらなプロジェクトが作成されます。
![6_本編_3_GAS.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/d6db5a2b-e211-dc73-7e1f-4af58ce76e80.png)

ではそちらにいろいろ関数を作成していきましょう。
まずはメニューに実行用のボタンを設置するための関数になります。
```javascript:Google Apps Script
// メニューに表示
function onOpen() {
  // メニューバーにカスタムメニューを追加
  var open_spread_sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name : "進捗率を更新"  , functionName : "startUpdateProgressRate"},
  ];
  open_spread_sheet.addMenu("GAS", entries);
}
```
すると以下のようにメニューに追加されるようになります。
※表示されない場合はスクリプトが保存されていないか、スプレッドシートでリロードが必要な場合がございます。
![6_本編_4_GAS.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/29a9fdc9-0040-760e-a7b3-6573a3cdc0ee.png)

続いて「進捗率を更新」を押した際の実行される「startUpdateProgressRate」関数を作成していきます。
```javascript:Google Apps Script
function startUpdateProgressRate() {
  try {
    // スプレッドシートを取得
    var spread_sheet = SpreadsheetApp.openById(spread_sheet_id);

    // シートを取得
    var sheet = spread_sheet.getSheetByName(sheet_name);

    // リスト分回す
    for(var list_count = 0; list_count < array_list_id.length; list_count++) {
      // Trelloからリスト内にあるカードを取得
      var list_url = 'https://api.trello.com/1/lists/' + array_list_id[list_count] + '/cards?key=' + api_key + '&token=' + api_token;
      var list_response = UrlFetchApp.fetch(list_url, {'method':'get'});
      var list_json = JSON.parse(list_response.getContentText());

      // カード分回す
      for(var j = 0; j < list_json.length; j++) {
        // trelloのカードID
        var trello_card_id = list_json[j].id;

        // シート内のIDカラムを取得
        var roop_row = start_row;
        var card_id = sheet.getRange(roop_row,card_id_column).getValue();

        // データある分回す（一致あれば抜ける）
        while(card_id.length > 0){
          // 一致するIDがあるか
          if(card_id == trello_card_id){
            break;
          }
          // 次の行へ
          roop_row++
          // シート内のIDカラムを再取得
          var card_id = sheet.getRange(roop_row,card_id_column).getValue();
        }

        // シート更新
        setSpreadsheets(list_count, sheet, list_json[j], roop_row);
      }
    }

    // slackに完了通知
    slack_notice(message);
  }catch(e){
    // slackにエラー通知
    errorSlackMessage(e);
  }
}
```
さらにここから呼び出される関数として、シート更新用とslack通知用を作成します。
```javascript:Google Apps Script
// シート更新
function setSpreadsheets(list_count, sheet, json, row){
  // データをセット
  sheet.getRange(row, card_id_column).setValue(json.id);
  sheet.getRange(row, card_name_column).setValue(json.name);
  if(json.idMembers[0] != null){
    var url = 'https://api.trello.com/1/members/' + json.idMembers[0] + '?key=' + api_key + '&token=' + api_token;
    var response = UrlFetchApp.fetch(url, {'method':'get'});
    var member_json = JSON.parse(response.getContentText());
    sheet.getRange(row, manager_column).setValue(member_json.fullName);
  }

  // シートに記載されている進捗率を取得
  var shert_progress_rate = sheet.getRange(row, progress_rate_column).getValue();
  var shert_progress_rate_int = 9999;
  if(shert_progress_rate != ""){
    shert_progress_rate_int = Number(shert_progress_rate)*100;
  }

  // Trelloのカードがあったリストによって進捗率を変える（ただし、現スプレッドシートの進捗率の方が高い場合そちらを真とする）
  if(shert_progress_rate_int == associative_array_list_id[array_list_id[list_count]]){
    return;
  }
  sheet.getRange(row, progress_rate_column).setValue(associative_array_list_id[array_list_id[list_count]] + "%");
}

// slack通知用
function slack_notice(slack_message) {
  // payloadの設定
  var json_data =
  {
     "text" : slack_message
  };
  var payload = JSON.stringify(json_data);

  // optionの設定
  var options =
  {
    "method" : "post",
    "contentType" : "application/json",
    "payload" : payload
  };

  // slackへ通知送信
  UrlFetchApp.fetch(postUrl, options);
}

function errorSlackMessage(errerMessage) {
  // エラーメッセージを作成
  var slack_error_message = Utilities.formatString(error_message, errerMessage);
  // slackへ通知
  slack_notice(slack_error_message);
}
```

その他変数も書きまして
```javascript:Google Apps Script
// 更新対象のスプレッドシートIDとシート名
var spread_sheet_id = 'xxxxxxxxxxxxxxxxxxx';
var sheet_name = '進捗率';

// TrelloのAPIキーおよびトークン
var api_key = 'xxxxxxxxxxxxxxxxxxx';
var api_token = 'xxxxxxxxxxxxxxxxxxx';

// リストIDと進捗率の連想配列
var associative_array_list_id = {
  'xxxxxxxxxxxxxxxxxxx':100,  // Done
  'xxxxxxxxxxxxxxxxxxx':70,   // Review
  'xxxxxxxxxxxxxxxxxxx':35,   // Doing
  'xxxxxxxxxxxxxxxxxxx':0,    // Todo
}

// リストIDの配列
var array_list_id = Object.keys(associative_array_list_id);

// カード格納先
var start_row = 2;
var card_id_column = 2;
var card_name_column = 3;
var manager_column = 4;
var progress_rate_column = 5;

// slack通知
var postUrl = 'https://hooks.slack.com/services/Txxxxxxxxxx/Bxxxxxxxxxx/xxxxxxxxxxxxxxxxxxx';
var error_message = 'エラーが発生しました。\nエラー内容：\n%s\n\n進捗率はこちらから閲覧できます。\nhttps://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxx/edit#gid=0';
var message = 'タスクの進捗率を更新しました。\n進捗率はこちらから閲覧できます。\nhttps://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxx/edit#gid=0';
```
保存して完成です！（プロジェクト名とかはお好きに）
では以下の状態から実行してみます。
![6_本編_5_Trello.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/39904578-d191-96b2-5c8d-15c73bdeb55c.png)
先ほどメニューに追加したボタンをぽちっとしたら
![6_本編_6_GAS.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/6767eb1d-c8c3-1b3c-aa57-df1fca48d088.png)
こうなりました！
![6_本編_7_GAS.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/00505a3b-1f95-aeae-18d6-3de084121b61.png)


slackのほうへも以下のようにメッセージが送信されていますね。
![6_本編_8_GAS.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2941104/25027244-9542-8ad6-2617-6770cec60662.png)



という感じで各リストに応じた進捗率を無事シートに記載しSlackへ通知できました。

GASには定期実行の機能があったりもしますし、Trelloカードには他にも開始日や終了日、最終更新日などの情報を設定できますので、いろいろお試しください。

# 最後に
ここまでお読みくださりありがとうございます。
GAS も Trello API も Slack API も便利ですので利用したことのない方はバシバシ利用してみてください！

# 参考サイト
https://qiita.com/chihiro/items/c7b11abc78f5d806c3a8

https://www.lisz-works.com/entry/gas-rw-spreadsheet

※作成した際に参考にしたサイトはほかにもあったはずですが、記憶ベースのためご了承ください。