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

Google スプレッドシートには、AVERAGE、SUM、VLOOKUP など、何百もの組み込み関数が用意されています。このような関数だけでは足りない場合は、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 の記述方法がわからない場合や、学習する時間がない場合は、Google Workspace アドオンストアで、必要なカスタム関数がすでに作成されているかどうかを確認してください。

カスタム関数を作成する

カスタム関数を作成する手順は次のとおりです。

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

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

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

Google Workspace Marketplace では、Google スプレッドシート用 Google Workspace アドオンとして、いくつかのカスタム関数が提供されています。これらのアドオンを使用または確認するには:

Google スプレッドシートでスプレッドシートを作成するか、開きます。
上部にある [アドオン] > [アドオンを取得] をクリックします。
Google Workspace Marketplace が開いたら、右上の検索ボックスをクリックします。
「カスタム関数」と入力して Enter キーを押します。
興味のあるカスタム関数アドオンが見つかったら、[インストール] をクリックしてインストールします。
アドオンに承認が必要であることを示すダイアログが表示されることがあります。その場合は、注意事項をよく読んで [許可] をクリックします。
アドオンがスプレッドシートで使用できるようになります。別個のスプレッドシートでアドオンを使用するには、そのスプレッドシートを開き、上部にある [アドオン] > [アドオンの管理] をクリックします。使用するアドオンを見つけて、[オプション] > [このドキュメントで使用] をクリックします。

カスタム関数を使用する

カスタム関数を作成するか、Google Workspace Marketplace からインストールすると、組み込み関数と同じように使用できます。

関数を使用するセルをクリックします。
等号（=）の後に、関数名と入力値を入力します（例: =DOUBLE(A1)）。入力したら、Enter キーを押します。
セルに 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... が無限に表示されます。
再計算をトリガーするには、参照されるセルまたはセル範囲を引数としてカスタム関数に直接渡す必要があります。それ以外の場合、関数を編集するか、参照先のセルの値を変更するまで、カスタム関数は再計算されません。カスタム関数で getValue メソッドを使用する場合、参照される範囲はカスタム関数の引数として直接渡されないことに注意してください。

戻り値

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

カスタム関数が値を返すと、その関数が呼び出されたセルに値が表示されます。
カスタム関数が値の 2 次元配列を返す場合、その値は、隣接するセルが空である限り、隣接するセルにオーバーフローします。この配列によって既存のセルの内容が上書きされる場合は、カスタム関数がエラーをスローします。例については、カスタム関数を最適化するをご覧ください。
カスタム関数は、値を返すセル以外のセルに影響を与えることはできません。つまり、カスタム関数は任意のセルを編集することはできず、呼び出し元のセルとその隣接するセルのみを編集できます。任意のセルを編集するには、カスタムメニューを使用して関数を実行します。
カスタム関数呼び出しは 30 秒以内に戻る必要があります。そうでない場合、セルには #ERROR! が表示され、セルのメモは Exceeded maximum execution time (line 0). になります。

データ型

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

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

予測入力

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

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

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

高度

このセクションでは、高度なカスタム関数に関するトピックについて説明します。

Google Apps Script サービスを使用する

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

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

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

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

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

カスタム関数は、作成されたスプレッドシートにバインドされた状態で開始されます。つまり、次のいずれかの方法を使用しない限り、あるスプレッドシートで作成したカスタム関数を別のスプレッドシートで使用することはできません。

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

コンテナにバインドされたすべてのスクリプトは、コンテナと同じアクセスリストを共有します。つまり、スプレッドシートの編集権限を持つユーザーは、スプレッドシートに添付されている Apps Script コードも編集できます。詳細については、バインドされたスクリプトへのアクセスをご覧ください。

最適化

スプレッドシートでカスタム関数が使用されるたびに、Google スプレッドシートは Apps Script サーバーに個別の呼び出しを行います。スプレッドシートにカスタム関数呼び出しが数十（または数百、数千）含まれている場合、このプロセスは遅くなる可能性があります。カスタム関数が多数あるプロジェクトや複雑なカスタム関数があるプロジェクトでは、実行が一時的に遅延することがあります。

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

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

/**
 * 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;
}

このアプローチでは、セルの 2 次元配列に対して JavaScript の Array オブジェクトの map メソッドを使用して各行を取得し、各行に対して map を再度使用して各セルの値を 2 倍にします。結果を含む 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;
}

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