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

# はじめに

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

で、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】スクリプト実行時の「承認」でびっくりしないために](https://tonari-it.com/gas-script-approval/)

## スプレッドシートの操作

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

- [【保存版】初心者向け実務で使えるGoogle Apps Script完全マニュアル](https://tonari-it.com/google-apps-script-manual/)
    - [連載目次：超初心者向けGASでBotを作りながら基礎を学ぶ](https://tonari-it.com/google-apps-script-manual/#toc2)

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

### スクリプトの作成

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/6f8de454-9711-b834-2afa-85a1c8aa7292.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/fbd9bf42-d44e-bba5-834f-928a0e0762aa.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/77eb2d22-0982-2c6c-2e15-36ae8db41ff5.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/715cdaa0-f2a3-2baa-1e84-ac3374b690b9.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/44d5523d-0adc-654a-49b8-9f5d27601e53.png)

#### 保存と実行とログ出力

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

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/6ed0d8d8-0fd7-071d-7d86-90a0ca30502a.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/dae264b7-acf6-92b1-35d6-6fa3d6b489c8.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/bcfc3cd5-b832-7672-110f-9959c016cb5b.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/fb27db8f-6756-d995-fad7-100257b0956b.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/5cef512f-2f34-1069-211e-0d38a48caa63.png)

参考: [【初心者向けGAS】はじめてのスクリプトを作成し、保存し、実行する](https://tonari-it.com/gas-script-create-save-run/)

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

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

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

#### スプレッドシートを開く

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

```javascript
var ss = SpreadsheetApp.getActiveSpreadsheet()
```

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

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

参考: [【初心者向けGAS】Spreadsheetサービスの「オブジェクト」の基礎の基礎を知ろう](https://tonari-it.com/gas-spreadsheet-object/)

#### シートを開く

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/60da0a40-57a1-0ffb-1571-d56b0ce4040b.png)

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

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

参考: [【初心者向けGAS】スプレッドシートのシートを取得する２つの方法](https://tonari-it.com/gas-spreadsheet-get-sheet/)

#### セルを取得する

セルを取得するには何通りか方法があるが、基本はまず`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列目」を取得する

```javascript
  var cell = sheet.getRange("A1")
```

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

```javascript
  var lastRow = sheet.getLastRow();
```

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

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

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

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

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

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

参考: [【初心者向けGAS】スプレッドシートのセル・セル範囲とその値を取得する方法](https://tonari-it.com/gas-spreadsheet-range-value-values/)

### メニューの追加

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/b684462e-df57-fa62-f16c-f107401af722.png)

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

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

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

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

参考: [スプレッドシートとGASでTrelloのリストにカードを作成するツール / スプレッドシートのメニューに項目追加](https://tonari-it.com/spreadsheet-gas-trello-createcard/#toc4)

## REST APIの実行

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

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

[認証と認可 | Backlog Developer API | Nulab](https://developer.nulab.com/ja/docs/backlog/auth/)

### APIキーの設定

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/cca0afa1-9ac3-2e48-9f98-616511de7033.png)

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

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

参考: [GASでテキストファイルの内容を読み取る - Qiita](https://qiita.com/golyat/items/b0c567c28e0e1ee80526)

### プロジェクト一覧の取得

まずはお試しで自分が見えるプロジェクトの一覧を取得してみる。
[プロジェクト一覧の取得 | Backlog Developer API | Nulab](https://developer.nulab.com/ja/docs/backlog/api/2/get-project-list/)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/a31c48d2-78c7-3166-b1a2-c54a2ee13a0a.png)

といっても、これだけ。

```javascript
  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`)を出力する例。

```javascript
  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)
  }
```

参考:

- [【Google Apps Script】天気予報をWeb APIで取得する方法](https://tonari-it.com/gas-api-weather/)
- [Google Apps ScriptでREST APIを使って郵便番号住所変換スプレッドシート関数を作る](https://tonari-it.com/gas-rest-api-zip-address/)

### プロジェクトの作成

[プロジェクトの追加 | Backlog Developer API | Nulab](https://developer.nulab.com/ja/docs/backlog/api/2/add-project/)

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

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

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

```sh
$ 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](https://www.monotalk.xyz/blog/google-apps-script-urlfetchapp-%E3%81%A7-http-header-%E3%82%92%E8%A8%AD%E5%AE%9A%E3%81%99%E3%82%8B/)

```javascript
  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ふぁくとりー](https://www.nishishi.com/javascript-tips/encodeuri.html)

```javascript
// 連想配列を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](https://developer.nulab.com/ja/docs/backlog/api/2/add-version-milestone/)

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

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

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

って指定すればOK

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

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

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

```javascript
  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)
  }
```

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