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

コレクションでコンテンツを整理 必要に応じて、コンテンツの保存と分類を行います。

Google スプレッドシートには、AVERAGESUMVLOOKUP などの何百もの組み込み関数が用意されています。要件に十分でない場合は、Google Apps Script でカスタム関数を記述できます。たとえば、メートル単位からマイル数に変換したり、インターネットからライブ コンテンツを取得したりできます。これらの関数は、組み込み関数と同様に Google スプレッドシートで使用できます。

使用を開始する

カスタム関数は、標準の JavaScript を使用して作成されます。JavaScript を初めて使用する場合は、Codecademy で初心者向けのコースをおすすめします。(注: このコースは Google が設計したものではなく、Google とは関連付けられていません。)

ここでは、DOUBLE という名前のシンプルなカスタム関数を使用しています。この関数は入力値に 2 を乗算します。

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 オブジェクトになります。スプレッドシートとスクリプトのタイムゾーンが異なる場合(まれな問題)は、カスタム関数で補正する必要があります。
  • Google スプレッドシートの期間の値も 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;
}

高度な

Apps Script サービスの使用

カスタム関数は、特定の 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 サービスを使用できます。

共有

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

  • [拡張機能] > [Apps Script] をクリックしてスクリプト エディタを開き、元のスプレッドシートからスクリプト テキストをコピーして、別のスプレッドシートのスクリプト エディタに貼り付けます。
  • [File > Make a copy] をクリックして、カスタム関数を含むスプレッドシートのコピーを作成します。スプレッドシートをコピーすると、スプレッドシートに添付されているスクリプトもコピーされます。スプレッドシートへのアクセス権を持つすべてのユーザーがスクリプトをコピーできます。(閲覧権限のみを持つ共同編集者は、元のスプレッドシートでスクリプト エディタを開くことができません。ただし、コピーを作成すると、作成者はコピーのオーナーになり、スクリプトを表示できます)。
  • スクリプトを 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;
}

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