Google スプレッドシートのカスタム関数

Google スプレッドシートには、AVERAGE、SUM、VLOOKUP などの数百の組み込み関数が用意されています。ご自分のニーズに合わない場合は、Google Apps Script を使用してカスタム関数を記述できます。たとえば、メートルをマイルに変換したり、インターネットからライブ コンテンツを取得したりするカスタム関数を作成し、組み込み関数と同じように Google スプレッドシートで使用できます。

ご利用にあたって

カスタム関数は、標準の JavaScript を使用して作成します。JavaScript を初めて使用する場合は、Codecademy の初心者向けのコースをご覧ください。（注: このコースは Google が開発したもので、Google との関連はありません）。

次に、入力値に 2 を乗算する DOUBLE という名前のシンプルなカスタム関数を示します。

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

JavaScript の記述方法がわからず、学ぶ時間がない場合は、アドオンストアを確認して、必要なカスタム関数を他のユーザーがすでに作成していないか確認してください。

カスタム関数を作成する

カスタム関数を作成するには:

  1. Google スプレッドシートでスプレッドシートを作成するか、開きます。
  2. メニュー項目の [拡張機能] > [Apps Script] を選択します。
  3. スクリプト エディタ内のコードをすべて削除します。上記の DOUBLE 関数の場合、コードをコピーしてスクリプト エディタに貼り付けます。
  4. 上部の保存アイコン（）をクリックします。

これで、カスタム関数を使用できるようになりました。

Google Workspace Marketplaceからカスタム関数を取得する

Google Workspace Marketplace には、Google スプレッドシートのアドオンとして複数のカスタム関数が用意されています。これらのアドオンを使用または探索するには:

  1. Google スプレッドシートでスプレッドシートを作成するか、開きます。
  2. 上部にある [アドオン] > [アドオンを取得] をクリックします。
  3. Google Workspace Marketplace が開いたら、右上の検索ボックスをクリックします。
  4. 「カスタム関数」と入力して、Enter キーを押します。
  5. 目的のカスタム関数アドオンが見つかった場合は、[インストール] をクリックしてインストールします。
  6. ダイアログ ボックスで、アドオンに認証が必要であることを伝える場合があります。その場合は、通知をよく読み、[許可] をクリックします。
  7. スプレッドシートでアドオンが使用可能になります。別のスプレッドシートでアドオンを使用するには、そのスプレッドシートを開き、上部にある [アドオン] > [アドオンを管理] をクリックします。使用するアドオンを見つけて、オプション > [このドキュメントで使用] をクリックします。

カスタム関数の使用

カスタム関数を記述するかGoogle Workspace Marketplaceからインストールすると、組み込み関数と同じくらい簡単に使用できます。

  1. 関数を使用するセルをクリックします。
  2. 等号（=）に続けて関数名と任意の入力値（=DOUBLE(A1) など）を入力し、Enter キーを押します。
  3. セルにすぐに Loading... が表示され、結果が返されます。

カスタム関数のガイドライン

独自のカスタム関数を作成する前に、知っておくべきガイドラインがいくつかあります。

命名

JavaScript 関数の標準的な命名規則に加えて、次の点に注意してください。

  • カスタム関数の名前は、SUM() などの組み込み関数の名前とは異なる必要があります。
  • カスタム関数の名前の末尾をアンダースコア（_）にすることはできません。アンダースコア（_）は、Apps Script の非公開関数を表します。
  • カスタム関数の名前は、var myFunction = new Function() ではなく function myFunction() 構文で宣言する必要があります。
  • スプレッドシートの関数の名前は基本的に大文字ですが、大文字表記は重要ではありません。

引数

組み込み関数と同様に、カスタム関数は入力値として引数を取ることができます。

  • 単一のセルへの参照（=DOUBLE(A1) など）を引数として関数を呼び出す場合、引数はセルの値になります。

  • セル範囲への参照を引数として関数（=DOUBLE(A1:B10) など）を呼び出すと、引数はセル値の 2 次元配列になります。たとえば、以下のスクリーンショットでは、=DOUBLE(A1:B2) の引数は Apps Script によって double([[1,3],[2,4]]) として解釈されます。上記の DOUBLE のサンプルコードは、入力として配列を受け入れるように変更する必要があります。


  • カスタム関数の引数は確定的である必要があります。つまり、計算のたびに異なる結果を返す組み込みのスプレッドシート関数（NOW() や RAND() など）は、カスタム関数への引数として使用できません。カスタム関数がこのような揮発性の組み込み関数のいずれかに基づいて値を返そうとすると、Loading... が無期限に表示されます。

値を返す

すべてのカスタム関数は、表示する値を返す必要があります。次に例を示します。

  • カスタム関数が値を返すと、関数の呼び出し元のセルにその値が表示されます。
  • カスタム関数が値の 2 次元配列を返す場合、隣接するセルが空である限り、値は隣接するセルにオーバーフローします。これにより、配列によって既存のセルの内容が上書きされる場合、カスタム関数によって代わりにエラーがスローされます。例については、カスタム関数の最適化をご覧ください。
  • カスタム関数は、値を返すセル以外のセルには作用しません。 つまり、カスタム関数で任意のセルを編集することはできません。編集できるのは、関数を呼び出したセルとその隣接するセルのみです。任意のセルを編集するには、カスタム メニューを使用して関数を実行します。
  • カスタム関数呼び出しは、30 秒以内に結果を返す必要があります。有効になっていない場合は、セルにエラー Internal error executing the custom function. が表示されます。

データ型

Google スプレッドシートでは、データの性質に応じてさまざまな形式でデータが保存されます。これらの値がカスタム関数で使用される場合、Apps Script は値を JavaScript の適切なデータ型として扱います。混乱を招きやすい分野は次のとおりです。

  • スプレッドシートの日付と時刻は、Apps Script では Date オブジェクトになります。スプレッドシートとスクリプトで異なるタイムゾーンが使用されている（まれな問題）場合は、カスタム関数で補正する必要があります。
  • スプレッドシートの Duration 値も Date オブジェクトになりますが、その操作は複雑な場合があります。
  • Apps Script では、スプレッドシートのパーセンテージ値は 10 進数になります。たとえば、Apps Script では値が 10% のセルは 0.1 になります。

予測入力

Google スプレッドシートでは、組み込み関数と同様に、カスタム関数の予測入力がサポートされています。セルに関数名を入力すると、その内容に一致する組み込み関数とカスタム関数のリストが表示されます。

下の DOUBLE() の例のように、スクリプトに JsDoc @customfunction タグが含まれている場合、カスタム関数がこのリストに表示されます。

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

高度な絞り込み

Google Apps Script サービスの使用

カスタム関数は、特定の Google Apps Script サービスを呼び出して、より複雑なタスクを実行できます。たとえば、カスタム関数で言語サービスを呼び出して、英語のフレーズをスペイン語に翻訳できます。

他のほとんどのタイプの Apps Script とは異なり、カスタム関数が個人データへのアクセスの承認をユーザーに求めることはありません。したがって、個人データにアクセスできないサービスのみを呼び出すことができます。具体的には、次のものがあります。

サポート対象のサービス メモ
キャッシュ 機能するが、カスタム関数では特に役に立たない
HTML HTML を生成できるが、表示できない（ほとんど有用ではない）
JDBC
言語
ロック 機能するが、カスタム関数では特に役に立たない
マップ ルートの計算はできますが、地図の表示はできません
プロパティ getUserProperties() は、スプレッドシートのオーナーのプロパティのみを取得します。スプレッドシート エディタでは、カスタム関数にユーザー プロパティを設定することはできません。
スプレッドシート 読み取り専用（ほとんどの get*() メソッドを使用できますが、set*() は使用できません）。
他のスプレッドシート（SpreadsheetApp.openById() または SpreadsheetApp.openByUrl()）を開くことはできません。
URL 取得
ユーティリティ
XML

カスタム関数がエラー メッセージ You do not have permission to call X service. をスローした場合、サービスはユーザー承認を必要とするため、カスタム関数では使用できません。

上記以外のサービスを使用するには、カスタム関数を作成する代わりに、Apps Script 関数を実行するカスタム メニューを作成します。メニューからトリガーされる関数は、必要に応じてユーザーに承認を求めます。その結果、Apps Script サービスすべてを使用できます。

共有

カスタム関数は、最初は作成されたスプレッドシートにバインドされています。つまり、1 つのスプレッドシートで作成されたカスタム関数は、次のいずれかの方法を使用しない限り、他のスプレッドシートでは使用できません。

  • [拡張機能] > [Apps Script] をクリックしてスクリプト エディタを開き、元のスプレッドシートからスクリプト テキストをコピーして、別のスプレッドシートのスクリプト エディタに貼り付けます。
  • [ファイル] > [コピーを作成] をクリックして、カスタム関数を含むスプレッドシートのコピーを作成します。スプレッドシートをコピーすると、そのスプレッドシートに関連付けられているスクリプトもコピーされます。スプレッドシートにアクセスできるユーザーは誰でもスクリプトをコピーできます。（閲覧権限しかない共同編集者は、元のスプレッドシートでスクリプト エディタを開くことができません。ただし、ユーザーがコピーを作成すると、そのユーザーがコピーのオーナーになり、スクリプトを閲覧できます）。
  • スクリプトを Google スプレッドシートのエディタ アドオンとして公開します。

最適化

カスタム関数がスプレッドシートで使用されるたびに、Google スプレッドシートは Apps Script サーバーを個別に呼び出します。スプレッドシートにカスタム関数の呼び出しが数十個（数百、数千）ある場合、処理にかなりの時間がかかります。

そのため、広い範囲のデータに対してカスタム関数を複数回使用する場合は、2 次元配列の形式で範囲を入力として受け入れ、適切なセルにオーバーフローできる 2 次元配列を返すように関数を変更することを検討してください。

たとえば、上記の DOUBLE() 関数は、次のように 1 つのセルまたはセル範囲を受け入れるように書き換えることができます。

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

上記のアプローチでは、JavaScript の Array オブジェクトの map メソッドを使用して、セルの 2 次元配列のすべての値に対して DOUBLE を再帰的に呼び出します。結果を含む 2 次元配列が返されます。これにより、以下のスクリーンショットに示すように、DOUBLE を 1 回呼び出して、多数のセルを一度に計算できます。（map 呼び出しの代わりに、ネストされた if ステートメントを使用して同じことを実現できます）。

同様に、以下のカスタム関数はインターネットからライブ コンテンツを効率的に取得し、2 次元配列を使用して 1 回の関数呼び出しで 2 列の結果を表示します。各セルに独自の関数呼び出しが必要な場合、Apps Script サーバーが毎回 XML フィードをダウンロードして解析しなければならないため、操作にかなりの時間がかかります。

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

これらの手法は、スプレッドシート全体で繰り返し使用されるほぼすべてのカスタム関数に適用できますが、実装の詳細は関数の動作によって異なります。