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

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

Google スプレッドシートには、組み込み関数 AVERAGE、 SUM、 VLOOKUP を選択します。そうでない場合 Google Apps Script を使用してカスタム関数を記述し、 — メートルをマイルに変換する、フェッチするライブコンテンツを作成し、それらを使用して組み込み関数と同じように使用できます。

スタートガイド

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

次の DOUBLE という名前のシンプルなカスタム関数では、 2 倍に減算します。

/**
 * 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 スプレッドシートで開きます。
メニュー項目 [拡張機能] > を選択します。 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()。
スプレッドシートの関数名は、大文字と小文字は区別されません。通常は大文字にします

引数

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

1 つのセルを引数として参照して関数を呼び出した場合（=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 スプレッドシートのデータはさまざまな形式の責任を負います。これらの値をカスタム関数で使用すると、アプリスクリプトはそれらを JavaScript で適切なデータ型を使用する必要があります。混乱を招きやすい分野は次のとおりです。

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

予測入力

Google スプレッドシートでは、スプレッドシートと同様に、カスタム関数の予測入力がサポートされている組み込み関数を使用できます。皆さんがセルに関数名を入力すると、組み込みの関数とカスタムの関数のリストが表示されます。自動的に抽出されます。

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

/**
 * 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 サービス。

カスタム関数は、最初はスプレッドシートから抽出できます。つまり、Terraform で記述したカスタム関数は、他のスプレッドシートで使用できないスプレッドシートを使用するには、次のメソッドを使用できます。

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

で確認できます。 <ph type="x-smartling-placeholder">

最適化

Google スプレッドシートでは、スプレッドシートでカスタム関数が使用されるたびに別個に呼び出します。スプレッドシートに多数のファイル（何百、何千ものカスタム関数呼び出しを使用するため、このプロセスは遅くなります。

そのため、大規模なデータセットでカスタム関数を複数回使用範囲を指定したい場合は、関数を変更して、 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;
}

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

同様に、以下のカスタム関数は、で、2 次元配列を使用して 2 列の結果を 1 回の関数呼び出しで済みます各セルに独自の関数呼び出しが必要な場合は、処理にかなりの時間がかかります。これは、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;
}

これらの手法は、Cloud Functions を使用するほぼすべてのカスタム関数に繰り返しますが、実装の詳細は、関数の動作によって異なります。