Trello API と GoogleAppsScript(GAS)を連携!環境準備からスプレッドシートへの書き込みまで楽々!

2,029 view

お久しぶりです、TOSHIです。

クーネルワークでは案件管理にTrelloというカード型タスクツールを利用しています。
このツールは付箋形式でデータ管理が可能で、直感的に操作出来るのですが、データを取り溜めたり分析することには若干不向きな面があります(デフォルトでCSVエクスポート機能もありますが、欲しいデータを出力することが出来ずに困りました)

Trello上での案件管理の流れとしては、単価や納品日を記載した「案件」カードを作成し、案件の進行に合わせて「見積り」「作業中」「納品」などの状態管理リストを移動していき、最終的に「完了」状態にするというものです。

案件管理は元々スプレッドシート上で行っていたのですが、直接案件データを入力し管理していくことはストレスで、案件数の増加に伴い管理自体難しくなっていました。そこでスプレッドシートとTrelloのAPIを連携し、データの蓄積とTrello上のカード管理を動的に行うことになりました。

GASを利用する準備

スプレッドシートにはJSのスクリプトをエクセルのマクロのように書くことが出来る、GoogleAppsScript(以降、GAS)という機能があります。GASは環境構築に時間をかけず、手軽に実装ができます。また、Trello側ではREST形式でアクセス可能なAPIが公開されているので、GASからTrelloのデータにアクセスすることが出来ます。

今回のスクリプトは全てスプレッドシートに紐づく「Container Bound Script」に記述しています。「Container Bound Script」はスプレッドシートの「ツール」>「スクリプト エディタ」から編集することが出来ます。

また、スクリプトをより簡潔に書く為に、Underscore.jsアンダースコアJS)とMoment.jsモーメントJS)のプロジェクトを読み込んでいます。外部プロジェクトは「リソース」>「ライブラリ」から読み込みが可能です。

アンダースコアのプロジェクトキー
M3i7wmUA_5n0NSEaa6NnNqOBao7QLBR4j

モーメントのプロジェクトキー
MHMchiX6c1bwSqGM1PZiW_PxhMjh3Sh48

「Container Bound Script」にスクリプトを書いていく場合、これで必要最低限の準備は完了です。

Trello APIを利用する準備

次にGASからTrelloのデータを取得する為の準備をします。Trello APIにアクセスする為には「API Key」と「API Token」が必要です。キーとトークンは下記URLから取得します。

https://trello.com/app-key

取得したキーとトークンは、JSの変数としてコードに直接書くことも可能ですが、プロジェクトのプロパティとしてスプレッドシートに保存しておくことも出来ます。

「ファイル」>「プロジェクトのプロパティ」>「スクリプトのプロパティ」
※スクリプトのプロパティはシートの所有者でないと書き込みが出来ません。

保存した各プロパティはPropertiesクラスのgetProperties()メソッドで取得可能です。スクリプトのプロパティにデータを保存しておくことで、効率よくデータにアクセスすることが出来ます。

Trello のデータを取得する

データの取得準備が出来たので、早速Trelloのデータを取得していきます。

Trello上のカードの取得は、ユーザー→ボード→リスト→カードの順に絞りこんでいくことで可能です。それぞれ名前やIDでデータを取得していくのですが、UI側からはIDが分からないので名前でボードを取得した後、各リストやカードの情報はAPIからJSONデータとして取得していく必要があります。

var yourUserName = 'Trello上でのユーザーネーム';
var yourApiKey = '準備したアクセスキー';
var yourApiToken = '準備したアクセストークン';
// REST形式のURLを作成
var boardUrl = 'https://api.trello.com/1/members/' + yourUserName + '/boards?key=' + yourApiKey + '&token=' + yourApiToken + '&fields=name';
// json形式のボード情報を取得
var jsonBoards = UrlFetchApp.fetch(boardUrl, {'method':'get'});
// jsonデータをパース
var boards = JSON.parse(jsonBoards.getContentText());
// 取得したボードの配列からボード名でIDを絞り込み取得
var board = boards.filter( function(brd, idx) {
if( { your board name } === brd.name ) {
boardId = brd.id;
} 
});
// REST形式のURLを作成
var listUrl = 'https://api.trello.com/1/boards/' + boardId + '/lists?key=' + yourApiKey + '&token=' + yourApiToken + '&fields=name';
// json形式のリスト情報を取得
var jsonLists = UrlFetchApp.fetch(listUrl, {'method':'get'});
// jsonデータをパース
var lists = JSON.parse(jsonLists.getContentText());
// 以下略...

各情報は、上記のような流れで取得出来ます。
REST形式なので、「GET」(取得)「PUT」(更新・移動)「POST」(作成)「DELETE」(削除)でデータにアクセス可能です。

API情報は下記リファレンスから確認出来ます。

https://trello.readme.io/v1.0/reference

また、Trelloのパワーアップ(追加機能)でカスタムフィールドを利用している場合、カスタムフィールドのデータをカードの名前やIDと一緒に取得することが出来ず、別途「GET」する必要がありました(このせいでAPIを叩く回数が倍増することに、、、)

var yourCardId = '取得したTrelloのカードID';
var pluginUrl = 'https://api.trello.com/1/cards/' + yourCardId + '/pluginData?key=' + yourApiKey + '&token=' + yourApiToken;
var jsonPluginData = UrlFetchApp.fetch(pluginUrl, {'method':'get'});
var pluginData = JSON.parse(jsonPluginData.getContentText());

取得したカスタムフィールドのデータは整形して、カード毎の情報にまとめておきます。

Trelloの情報をスプレッドシートに書き込む

Trello上の欲しいデータを取得することが出来たら、次は情報を書き込む為にスプレッドシートを取得します。

var spreadSheets = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadSheets.getSheetByName('シート名');

「Container Bound Script」でコードを書いている場合は、getActiveSpreadsheet()メソッドでシートを参照出来るので便利です。シートを取得したら、各行と列をループで回してセルにデータを入力していきます。

// getRangeの中はA1形式でも入力可能
sheet.getRange('行番号', '列番号').setValues('Trello の値');

setValue()メソッドで一つ一つのセルに書き込むのではなく、出来る限りsetValues()メソッドで複数のセルに同時に書き込む方がセルを何度も取得しなくて済むので処理が早くなります。セルのデータを取得するgetValues()メソッドも同様です。

GoogleAppsScript Range ドキュメント
https://developers.google.com/apps-script/reference/spreadsheet/range

Trello上のカードはシートに情報を書き込んだ後、必要に応じてTrello APIの「PUT」メソッドを使用して、リストの移動をしたり、アーカイブすることも出来ます。

スプレッドシートにメニューを追加

GASで作ったデータ更新用の関数を利用する為にスプレッドシートに独自メニューを追加します。onOpen()メソッドを「Container Bound Script」に書くことで、スプレッドシートを開いた時に自動で独自メニューを追加することが出来ます。

コードとしてはこのような感じです↓

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('独自メニュー名')
.addItem('ラベル', '関数名')
.addSeparator()
.addItem('ラベル', '関数名')
.addSeparator()
.addItem('ラベル', '関数名')
.addToUi();
}

書き込みや更新など複数の処理をするので、独自メニューの中にサブメニューを作成します。サブメニューのラベルをクリックすることで、指定した関数を呼び出すことが出来ます。

まとめ

機能についてはかなり端折りましたが、一通りTrelloのデータをスプレッドシートに書き込む流れを紹介しました。

GASによるスプレッドシートとREST形式のAPI連携は汎用性が高い上に、環境構築の手間がかからず、JSで書くことも出来る為、気軽に実装することが出来ます。また、GASは各Googleのサービスを利用することが出来るので更新情報をGmailに通知することなども容易に出来ます。

面倒なことはGASでさくっと自動化するとみんなから喜ばれること間違いなしです!

\ SNSでシェア /

WRITER

toshi

制作 toshi

1990年生まれの新米エンジニア。
慶応義塾大学文学部出身。

趣味は大学から始めた競技かるた(A級)/性格は飽きっぽいB型/移動手段はロードバイク/好きな食べものは馬刺し/わりと細身ですが、クーネルワークのクー(食)担当。