概要

スプレッドシートを使うときに、細かいところに手が届かない、こんな機能があったら便利なのに、そんな悩みを解決するのがカスタム関数です。Google Apps Scriptを使う必要があるため一般的なスクリプトで処理を記述できるくらいの知識が必要になりますが、スプレッドシートへ複雑な関数の組み合わせを入力する必要がないため関数を見ながらシート上をさまようことがなくなります。
カスタム関数とGoogle Apps Scriptの書き方を覚えて省エネ開発ができるようになりましょう（希望)。

自己紹介

2014年12月よりQAエンジニアとして、業務効率化や障害削減に関係するツール類の作成を主として業務を行なっています。

はじめに

本記事はスプレッドシート及びGoogle Apps Script(以降GASと呼びます)に関する基本的な知識があることを前提として内容を記載していきます。GASを使用するための準備は過去に投稿した記事があるので読み進める上で理解しにくいところがある場合には参考にしてみてください。
Google Apps Scriptを使ってみよう(前編)
Google Apps Scriptを使ってみよう(後編)
Google Apps Scriptを使ってみよう(おまけ編)

カスタム関数とは

スプレッドシートであらかじめ用意されている関数以外に、GASで関数を定義することにより標準の関数と同様に関数の呼び出しを行うことができます。
標準で用意されている関数でも十分すぎる種類があり、それらを組み合わせることで目的の処理を行うことは可能であるとは思います。
一方で複雑に組み合わされた関数で構成されるスプレッドシートの運用、特に保守に関しては中々骨の折れる作業であることは皆さんもご存知なのではないでしょうか？
関数の組み合わせに頭を悩ますことがなくシートの見通しが良い状態で運用や保守を行えるところがカスタム関数のメリットの一つであると言えます。
今回はゲームアプリケーションでよく見られるキャラクタースキルを例に簡易化したモデルでカスタム関数の使用例と説明を、加えて運用面での利便性をあげる実装方法を解説していきます。

今回の記事で使用する要件と仕様

要件

• スキルIDを入力するとスキルに設定された内容に従いスキルの説明文を作成し出力する。
• 入力に対するエラーはできる限りその後の対応がしやすいように出力する。

仕様

• スキルはスキルID、スキル名、スキルタイプ、値で構成される。
• 説明文はスキルに定義されるスキルタイプ毎にテンプレートを用意する。テンプレート内の特定の文字列を対象にスキルマスタの値で置き換えることでスキル説明文を作成する。
• スキルマスタでは同一のスキルIDを許容しない。スキルIDが重複している場合にはその内容を説明文の代わりにエラーとして出力する。
• スキル詳細の表示を行う際、指定されたスキルが見つからない場合には説明文の代わりに見つからない旨を表示する。
• スキル詳細の表示を行う際、指定されたスキルのスキルタイプに応じたテンプレートが見つからない場合には説明文の代わりにスキルタイプに対応した説明文が見つからない旨を表示する

シート

シートは3つで構成

• スキルの内容を構成するマスタ(スキルマスタ)
• スキルタイプごとにスキルの説明文を出力するためのテンプレート(説明文マスタ)
• スキルIDを入力することでスキルの内容とテンプレートからスキルの説明文を出力するシート(スキル詳細表示シート)

マスターデータ

スキルマスタ

• スキルIDはユニーク

説明文マスタ

• スキルタイプはユニーク

スキル詳細表示シート

• A列はスキルID入力用、説明文を表示したいスキルのスキルIDを入力する
• スキルが見つかった場合スキル名とスキルマスタの内容から作成した説明文を表示する

準備

スキルマスタ

説明文マスタ

• 説明文マスタで「[値]」と表記している箇所を対象にスキルマスタの「値」で置き換えます。

スキル詳細表示シート

コード

//本来は読み込んだデータを連想配列にするべきだが今回はなるべく簡潔に要点を説明したいので配列を直接インデックスで参照することを避けるためにインデックスに対応する連想配列を使用した。
var MASTER_SKILL = {ID:0, NAME:1, TYPE:2, VALUE:3};
var MASTER_DESCRIPTION = {ID:0, TYPE:1, DESCRIPTION:2};

//入力されたスキルマスタのバリデーションを行う
//仕様に基づき実装する内容
// ・スキルIDが未定義
// ・スキルIDが重複
function validateSkillMaster(master_skill_list)
{
  if (master_skill_list == '#N/A') {
    throw new Error('ERR:指定したスキルIDのスキルが未定義です');
  }
  if (master_skill_list.length > 1) {
    throw new Error('ERR:スキルマスタにスキルIDが重複しています' + '[' + master_skill_list.length + ']');
  }
}

//スキル説明文を作成するカスタム関数
function CREATE_SKILL_DESCRIPTION(master_skill_list, master_description_list)
{
  try {
    validateSkillMaster(master_skill_list);
    master_skill = master_skill_list.shift();

    //スキルタイプが一致する説明文を探し、スキルに設定されている値で置き換える
    for (key in master_description_list) {
      if (master_description_list[key][MASTER_DESCRIPTION.TYPE] == master_skill[MASTER_SKILL.TYPE]) {
        description = master_description_list[key][MASTER_DESCRIPTION.DESCRIPTION];
        return [[master_skill[MASTER_SKILL.NAME], description.replace('[値]', master_skill[MASTER_SKILL.VALUE])]];
      }
    }
    //仕様に基づき実装する内容
    // ・スキルタイプが一致する説明文が見つかっていないのでエラーで返す
    return [[master_skill[MASTER_SKILL.NAME],'ERR:スキルタイプに対応する説明文が見つかりませんでした' + '[' + master_skill[MASTER_SKILL.TYPE] + ']']];
  } catch (e) {
    return [["", e.message]];
  }
}

スキル詳細表示シートへカスタム関数を記述を行う

• スキル詳細表示シートのB2のセルに「=IF(ISBLANK(A2),"",CREATE_SKILL_DESCRIPTION(query('スキルマスタ'!A2:D,"select A,B,C,D where A="&A2&""),query('説明文マスタ'!A$2:C,"select A,B,C")))」の内容をコピーして貼り付けます。
• その後B2セルをクリックすると青色の枠が表示されるので右下の少し大きくなった■をB10までドラッグします。

動作確認

今回はスキルマスタにID1から6までのデータを用意しました。
A1からA6までのセルに１から6まで順に入力します。

画像のように表示されたでしょうか？

正常系の入力に対しては期待通りに動作しているようですが、他にも仕様がありました。

1. スキルマスタでは同一のスキルIDを許容しない。スキルIDが重複している場合にはその内容を説明文の代わりにエラーとして出力する。
2. スキル詳細の表示を行う際、指定されたスキルが見つからない場合には説明文の代わりに見つからない旨を表示する。
3. スキル詳細の表示を行う際、指定されたスキルのスキルタイプに応じたテンプレートが見つからない場合には説明文の代わりにスキルタイプに対応した説明文が見つからない旨を表示する

1から順に確認していきましょう。
1を確認するためにはスキルマスタに重複したスキルIDを持つスキルが存在すれば良いため、スキルマスタに入力ずみのスキルID1の内容を定義ずみのスキルの下に貼り付けます。

こんな感じです。
早速スキル詳細表示シートを見てみましょう。

仕様通りスキルIDが重複しているという内容のエラーが表示されました。

次に2を確認します。
2番目の仕様は存在しないスキルIDを入力することで確認が可能です。
スキルID入力用のセルに２が入力されているところに「7」を入力します。

仕様通り入力したスキルIDに対応するスキルが未定義という内容でエラーが表示されました。

最後に３を確認します。
3を確認するためにはスキルマスタシートへ未定義のスキルタイプを持ったスキルを入力することで確認します。
実際に運用中のアプリの開発中に遭遇するシーンとしてはスキルの新規開発などになるかと思います。
今回は最大HPを200UPするという内容のスキルを想定してデータの入力を行ってみます。

この状態だとスキルマスタにはMAX_HP_UPというスキルタイプが存在していますが、説明文マスタには存在しないためスキル詳細表示シートではエラーとして表示される必要があります。どうでしょうか。

しっかりとスキルタイプに対応する説明文が見つからないという内容でエラーが表示されました。
せっかくなので最大HPアップについてもスキルの説明文を追加しておきましょう。

こんな感じで追加しました。
スキル詳細表示シートを確認します。

新しく追加した内容もしっかりと表示することができました。

解説

今回紹介したカスタム関数の使い方の中で特に注目してほしいところは「コード」のセクションでtry...catchを使用しているところになります。
カスタム関数を使用する場合、スクリプトエディタのデバッグ機能が使用できない(厳密には個別の関数については使用できるがシートから値を渡す所はテスト用の関数を作成しその関数を呼び出すことで対象のコードをテストすることになる)ため問題になる箇所の対応を行う際に手がかりになる情報を文字列として呼び出し元へ返し最終的にシート上に表示することで利便性の向上を図っています。

まとめ

今回はゲームアプリケーションでよくみられるマスターデータをモデルとしてカスタム関数の使用例と利便性向上のための実装方法について紹介をしました。
スクリプトを書き慣れていない方には少し難しい内容となっているかもしれませんが、カスタム関数を利用するきっかけとなりましたら幸いです。