GASを使ってスプレッドシートのプルダウンの変更とTrelloのリスト移動を連携させる


## やりたいこと

BtoCのマッチングサービスのマーケティングに関わっているときに、Bを担当するチームに所属していた。

チームでは企業の情報ををスプレッドシートで管理していたのだが一つ問題があった。それは企業が増えると**どの企業がどの営業ステータス**なのかひと目で分からないことだ。つまり今何をするべきなのかが分からない。

こんな感じ。

<img width="882" alt="スクリーンショット 2019-03-05 10.53.11.png" src="https://qiita-image-store.s3.amazonaws.com/0/240838/16130946-7cc1-9260-eca0-1795230700c8.png">
（企業がかなり増えてきていて、パッと見でどの企業がどのステータスなのかが分からない！）

そこでそのステータスはTrelloでも管理しようということになった。
<img width="1127" alt="スクリーンショット 2019-03-05 10.49.17.png" src="https://qiita-image-store.s3.amazonaws.com/0/240838/a00639c2-5e59-a517-205e-223254bdd355.png">

確かにひと目で分かる。

ただ問題はスプレッドシートとTrelloの更新と2度手間になってしまうことだった。そこでスプレッドシートで管理しているステータス管理を、そのプルダウンの変更に合わせてTrelloのリスト間をカードが移動する自動化をしようということになった。


（※イメージ動画）


## 実装方法

![図1.png](https://qiita-image-store.s3.amazonaws.com/0/240838/7ad563b7-6873-bdfd-e025-e365aaa0d9ed.png)

実装は少し複雑。
僕は業務上はエンジニアではないし、めちゃめちゃな部分もあるというのを承知の上で参考にしてみてください。（この方がうまくいくよ。みたいなご指摘大歓迎です。コメントください！）

下記のURLは大いに参考にさせてもらった。
https://www.virment.com/use-trello-api-google-apps-script/

まずはこちらを参考に連携に必要なTrelloの種々のIDをGASを使って取得してください。
https://qiita.com/RYOSUKE310/items/016549922a17376efdf8

それと今回はスプレッドシートからTrelloへカード追加ができるようにもしてあるのでそちらも見てください（必須でやらなければならないことではない）

今回は連携がやりやすいようにスプレッドシートを下記の写真の通りにしました。
<img width="1119" alt="スクリーンショット 2019-03-06 18.05.48.png" src="https://qiita-image-store.s3.amazonaws.com/0/240838/c2f7f073-b068-5259-e25f-d0ded78e23dc.png">

結論から言うと用いたコードはこちらです。

```javascript
function updateCard(cardID){
  var DEV_API_KEY = "// TrelloのAPIキー";
  var API_TOKEN = "// TrelloのAPIトークン";
  var cardPos = "top"; 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssname = ss.getSheetName()
  if(ssname == "//対象のシート"){
    var myCell = ss.getActiveCell();//アクティブセルの取得
    var IDset = myCell.offset(0, -7).getValue(); //アクティブセルの列の最左からカードID取得
    if(myCell.getColumn()==8){  //もしアクティブセルがH（＝8）だったら 
      var newListID = myCell.offset(0,1).getValue();
      var url_ext = 'idList=' + newListID
      + '&pos=' + cardPos
      + '&key=' + DEV_API_KEY
      + '&token=' + API_TOKEN;
      
      var baseurl = 'https://api.trello.com/1/cards/' + IDset + '?';
      var url = baseurl + url_ext;
      
      try {
        var response = UrlFetchApp.fetch(url, {'method':'put'});  // PUTリクエスト
        Logger.log(response);
        
      } catch (er) { //エラー発生寺にログを記録
        var logText = "エラー : line - " + er.lineNumber + '\n Error: ' + er.message;
        Logger.log(logText);
      }
    }
  }
}

```

ポイントはA列にカードIDが自動で入力されていることと、H列に異動先のリストIDが自動で入力されていることだ。順にやり方を解説する。

- A列にカードIDを自動入力

下記URLを参考にコードを書き直し実現した。
https://qiita.com/naoqoo2/items/956d3294e9da861c2975


```javascript
var DEV_API_KEY = "// TrelloのAPIキー";
var API_TOKEN = "// TrelloのAPIトークン";
var userName = "//ユーザ名を入力してください";

// ボードID
// getBoardsを実行して取得する
var board_id = '5c78b9e9f8443c0e6677db24';

// カード名の列番号
var title_column_no = 4;
// カード詳細説明の列番号
var description_column_no = 5;

function getBoards() {
  var url = 'https://api.trello.com/1/members/' + user_name + '/boards?key=' + api_key + '&token=' + api_token + '&fields=name';
  Logger.log(UrlFetchApp.fetch(url, {'method':'get'}));
}

function getlists() {
  var url = "https://trello.com/1/boards/" + board_id + "/lists?key=" + api_key + "&token=" + api_token + "&fields=name";
  Logger.log(UrlFetchApp.fetch(url, {'method':'get'}));
}

function getLabels() {
  var url = "https://trello.com/1/boards/" + board_id + "/labels?key=" + api_key + "&token=" + api_token + "&fields=name";
  Logger.log(UrlFetchApp.fetch(url, {'method':'get'}));
}

function addTrelloCard() {

  // 選択しているセルの開始行番号を取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var upper_left_cell = sheet.getActiveCell();
  var start_row = upper_left_cell.getRow();

  // 選択しているセルの行数を取得
  var range = SpreadsheetApp.getActiveRange();
  var rows = range.getNumRows();
  var newListID = sheet.getRange(start_row,9).getValue();
  var list_id = newListID;

  // 確認ダイアログ
  var message = '';
  var start_card_title = sheet.getRange(start_row, title_column_no).getValue();
  message += start_card_title;
  if (rows > 1) {
    message += ' など' + rows + '枚';
  }
  var result = Browser.msgBox('カードを作成します。よろしいですか？', message, Browser.Buttons.OK_CANCEL);

  if (result == 'cancel') {
    return;
  }

  // カード作成
  for (var i = 0; i < rows; i++) {
    var row = start_row + i;
    var card_title = sheet.getRange(row, title_column_no).getValue();
    var card_description = sheet.getRange(row, description_column_no).getValue();

    var url = 'https://api.trello.com/1/cards/?key=' + api_key + '&token=' + api_token;
    var options = {
      'method' : 'post',
      'muteHttpExceptions' : true,
      'payload' : {
        'name'      : card_title,
        'desc'      : card_description,
        'due'       : '',
        'idList'    : list_id,
        'urlSource' : ''
      }
    }

    var response = UrlFetchApp.fetch(url, options);
    var response_data = JSON.parse(response.getContentText());
    sheet.getRange(row, 1).setValue(response_data['id']);
  }
}

function onOpen() {
  // メニューバーにカスタムメニューを追加
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name : "addTrelloCard"  , functionName : "addTrelloCard"},
  ];
  spreadsheet.addMenu("GAS", entries);
}
```

- H列に異動先のリストIDを自動入力
これはプルダウンを作成する時から少し工夫している。
作りたいもののイメージはこちら。
<img width="181" alt="スクリーンショット 2019-03-06 18.11.46.png" src="https://qiita-image-store.s3.amazonaws.com/0/240838/117f67c3-694e-f692-d64e-fd0085dda497.png">



まず、別シートを作成する。
次にTrelloのリストをプルダウンで用いたいステータスと同様の名前をつける。
そしたらそれを写真のようにExcelに記入。

そしたらGASの出番。

```javascript

function getListId() {
  var DEV_API_KEY = "// TrelloのAPIキー";
  var API_TOKEN = "// TrelloのAPIトークン";
  var userName = "//ユーザ名を入力してください";
  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が出てくるのでスプレッドシートの対応するリストの横にコピペ（自動化しても良かったが面倒だったし量もないので力技にした）

そしたら元のシートに戻る。
プルダウンのところは入力規則から先ほど作った別シートのリスト名のところを選んであげれば良い。
その横にリストIDを自動入力させるにはvlookupを使うだけ。


これで必要なものが揃ったため、プルダウンでステータスを変更してください。Trelloにも反映されるはずです！

## 最後に
結構時間がかかったがこれによって管理がしやすくなり、スプレッドシートとTrelloで情報が違うようなこともなくなった。業務が便利になる自動化はどんどんやっていきたい。

こんな自動化したいです。のようなコメントもお待ちしています！可能な限りお答えします！








<br><br><br><br><br><br><br><br>

[![cci95logo.gif](https://raw.githubusercontent.com/k-tamura/cocha-icons-9to5er-edition/master/cci95logo.gif)](https://github.com/k-tamura/cocha-icons-9to5er-edition)