TrelloのカードをSpreadSheetで記録するGoogle Apps Script

.gitignore

# Created by https://www.gitignore.io/api/webstorm

### WebStorm ###
# Covers JetBrains IDEs: IntelliJ, RubyMine, PhpStorm, AppCode, PyCharm, CLion, Android Studio and Webstorm
# Reference: https://intellij-support.jetbrains.com/hc/en-us/articles/206544839

# User-specific stuff:
.idea

## File-based project format:
*.iws

## Plugin-specific files:

# IntelliJ
/out/

# mpeltonen/sbt-idea plugin
.idea_modules/

# JIRA plugin
atlassian-ide-plugin.xml

# Crashlytics plugin (for Android Studio and IntelliJ)
com_crashlytics_export_strings.xml
crashlytics.properties
crashlytics-build.properties
fabric.properties

### WebStorm Patch ###
# Comment Reason: https://github.com/joeblau/gitignore.io/issues/186#issuecomment-215987721

# *.iml
# modules.xml

trello-to-spreadsheet.gs

/**
 * Trelloの指定したボード・リスト内にあるカードを参照し
 * 新しいカードの追加や他のリストへの移動があった場合に
 * それらをSpreadSheetへ書き出すGASです
 *
 * 事前にTrelloから{@link https://trello.com/app-key API Key}と
 * API Tokenを取得しておく必要があります
 *
 * テンプレートシート以外に最低限1枚以上のシートが必要です
 *
 *
 * Created by imaizume on 2016/05/23.
 */

var scriptProperties = PropertiesService.getScriptProperties();

/** TrelloのAPI Key */
var key = scriptProperties.getProperty("key");
/** TrelloのAPI Token */
var token = scriptProperties.getProperty("token");
/** アクセスする際のAuthorizationオプション */
var optAuth = "key=" + key + "&token=" + token + "&";

/** 監視対象のボード名 */
var watchBoard = scriptProperties.getProperty("boards").split(",");
/** 監視対象のリスト名 */
var watchList = scriptProperties.getProperty("lists").split(",");

/** カードの名前のパターンから見積もり工数を抽出 */
var pattern_estimate = new RegExp(scriptProperties.getProperty("pattern"));
/** デフォルトで記入される実測工数 */
var default_actual = scriptProperties.getProperty("actual");

var URL_BASE = "https://api.trello.com/1/";

var HEADER_ID = "ID";
var HEADER_NAME = "Name";
var HEADER_STATUS = "Status";
var HEADER_LIST = "List";
var HEADER_LABELS = "Labels";
var HEADER_SHORT_URL = "ShortURL";
var HEADER_URL = "URL";
var HEADER_ESTIMATE = "Estiamte";
var HEADER_ACTUAL = "Actual"
var HEADER_DIFF = "Difference";
var HEADER_STARTED_AT = "Start Date";
var HEADER_FINISHED_AT = "Finish Date";

/**
 * 各シートのテーブルヘッダー
 */
var HEADER_ITEMS = [HEADER_ID, HEADER_NAME, HEADER_LIST, HEADER_LABELS,
    HEADER_SHORT_URL, HEADER_URL, HEADER_STATUS, HEADER_ESTIMATE, HEADER_ACTUAL, HEADER_DIFF, HEADER_STARTED_AT, HEADER_FINISHED_AT];

/** 「作業中のタスク」ラベル */
var STATUS_WORKING = 'working';
/** 「完了タスク」ラベル */
var STATUS_FINISHED = 'finished';

/**
 * TrelloのカードをSpreadSheetに記録
 */
function logTrelloCard() {

    // 監視対象の各ボードについて
    var urlBoard = URL_BASE + "members/" + "incheonspecial/boards?"
        + optAuth + "filter=open" + "&" + "fields=name,id";
    parseBoardToLists(JSON.parse(UrlFetchApp.fetch(urlBoard)));
}

/**
 * 取得したボードからリスト群を取得して次の処理へ渡す
 * @param responseBoard - APIサーバーからのJSONをパーズしたボードの配列
 */
function parseBoardToLists(responseBoard) {
    responseBoard
        .filter(function (board) {
            return (watchBoard.indexOf(board.name) !== -1);
        })
        .map(function (board) {
            var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(board.name);

            // ボード名に対応したシートがなければ新規作成
            if (sheet == null) {
                var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
                var templateSheet = activeSheet.getSheetByName('Template');
                sheet = activeSheet.insertSheet(board.name, 1, {template: templateSheet});
            }
            SpreadsheetApp.setActiveSheet(sheet);

            // 監視対象の各シートについて
            var urlList = URL_BASE + "boards/" + board.id + "/lists?"
                + optAuth + "&" + "fields=name,id";
            parseListToCards(JSON.parse(UrlFetchApp.fetch(urlList)));

        });
}

/**
 * 取得したリストからカード群を取得して次の処理へ渡す
 * @param responseList - APIサーバーからのJSONをパーズしたリストの配列
 */
function parseListToCards(responseList) {
    responseList
        .filter(function (list) {
            return (watchList.indexOf(list.name) !== -1);
        })
        .map(function (list) {

            var urlCard = URL_BASE + "lists/" + list.id + "/cards?"
                + optAuth + "&" + "fiedls=id,name,labels,shortUrl,url";
            var responseCard = JSON.parse(UrlFetchApp.fetch(urlCard));

            // カードが存在しなければスキップ
            if (responseCard.length === 0) return;

            var datas = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues();
            var workingCardIDs = [];

            datas.map(function (data, index) {

                var theID = data[HEADER_ITEMS.indexOf(HEADER_ID)];
                var theStatus = data[HEADER_ITEMS.indexOf(HEADER_STATUS)];

                if (theStatus === STATUS_FINISHED) return;
                if (theStatus === STATUS_WORKING) {
                    var cardIDs = responseCard.map(function (card) {
                        return card.id;
                    });
                    if (cardIDs.indexOf(theID) === -1) {
                        // 取得したカードに注目中しているIDが含まれていない
                        // 他のリストへ移動した=終了したとみなしてラベル付け
                        SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
                            .getRange(index + 1, HEADER_ITEMS.indexOf(HEADER_STATUS) + 1).setValue(STATUS_FINISHED);
                        SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
                            .getRange(index + 1, HEADER_ITEMS.indexOf(HEADER_FINISHED_AT) + 1).setValue((function(){
                            var d = new Date();
                            return d.getFullYear() + "/" + (d.getMonth() + 1) + "/" + d.getDate()
                                + "_" + d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds();
                        }).call());
                    } else {
                        // workingのカードは特に変更しないので一度配列にストック
                        workingCardIDs.push(theID);
                    }
                }
            });
            // workingのカードは除いて新規に行
            logNewCard(responseCard
                .map(function (card) {
                    card.list = list.name;
                    return card;
                })
                .filter(function (card) {
                    return (workingCardIDs.indexOf(card.id) === -1);
                }));
        });

}

/**
 * 残りのカードを新しく追記
 * @param cards - 追記するカードの配列
 */
function logNewCard(cards) {
    cards.map(function (theCard) {
        var nextRowNumber = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
                .getDataRange().getLastRow() + 1;
        var estmiate = (theCard.name).match(pattern_estimate);
        estmiate = (estmiate === null) ? 0 : estmiate[1];
        var values = [
            [HEADER_ID, theCard.id],
            [HEADER_NAME, theCard.name.replace(pattern_estimate, "").trim()],
            [HEADER_LIST, theCard.list],
            [HEADER_LABELS, theCard.labels.map(function (label) {
                return label.name;
            }).toString()],
            [HEADER_SHORT_URL, theCard.shortUrl],
            [HEADER_URL, theCard.url],
            [HEADER_STATUS, STATUS_WORKING],
            [HEADER_ESTIMATE, estmiate],
            [HEADER_ACTUAL, default_actual ? 0 : estmiate],
            [HEADER_STARTED_AT, (function(){
                var d = new Date();
                return d.getFullYear() + "/" + (d.getMonth() + 1) + "/" + d.getDate()
                    + "_" + d.getHours() + ":" + d.getMinutes() + ":" + d.getSeconds();
            }).call()]
        ];
        values.map(function (value) {
            SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
                .getRange(nextRowNumber, HEADER_ITEMS.indexOf(value[0]) + 1).setValue(value[1]);
        });
        SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
            .getRange(nextRowNumber, HEADER_ITEMS.indexOf(HEADER_DIFF) + 1)
            .setFormulaR1C1("=R[0]C[-1]-R[0]C[-2]");
    });
}

/**
 * テンプレートシートの作成
 * @param force - trueで強制的にリセットするかどうか
 */
function createTemplate(force) {
    var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
    var oldTemplateSheet = activeSheet.getSheetByName('Template')
    if (oldTemplateSheet) {
        if (!force) {
            Logger.log("Called createTemplate but not deleted because of the option.");
            return;
        }

        SpreadsheetApp.setActiveSheet(oldTemplateSheet);
        activeSheet.deleteActiveSheet();
        Logger.log("Called createTemplate and deleted forcibly.");
    }

    var templateSheet = activeSheet.insertSheet('template', 1);
    HEADER_ITEMS.map(function (v, i) {
        templateSheet.getRange(1, i + 1).setValue(v);
    });
    Logger.log("Craeted template sheet.");
}

/**
 * テンプレートシートのリセット
 */
function resetTemplate() {
    createTemplate(true);
}