【GAS】Redmineのチケット情報をスプレッドシートに転記する

スプレットシートにREDMINEのチケット情報を転記して一覧表示する機能をGoogleAppsScriptで実装します。

背景

情報セキュリティ的観点やアカウント作成のフローなどでREDMINEログインさせてあげられないけど、おおまかな業務進捗は把握してもらいたいなどあるらしいのでチケット情報をスプレッドシートで確認できるようにしていきます。

手順

Redmine

APIアクセスキー

[個人設定] → [APIアクセスキー] → [表示]
GASプログラムで必要になるのでメモしておく

RESTによるWebサービスを有効にする

[管理] → [設定] → [API]
「RESTによるWebサービスを有効にする」にチェックを入れて保存

GAS

新規プロジェクト作成

先にスプレッドシートを用意しておきます。
チケットを転記したいスプレットシートを新規作成します。

GoogleAppsScriptで新しいプロジェクトを作成します。今回はスプレッドシートから直接GASプロジェクトを開いて、スプレッドシートとGASを自動で関連付けるようにします。

スプレッドシートの[ツール] → [スクリプトエディタ]と進みスクリプトエディタを開く。

トリガー設置

今回はボタンを押したらREDMINEのチケット情報を取得したいのでボタンを設置します。
トリガーとなるボタンをメニューに追加します。

// スプレッドシートを開いた時に、メニューを追加する
function onOpen(){
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('チケット取得');
  menu.addItem('REDMINEから取得する', 'doRedPull');
  menu.addToUi();
}

シート名命名

REDMINEのチケット情報を転記するシートをプログラムで指定するのでシート名を「RED_TICKET」と命名します。

シートを取得

プログラムでシート指定するので関数を作っておきます。

/*------------------------------------------*/
/*シートを取得*/
/*------------------------------------------*/
var REDMINE_SHEET_NAME = 'RED_TICKET';
function getRedmineSheet(){
  var sheetName = REDMINE_SHEET_NAME;
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  return sheet;
}

シートの準備

今回はチケットIDと題名とステータスの３つを転記します。
まずはシートに「ID」「題名」「ステータス」のカラムを入力して、その下にずらずらとチケット情報が書き足されていくようにします。

チケット情報の削除

チケット情報を取得した際に既存のスプレッドシートのデータを削除する機能も先に作っておきます。
今回は10行まで転記する予定なので10行分削除するようにプログラムします。

/*------------------------------------------*/
// 既存のデータを削除する
/*------------------------------------------*/
  var MAX_ROWS = 10;
  var RED_START_ROWS = 2;
  var RED_TICKET_COLS = 1;
  // 列番号ぼ設定
  var TICKET_COLUMNS = {
    'id' : 1,
    'subject' : 2,
    'status' : 3,
  }
function doContentsClear(){
  contentsClear(REDMINE_SHEET_NAME);
}
function contentsClear(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  for(var idx in TICKET_COLUMNS){
    if(TICKET_COLUMNS.hasOwnProperty(idx)){
//      設定したカラムを削除
      var redComentCols = TICKET_COLUMNS[idx];
      sheet.getRange(RED_START_ROWS,redComentCols,MAX_ROWS).clearContent();
    }
  }
  return;
}

「ID」「題名」「ステータス」に削除用に文字を打っていきdoContentsClearを実行します。
実行後に削除されたら成功です。

REDMINEチケット取得

それではREDMINEのチケット取得処理を記述していきましょう。
チケットを取得するのに必要な情報は下記2点です。

1. REDMINEのURL
2. REDMINEのアクセスAPI

URLとAPIアクセスキー定数として宣言しておきます。コードの配布や公開などする際に実在する文字列で公開しないように注意しましょう。
SSLエラーを回避するオプションもつけておきます。

var REDMINE_URL = 'https://redmine.my_site.com/issues';
var REDMINE_API_KEY = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
var EXTENSION = '.json';
var FETCH_OPTIONS = {
    // SSLエラー回避
    "validateHttpsCertificates" : false
  }

REDMINEのAPIをたたいてチケット情報が取得できているか確認してみます。
GASでは「Logger.log();」でデバッグしていきます。
[表示] → [ログ]を押下するとログが表示されますので適宜コードに仕込んで確認していきましょう。
ショートカットキーは「Ctrl+Ent」です。

function getRedmineIsseus(){
  var url_params = '?key=' + REDMINE_API_KEY;
  var json_url = REDMINE_URL + EXTENSION + url_params;

  var contents = UrlFetchApp.fetch(json_url,FETCH_OPTIONS).getContentText();
  var jsonDataTotalContents = JSON.parse(contents);
  Logger.log(jsonDataTotalContents);
}

実行する関数「getRedmineIsseus」を選択して「▶」を押下します。処理が正常に完了したら（エラーメッセージがでなかったら）ログを見てみます。
無事取得できていればチケット情報がもりもりと表示されていることでしょう。

必要なデータだけを転記する

今回は「ID」「題名」「ステータス」をスプレッドシートに表示させたいので取得したデータをjson形式に直してからデータをセルに入力する処理を記述します。

var REDMINE_URL = 'https://redmine.my_site.com/issues';
var REDMINE_API_KEY = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
var EXTENSION = '.json';
var FETCH_OPTIONS = {
    // SSLエラー回避
    "validateHttpsCertificates" : false
  }
*------------------------------------------*/
// REDMINE連携
/*------------------------------------------*/
function doRedPull(){
  getRedmineIsseus();
}
/*------------------------------------------*/
// チケット取得
/*------------------------------------------*/
function getRedmineIsseus(){

  var url_params = '?key=' + REDMINE_API_KEY;
  var json_url = REDMINE_URL + EXTENSION + url_params;

  var contents = UrlFetchApp.fetch(json_url,FETCH_OPTIONS).getContentText();
  var jsonDataTotalContents = JSON.parse(contents);
  var total_count = jsonDataTotalContents.total_count;

  // データが取得できたら既存のデータを削除する
  if(total_count > 0){
    contentsClear(REDMINE_SHEET_NAME);
  }

  // APIは100件までしか表示できないので分割して取得する
  if(total_count < LIMIT){
    var pageCount = 1;
  }else{
    var pageCount = Math.ceil(total_count / LIMIT);
  }

  //シート指定
  var sheet = getRedmineSheet();

  var i = RED_START_ROWS; // 何行目から開始するのか？
  for(var page = 1; page <= pageCount; page++){
    var json_tmp_url = json_url + '&limit=' + LIMIT + '&page=' + page;
    // JSONの設定
    var json = UrlFetchApp.fetch(json_tmp_url,FETCH_OPTIONS).getContentText();
    var jsonData = JSON.parse(json);
    var issues = jsonData.issues;

    //逆順にする
    issues = issues.reverse();

    // JSONを書き込む
    for (var idx in issues) {

      if(i >= MAX_ROWS + RED_START_ROWS){break;}

      sheet.getRange(i, TICKET_COLUMNS['id']).setValue(issues[idx]['id']);
      sheet.getRange(i, TICKET_COLUMNS['subject']).setValue(issues[idx]['subject']);
      sheet.getRange(i, TICKET_COLUMNS['status']).setValue(issues[idx]['status']['name']);

      i++;

    }
  }
}

doRedPull関数を実行してみましょう。スプレッドシートにチケット情報が転記されれば成功です。
スプレッドシートからも実行してみます。メニューに追加したボタンを押下してチケット情報が更新されればOK。今後はスクリプトエディタを開かなくても、設置したボタンでチケット情報を取り込むことが出ます。