LoginSignup
10
9

More than 3 years have passed since last update.

@zaki-lknrin株式会社エーピーコミュニケーションズ

Google Apps Scriptを使ってBacklogのプロジェクト作成をもっと便利に［REST API / スプレッドシート操作］

Last updated at Posted at 2019-05-06

はじめに

職場の非技術職の方が業務効率を上げるためにBacklogを試用し始めたらしいのだけど、「プロジェクト作成時に定型のマイルストーンを同時に作成したい(でもそんな機能がなさそう)、なんとかならないかなぁ」という話を小耳にはさんだので、用意されているREST APIで対応して使い勝手向上できないかなと試してみた。

で、Excel＋PowerShellでも出来そうだったけど、もともとExcelよりはGoogleスプレッドシートで情報管理はしていたそうなので、スプレッドシートで使えるGoogle Apps Scriptもあわせて試してみた。

(※ ちなみに筆者はGoogle Apps ScriptもJavaScriptも経験なし…)

Google Apps Scriptって？

  • Google Apps Scriptは単体でも動くし、(ExcelのVBAのように)スプレッドシートなどに内蔵させて動作させることもできる
  • GmailやカレンダーなどGoogleの各種サービスと簡単に連携できる
  • スプレッドシートの操作も当然できる
    • シートやセルの基本的な操作はほぼCOMコンポーネント使ったExcelの操作と同じ
  • 言語はJavaScript
  • サードパーティのライブラリも豊富
    • ただBacklog操作用のライブラリは見つからなかった…

Backlogって？

  • ヌーラボ社が運営しているタスク・プロジェクト管理のサービス
  • REST APIが用意されているので定型操作を自動化できる

ということで、Google Apps Scriptのwebアクセス機能を使って、スプレッドシートに記入した「プロジェクト名一覧」と「マイルストーン一覧」を入力として、Backlog APIを使って「リストの全プロジェクトを作成・各プロジェクトのマイルストーンも初期設定として作成」してみたという話。

Google Apps Scriptで実装

5/7追記: 初めてのスクリプト実行時には、実行しても問題ないことを「承認」する必要があるので、許可を求めるダイアログが表示されたら(問題ないスクリプトであること・ユーザ権限であることを確認して)許可を与える必要がある。

詳しくはこちら: 【初心者向けGAS】スクリプト実行時の「承認」でびっくりしないために

スプレッドシートの操作

とにかく、以下をざっと見れば操作できるようになる。

ここの「【初心者向けGAS】」の1から14まで通してみれば、GASでスプレッドシートを操作する基本的な処理内容は把握できる。
1ページごとの内容も短く簡潔にまとめられているので、すごくわかりやすいし、意外と早く概要は理解できるはず。

スクリプトの作成

まずはデータ管理用のスプレッドシートを作成する。

image.png

ファイル名は適当に入力。

image.png

ファイルを作成したら、[ツール]メニューの[スクリプト エディタ]を起動する。

image.png

すると、新しいタブでスクリプトエディタが起動する。

image.png

デフォルトでmyFunction()という空の関数が作成され、このファイルにスクリプトを作成していく。
スプレッドシートと同じく名前(「無題のプロジェクト」となっているところ)をクリックすると名前変更できるので、適当に変更する。

image.png

保存と実行とログ出力

Google Apps Scriptでは、(print()とかconsole.log()のような)ログ出力にはLogger.log()を使用する。

コード.gs
function myFunction() {
  Logger.log("hello world");
}

こんなコードを書いたら…

image.png

未保存のマークがつくので、Ctrl-Sで保存(自動保存じゃないみたい)

保存したら、Ctrl-Rで実行。

image.png

Ctrl-R以外にも、メニューバーや[実行]->[関数を実行]からも実行できる。

image.png

Logger.log()で出力した内容は、Ctrl-Enterか[表示]->[ログ]で出力内容を確認する。

image.png

関数が複数ある場合は、実行する関数を選択して実行することもできる。(個人的にこの仕組みはびっくりしたｗ)

image.png

参考: 【初心者向けGAS】はじめてのスクリプトを作成し、保存し、実行する

スプレッドシートの基本操作

基本的な操作は以下の通り。
(実はExcelの操作とまったく同じ)

  1. スプレッドシートを開く
  2. シートを開く
  3. セルを取得する
  4. 取得したセルに対して読み書きを行う

スプレッドシートを開く

スプレッドシートのスクリプトエディタで作成したスクリプトであれば、とにかく以下のコードで「開いているスプレッドシートのオブジェクト」が取れる。

var ss = SpreadsheetApp.getActiveSpreadsheet()

GASやJavaScript固有の単語はまだちょっと把握できてないけど、C++/Java的にいうとSpreadsheetAppクラスのgetActiveSpreadsheet()staticメソッドを呼んでいる感じ。

スプレッドシートに対する操作は、すべてこのメソッドで取得できるオブジェクト(ここではss変数)に対して行う。

参考: 【初心者向けGAS】Spreadsheetサービスの「オブジェクト」の基礎の基礎を知ろう

シートを開く

スプレッドシートは(Excelも同様に)複数のシートで構成されているので、処理対象のシートを選択する。
スプレッドシートを開くときに使用したgetActiveSpreadsheet()と同じように、現在アクティブなシートを開くgetActiveSheet()もあるが、複数シートがあると制御が難しい。
複数シートのうちどれかを開く場合は、シート名を指定してgetSheetByName("シート名")を使うとよい。

image.png

例えばこのシートを開くのであれば、以下の通り。

var ss = SpreadsheetApp.getActiveSpreadsheet()
var sheet = ss.getSheetByName("マイルストーン")

参考: 【初心者向けGAS】スプレッドシートのシートを取得する２つの方法

セルを取得する

セルを取得するには何通りか方法があるが、基本はまずgetRange()でセルの範囲を指定する。

getRange()にも使い方が複数あり、

  • セルの範囲が1つの場合
    • getRange('B1')で「B1セル」を取得する
    • getRange(1, 2)で、「1行目2列目」を取得する (数字は0でなく1開始)
  • 範囲が複数の場合
    • getRange('A1:B5')で「A1からB5までの範囲を取得する
    • getRange(1, 2, 3, 5)で「1行目2列目から3行目5列目」を取得する
  var cell = sheet.getRange("A1")

シート内のデータ件数がいくつあるか不明の場合でも、getLastRow()で、データが入力されている最終行を取得できる。

  var lastRow = sheet.getLastRow();

セルに対して読み書きする

getRange()で取得したセルに対してgetValue()を行うことでセルの内容を取得できる。
セルの範囲が複数の場合はgetValues()(複数形)で、リスト形式で内容を取得できる。

  var cell = sheet.getRange("A1")
  Logger.log("cell A1: %s", cell.getValue())  // A1セル内の値が取得できる

  var list = sheet.getRange(dataRowIndex, dataColumnIndex, lastRow, dataColumnIndex).getValues();

セルに値を書き込むには、setValue()を使用する。

  sheet.getRange(i, checkedColumnIndex).setValue("登録済")

参考: 【初心者向けGAS】スプレッドシートのセル・セル範囲とその値を取得する方法

メニューの追加

image.png

function onOpen(e)を使うことで、スプレッドシートにオリジナルのメニューを追加して任意の関数を簡単に呼び出せるように設定できる。

スクリプト内に、onOpen(e)という関数を定義することで、スプレッドシートを開いたタイミングでこの関数が読み込まれるので、ここにメニューを追加する処理を定義すればOK。

function onOpen(e) {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Backlog')
  .addItem('プロジェクト作成', 'createBacklogProject')
  .addItem('プロジェクト情報取得', 'getBacklogProjects')
  .addToUi();
}

createMenu("メニュー用文字列")にメソッドチェーン(関数の戻り値にさらに関数をつなげて書ける書き方のこと)でaddItem()で項目を追加する。
追加時の注意として、メニューを選択されたときに実行する関数名を文字列としてセットする。
(createBacklogProjectでなく'createBacklogProject'ということ)

参考: スプレッドシートとGASでTrelloのリストにカードを作成するツール / スプレッドシートのメニューに項目追加

REST APIの実行

RESTの操作で使用するのは、webアクセスを行うUrlFetchApp.fetch()と、JSONをparseするJSON.parse()。

BacklogのAPIを実行するには、APIキーを作成し、リクエストするURLに付加してアクセスすればよい。
(OAuth2も使えるけど、キーの期限切れ時の処理とかの処理とかが増えるので、今回はAPIキーで実装)

認証と認可 | Backlog Developer API | Nulab

APIキーの設定

Backlogの「個人設定」のメニューからAPIキーを作成する。
メモには用途など適当に入力して[登録]を押下。

image.png

このAPIキーの文字列を使えば、作成したユーザの権限であらゆる操作ができるようになるので他人に漏れたりしないようにしっかり管理する。
万一漏れてしまった場合は、×ボタンから削除し、再作成を行う。

APIキーをスクリプト内にそのまま書いても良いが、その場合はスクリプトをほかの人と共有設定するとAPIキーもほかの人に漏れてしまうので注意。
(共有する場合は、外部ファイルに設定ファイルとして持たせて、GAS実行時に外部ファイルを読み込む仕組みなどが必要)

参考: GASでテキストファイルの内容を読み取る - Qiita

プロジェクト一覧の取得

まずはお試しで自分が見えるプロジェクトの一覧を取得してみる。
プロジェクト一覧の取得 | Backlog Developer API | Nulab

メソッドはGETでURLは/api/v2/projects、クエリパラメーターは必須項目はないのでまずは省略してアクセスしてみる。

URLのベース部分は、ダッシュボードを開いたときのこの部分。

image.png

といっても、これだけ。

  var base_url = 'https://***.backlog.com'
  var endpoint = base_url + '/api/v2/projects';
  var apiKey = '*** api key ***';

  var url = endpoint + '?' + 'apiKey=' + apiKey;

  var resp = UrlFetchApp.fetch(url);

これで、https://***.backlog.com//api/v2/projects?apiKey=********にGETアクセスしたレスポンスがrespに入る。
レスポンスの内容はオブジェクトになっており、ステータスコード(getResponseCode())やレスポンスヘッダ(getAllHeaders())などいろいろセットされているが、APIからの応答であるレスポンスボディを参照するにはresp.getContentText()で取り出せる。
レスポンスの内容はAPI仕様の通りで、JSONの連想配列になっているプロジェクト情報が配列形式になっている。

これをJSON.parse()に通せば、オブジェクトとして操作できる。
プロジェクト名(name)とプロジェクトキー(projectKey)を出力する例。

  var json = JSON.parse(resp.getContentText())

  for(var i = 0; i < json.length; i++) {
    Logger.log("%s (key:%s)", json[i].name, json[i].projectKey)
  }

参考:

プロジェクトの作成

プロジェクトの追加 | Backlog Developer API | Nulab

URLはプロジェクト一覧の取得と同じ。
ただし、POSTメソッドを使って作成するプロジェクトのパラメタを付加する。

リクエストパラメーターがContent-Type:application/x-www-form-urlencodedとなっているので、HTTPリクエストヘッダにこれを設定。

そしてリクエストボディに表に記載されている項目を指定する。
curlコマンドであれば以下のような感じ。(APIキーは環境変数${API_KEY}にある前提)

$ curl -XPOST \
  -d 'name=PROJECT_NAME' \
  -d 'key=PJ_KEY' \
  -d 'chartEnabled=true' \
  -d 'subtaskingEnabled=true' \
  -d 'textFormattingRule=markdown' \
  -H 'Content-Type: application/x-www-form-urlencoded' \
  "https://******.backlog.com/api/v2/projects?apiKey=$API_KEY"

プロジェクト名などで日本語を使用する場合は、(いろいろ試した結果)UTF-8エンコードの文字列をURLエンコーディングすればOK

GASのコードで書こうとするとこんな感じ。
(プロジェクト名「ぼくのプロジェクト」、プロジェクトキー「PJKEY」で作成)

参考: Google Apps Script UrlFetchApp で Http Header を設定する | Monotalk

  var base_url = 'https://***.backlog.com'
  var endpoint = base_url + '/api/v2/projects';
  var apiKey = '*** api key ***';

  var url = endpoint + '?' + 'apiKey=' + apiKey;

  var params = {
    'name': 'ぼくのプロジェクト',
    'key': 'PJKEY',
    'chartEnabled': 'true',
    'subtaskingEnabled': 'true',
    'textFormattingRule': 'markdown',
  }
  var options = {
    'method': 'POST',
    'contentType': 'application/x-www-form-urlencoded',
    'payload': createQuery(params),
  }
  var resp = UrlFetchApp.fetch(url, options)

key1=value1&key2=value2&...の形式のリクエストボディを作成する標準関数的なものがあるのかどうかわからなかったので、以下のような関数を作成。(上記のpayloadの値に指定している部分)

日本語のURLエンコードについては標準で用意されているencodeURI()が使用できる。

参考: パーセント記号を使ったURL(URI)エンコード・デコード方法 - JavaScript TIPSふぁくとりー

// 連想配列をqueryにする関数
function createQuery(obj) {
  var query = []
  for(var key in obj) {
    query.push(key + '=' + encodeURI(obj[key]))
    //Logger.log(key)
  }
  return query.join('&')
}

これでプロジェクトの作成をAPIで実行できるようになった。

これだけだとwebの画面から手動で登録作業を行うのとあまり変わらないかもしれないけど、たとえば10件まとめて登録したい場合なんかは、登録したいプロジェクトの一覧のリストを用意し、ループで処理すればさくっと10件のプロジェクト作成ができるようになる。

マイルストーンの作成

ここから(「プロジェクト作成時に初期設定として定型のマイルストーンを作成したい」という要望の)本題。
といっても対象プロジェクトに対してマイルストーンを作成するBacklog APIを使用すればよい。

バージョン(マイルストーン)の追加 | Backlog Developer API | Nulab

API仕様をみると、「URLパラメーター」と「リクエストパラメーター」の二種類のパラメーターがあるが、要は「対象プロジェクトのプロジェクトキーをURLの一部に指定」「その他のパラメーターはリクエストボディに指定」という構成。

「URL」の仕様に:が入ってて紛らわしいけど、プロジェクトキー指定時は:は付けずに

'https://***.backlog.com/api/v2/projects/'+ project_id + '/versions'

って指定すればOK

ということで、指定プロジェクトに対して固定のマイルストーンを作成するには…

  var milestones = ["朝起きる",
                    "学校へ行く",
                    "授業受ける",
                    "部活に行く",
                    "帰宅する"]

というリスト形式のマイルストーンがあったとして、GASで指定プロジェクトに対してこの5つのマイルストーンを(必須項目のみで)作成するには以下のような感じ。

  var base_url = 'https://***.backlog.com'
  var endpoint = base_url + '/api/v2/projects/'+ project_id + '/versions'
  for (var i in milestones) {
    Logger.log("milestone: %s", milestones[i])

    var milestone_param = {
      'name': milestones[i]
    }

    var url = endpoint + '?' + 'apiKey=' + apiKey
    var milestone_option = {
      'method': 'POST',
      'contentType': 'application/x-www-form-urlencoded',
      'payload': createQuery(milestone_param),
    }
    var resp = UrlFetchApp.fetch(url, milestone_option)
  }

前述のプロジェクト作成もリスト処理できるようにして、このマイルストーン作成と組み合わせれば、初期設定として同じマイルストーン設定の複数のプロジェクトを、ワンアクションで一気に作成できるようになる。

10
9
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
10
9