このページは Cloud Translation API によって翻訳されました。

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 の記述方法が不明で、学習する時間がない場合は、アドオンストアを確認して、必要なカスタム関数が他のユーザーにすでに作成されていないか確認してください。

カスタム関数の作成

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

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

これで、カスタム関数を使用できます。

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

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

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]]) と解釈されています。上記のサンプルコードは、配列を入力として受け入れるように変更する必要があります。
カスタム関数の引数は確定的である必要があります。つまり、計算のたびに異なる結果を返す組み込みのスプレッドシート関数（NOW() や RAND() など）を、カスタム関数の引数として使用することはできません。カスタム関数が、これらの揮発性組み込み関数のいずれかに基づいて値を返そうとした場合、Loading... は無期限に表示されます。

戻り値

すべてのカスタム関数は、次のような値を返す必要があります。

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

データタイプ

Google スプレッドシートは、データの性質に応じてデータをさまざまな形式で保存します。これらの値をカスタム関数で使用すると、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 The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

高度

Google Apps Script サービスの使用

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

他のほとんどの 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 サービスをすべて使用できます。

カスタム関数は、作成先のスプレッドシートの「バインド」から始まります。つまり、あるスプレッドシートで記述したカスタム関数は、次のいずれかの方法を使用しない限り、他のスプレッドシートで使用できません。

[拡張機能] > [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;
}

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