Google Apps ScriptとclaspでBacklogの情報をGoogleスプレッドシートに取得しよう

こんにちは。Backlog開発チームのshomaです。

先日、GoogleスプレッドシートからBacklogへ課題を一括登録できるツール(以降、課題一括登録ツールといいます)を、Nulabの公式ツールとしてリリースしました。プレスリリース：Backlog、Googleスプレッドシートから課題の一括登録が可能に

この記事では、GoogleスプレッドシートとGoogle Apps Scriptを用いたアプリケーションの作成についてご紹介します。

課題一括登録ツールとは？

課題一括登録ツールでは、Googleスプレッドシート上にBacklogの課題を入力しておくことで、Backlogに一括してその課題を登録することができます。定期的に同じタスクを作成されている場合など、ぜひご活用ください。詳細のご利用方法等はREADME.mdをご参照ください。

このツールは弊社のikikkoが個人的に開発していたプロダクトですが、ご利用者数が多いことや機能追加、サポート体制を強化するためヌーラボの公式ツールとなりました。今回、リリースの大きな目玉はカスタム属性に対応したことです。それ以外に多くの内部的な改善を行っており、ローカル環境での開発をしやすくしました。主な新機能は以下の通りです。

• カスタム属性対応
• 担当者やカテゴリ等をリストから選択可能に
• 多言語対応

Google Apps Scriptとは

Google Apps Script(以降、GASといいます)とは、Googleが提供するクラウド内のJavaScriptの実行環境です。ドキュメント、スプレッドシート、スライド、フォームなどのG Suite製品で様々なことを実現できます。主にブラウザでコードエディタを使用し、Googleのサーバーでスクリプトを実行します。

スクリプトの種類について

GASにはContainer Bound ScriptとStandalone Scriptの２種類が存在し、それぞれ特徴があるためやりたいことに合わせてどちらのスクリプトを使用するか決定します。

Standalone ScriptStandalone

Standalone Scriptは、Googleドライブのファイルに単体として存在できるスクリプトのことです。このスクリプトはWebアプリケーションとして実行したり、ライブラリとして公開することができます。

Container Bound Script

Container Bound Scriptはドキュメントやスプレッドシート、フォーム内のみで作成することができるスクリプトです(マクロのような扱いです)。このスクリプトは通常、Googleドライブには表示されません。その代わりにStandalone Scriptとは違い、親ファイル特有の関数を呼び出すことが出来ます。

claspとは

claspはGoogle公式のGAS用コマンドラインツールで、スクリプトの作成(Standalone Scriptのみ)や複製、アップロード機能などを提供します。

GASの開発手法

先ほど述べたように、GASは主にブラウザのオンラインエディタで開発します。このエディタがかなり使いづらく、ある程度の補完機能はあるものの、普段IDEで開発されている方にはツライのではないかと思います。

また、JavaScriptを開発言語として使用しますがES2015などの新しい構文に対応していません。今回、課題一括登録ツールの開発にあたって以下の点を解消すべく試行錯誤しました。

• 普段使い慣れたエディタで開発したい(スクリプトをオンラインエディタにコピペすらしたくない)
• モダンな言語仕様を取り入れたい
• TypeScriptを使いたい(型っていいですよね)
• 単体テストしたい

これ以降はサンプルアプリケーションを通して、これらの不満点を全て解消したモダ〜ンな開発手法をご紹介します。

作るモノ

本記事のサンプルアプリケーションでは、Googleスプレッドシート内にBacklogのプロジェクトに属するユーザーを一覧で取得できるようにします。また、以下のことをできるようになります。

• Googleスプレッドシートを開いたことをトリガーにして処理を実行する
• Googleスプレッドシートにカスタムメニューを追加する
• Googleスプレッドシート内に値を保存して、あとから参照できるようにする
• Container Bound Script固有の関数を使用してGoogleスプレッドシートを操作する
• claspを使ってオンラインエディタを使用することなく作ったファイルをアップロードする
• UrlFetchApp(GAS用Httpクライアント)で外部サイトにアクセスする

完成品はGithub: shomatan/backlog-gas-exampleです。

必須条件

• Node.js
• npm

claspをインストールする

npm i -g @google/clasp@1.4.1

claspを使用してGoogleアカウントの認証を行う

claspからスクリプトをアップロードするにはGoogleのアカウント認証が必要です。以下のコマンドを実行します。

clasp login

すると次のようなメッセージが表示され、ブラウザが起動します。

No credentials given, continuing with default...
Authorize clasp by visiting this url:    https://accounts.google.com/o/oauth2/v2/auth?access_type ... (省略)    

[許可]をクリックします。

Logged in! You may close this page.

と表示されればログインは完了です。

空のGoogleスプレッドシートを作成する

Googleドライブから[新規]→[Googleスプレッドシート]→[空白のスプレッドシート]を選択します。

スクリプトエディタを開き、スクリプトのプロジェクトを作成する

Container Bound Scriptではclaspを使用してプロジェクトを作成することができませんので、ここだけ手作業で行います。メニューの[ツール]→[スクリプトエディタ]を選択します。

スクリプトエディタが開いたら[ファイル]→[保存]をクリックするとプロジェクト名を入力するダイアログが表示されますので、適当な名前を入力し[OK]をクリックします。

ここでGoogleスプレッドシート側の作業は完了です。一旦そのままにしておきます。

npmの初期設定を行う

適当なディレクトリを作成後、そのディレクトリ内で以下のコマンドを実行します。       

npm init -y

モジュールのインストール

TypeScriptをインストールします。

npm i -D typescript

TypeScriptで開発するために、Google Apps Script用の型定義ファイルを取得します

npm i -D @types/google-apps-script

テスト周りのモジュールをインストールします。

npm i -D jest @types/jest ts-jest

webpack周りのモジュールをインストールします

npm i -D webpack webpack-cli gas-webpack-plugin ts-loader

webpackがdist以下にファイルを生成するので、htmlファイルも同様にdistに出力するようにします

npm i -D html-webpack-plugin html-loader

webpack.config.js

webpack.config.jsを次のように作成します。ポイントはgas-webpack-pluginを追加していることです。このプラグインを使えばGASから関数を認識させることができます。GASでは、エントリポイントがgoogle.script.runから呼び出される最上位レベルの関数宣言である必要があります。このプラグインは最上位レベルの関数宣言文を生成してくれます。

const path = require('path');
const GasPlugin = require("gas-webpack-plugin");
const HtmlWebpackPlugin = require('html-webpack-plugin')

module.exports = {
  mode: 'development',
  entry: './src/index.ts',
  devtool: false,
  output: {
    filename: 'bundle.js',
    path: path.join(__dirname, 'dist')
  },
  module: {
    rules: [
      {
        test: /\.ts$/,
        use: 'ts-loader'
      },
      {
        test: /\.html$/,
        loader: "html-loader"
      }
    ]
  },
  resolve: {
    extensions: [
      '.ts'
    ]
  },
  plugins: [
    new GasPlugin(),
    new HtmlWebpackPlugin({
      template: "./index.html"
    })
  ]
};

tsconfig.json

tsconfig.jsonを次のように作成します。

{
  "compilerOptions": {
    "target": "es5",
    "module": "es2015",
    "lib": ["es6"]
  }
}

npm scripts

package.jsonのscriptsを以下のように変更します。

  "scripts": {
    "test": "jest",
    "build": "webpack && npm run test",
    "push": "npm run build && clasp push"
  },

jest

package.jsonに以下の内容を追加します。

  "jest": {
    "verbose": true,
    "testURL": "http://localhost/",
    "transform": {
      "^.+\\.tsx?$": "ts-jest"
    },
    "testRegex": "(src/.+(\\.|/)(test|spec))\\.(jsx?|tsx?)$",
    "moduleFileExtensions": [
      "ts",
      "tsx",
      "js",
      "jsx",
      "json",
      "node"
    ]
  }

claspの設定

.clasp.jsonを次のように作成します。

{
  "scriptId": "SCRIPT_ID",
  "rootDir": "dist"
}

scriptIdには[スクリプト ID]を指定します。rootDirを指定することで、distディレクトリ以下のファイルをアップロードの対象とします。

つくってみる

今回のようなフォームアプリケーションでは、以下で説明するファイルを作成します。長くなるので全て見るには各リンクを参照ください。

index.html

index.htmlを作成します。
GASではFormのSubmitを使用できないため、以下のようにSubmitを無効にするコードを挿入します。

<script>
  // Prevent forms from submitting.
  function preventFormSubmit() {
    var forms = document.querySelectorAll('form');
    for (var i = 0; i < forms.length; i++) {
      forms[i].addEventListener('submit', function(event) {
        event.preventDefault();
      });
    }
  }
  window.addEventListener('load', preventFormSubmit);
...

Submitされたときの処理を追加します。google.script.run.関数名(引数)で関数を呼び出せます。このサンプルアプリケーションではexecute関数内にメインの処理を記述しているのでそのように指定します。

...     
  function onSuccess(result) {
    google.script.host.close();
  }
  function onFailure(e) {
    alert([e.message, e.stack]);
  }
  function handleFormSubmit(formObject) {
    google.script.run
      .withSuccessHandler(onSuccess)
      .withFailureHandler(onFailure)
      .execute(formObject);
  }
</script>

index.ts

src/index.tsを作成します。

onOpen関数はスプレッドシートを開いたときに実行されるフック関数です。ここではメニューを追加するように指定しています。addItemの第2引数はメニューをクリックしたときに実行する関数名を指定します。SpreadsheetAppは親ファイルとなるスプレッドシートを操作する関数群を提供します。

global.onOpen = function() {
  SpreadsheetApp
    .getUi()
    .createMenu('Backlog')
    .addItem('実行フォームを開く', 'showDialog')
    .addToUi()
}

showDialog関数は[実行フォームを開く]メニューをクリックしたときに実行されます。テンプレートであるindex.htmlを評価し、モーダルダイアログとして表示します。

global.showDialog = function() {
  const html = HtmlService
    .createTemplateFromFile('index')
    .evaluate()	

  SpreadsheetApp
    .getUi()
    .showModalDialog(html, 'Backlogからプロジェクトユーザーを取得するサンプルアプリケーション')
}

execute関数はフォームの[実行]ボタンをクリックしたときに呼び出されます。

global.execute = function(config: Config): string {
  const propertyService = UserPropertyServiceImpl()
  const httpClient = HttpClientImpl()

  // フォームに入力された値を保存し、次回から入力しなくてもいいようにする
  storeConfig(config, propertyService)

  // プロジェクトユーザー一覧を取得する
  const uri = `${config.url}/api/v2/projects/${config.projectKey}/users?apiKey=${config.apiKey}`
  const usersJson = httpClient.get(uri)
  const users = Object.keys(usersJson).map(key => jsonToUser(usersJson[key]))

  // スプレッドシートに新たなシートを追加する
  const sheetName = Utilities.formatDate(new Date(), "JST", "yyyy/MM/dd HH:mm:ss")
  const sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName, 0)

  // ヘッダーを設定
  sheet.getRange(1, 1).setValue("ID")
  sheet.getRange(1, 2).setValue("ユーザーID")
  sheet.getRange(1, 3).setValue("名前")
  sheet.getRange(1, 4).setValue("メールアドレス")

  // データを出力
  for (let i = 0; i < users.length; i++) {
    const rowNumber = i + 2
    const user = users[i]

    sheet.getRange(rowNumber, 1).setValue(user.id)
    sheet.getRange(rowNumber, 2).setValue(user.userId)
    sheet.getRange(rowNumber, 3).setValue(user.name)
    sheet.getRange(rowNumber, 4).setValue(user.mailAddress)
    SpreadsheetApp.flush()
  }
  return "success"
}

UserPropertyService.ts

src/UserPropertyService.tsを作成します。設定などの入力値をスプレッドシートに保存したり参照する場合は、PropertiesServiceが便利です。今回はBacklogのurl, apiKey, projectKeyの入力値を保存するために使っています。

// keyで指定した値を取得する
PropertiesService.getUserProperties().getProperty(key)
// keyで値を指定して保存する
PropertiesService.getUserProperties().setProperty(key, value)

HttpClient.ts

src/HttpClient.tsを作成します。外部サイトにアクセスする場合は、UrlFetchAppを使用します。

// Getの例
const response = UrlFetchApp.fetch(uri)
const content = response.getContentText()

Postする場合はURLFetchRequestOptionsを以下のように指定することで実行できます。

// Postの例
const options = {
  method: "post",
  payload: data
}
const response = UrlFetchApp.fetch(uri, options)
const content = response.getContentText()

アップロード

npm run push     

スクリプトエディタを開くとファイルがアップロードされていることが確認できます。ちなみに.jsファイルはオンラインエディタ上では自動的に.gsへと変換されます。

実行

onOpen メソッド内を変更した場合は、スプレッドシート自体をリロードする必要があります。しばらくするとHelpメニューの隣にBacklogというメニューが現れます。

[Backlog]→[実行フォームを開く]をクリックします。

初回実行時には承認が必要になります。[許可]をクリックします。
各項目を入力し、[実行]ボタンをクリックします。しばらくすると新しいシートが追加され、プロジェクトに属するユーザー一覧を取得します。

おわりに

GASは高機能で便利な反面、オンラインエディタに不満がありましたが、普段使い慣れた方法で開発できるまで近づけられたのではないでしょうか。ぜひBacklogと連携して面白いアプリケーションを作ってみてください！