Google スプレッドシートには、数百もの
組み込み関数が、
AVERAGE、
SUM、
VLOOKUPのように用意されています。このような関数だけでは足りない場合は、Apps Script を使用してカスタム関数を作成し、組み込み関数と同様に Google スプレッドシート内で使用できます。
カスタム関数の例については、次のチュートリアルをご覧ください。
- 割引商品のセール価格を計算する（クイックスタート）
- 段階的な料金割引を計算する
- 運転距離を計算してメートルをマイルに変換する
- 複数のシートのデータを集計する
- ADK AI エージェントと Gemini モデルを使用してステートメントのファクト チェックを行う
スタートガイド
カスタム関数は、標準の 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 Workspace アドオンとして、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]])として解釈されます。前述のDOUBLEで説明した `DOUBLE` のサンプルコードは、配列を入力として受け入れるように 変更する必要があります。
カスタム関数の引数は 決定論的である必要があります。つまり、
NOW()やRAND()など、計算するたびに異なる結果を返す組み込みのスプレッドシート関数は、カスタム関数の引数として使用できません。カスタム関数が、このような揮発性の組み込み関数に基づいて値を返そうとすると、Loading...が無期限に表示されます。再計算をトリガーするには、参照先のセルまたはセル範囲をカスタム関数の引数として直接渡す必要があります。そうしないと、関数を編集するか、参照先のセルの値を変更するまで、カスタム関数は再計算されません。カスタム関数で
getValueメソッドを使用する場合、参照先の範囲はカスタム関数の引数として直接渡されないことに注意してください。
戻り値
すべてのカスタム関数は、表示する値を返す必要があります。
- カスタム関数が値を返す場合、その値は関数が呼び出されたセルに表示されます。
- カスタム関数が値の 2 次元配列を返す場合、その値は、セルが空である限り、隣接するセルにオーバーフローします。これにより、配列が既存のセルコンテンツを上書きする場合は、カスタム関数がエラーをスローします。例については、 カスタム関数の最適化に関するセクションをご覧ください。
- カスタム関数は、値を返すセル以外のセルに影響を与えることはできません。 つまり、カスタム関数は、呼び出し元のセルとその隣接するセルのみを編集でき、任意のセルを編集することはできません。任意のセルを編集するには、 カスタム メニューを使用して関数を実行します。
- カスタム関数の呼び出しは 30 秒以内に戻る必要があります。そうでない場合、
セルに
#ERROR!と表示され、セルのメモにExceeded maximum execution time (line 0).と表示されます。
データ型
Google スプレッドシートでは、データの性質に応じて 異なる形式でデータが保存されます。これらの値がカスタム関数で使用される場合、 Apps Script はそれらを JavaScript の適切なデータ型として扱います。 混乱しがちな部分を次に示します。
- Google スプレッドシートの日時が Apps Script で Date オブジェクトになります。スプレッドシートとスクリプトで異なるタイムゾーンを使用している場合（まれな問題）、カスタム関数で補正する必要があります。
- Google スプレッドシートの期間の値も
Dateオブジェクトになりますが、 操作が複雑になることがあります。 - Google スプレッドシートのパーセント値が Apps Script で小数になります。たとえば、値が
10%のセルは、Apps Script では0.1になります。
予測入力
Google スプレッドシートでは、 組み込み関数と同様に、カスタム関数の予測入力がサポートされています。セルに関数名を入力すると、入力内容に一致する組み込み関数とカスタム関数のリストが表示されます。
カスタム関数は、スクリプトに
JSDoc @customfunction タグが含まれている場合（DOUBLE()
の例など）、このリストに表示されます。
/**
* 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 サービスを使用できます。
カスタム関数を共有する
カスタム関数は、作成された スプレッドシートにバインドされます。つまり、1 つのスプレッドシートで作成されたカスタム関数は、次のいずれかの方法を使用しない限り、他のスプレッドシートでは使用できません。
- [拡張機能] > [Apps Script] をクリックしてスクリプト エディタを開き、元のスプレッドシートからスクリプト テキストをコピーして、別のスプレッドシートのスクリプト エディタに貼り付けます。
- [ファイル > コピーを作成] をクリックして、カスタム関数を含むスプレッドシートのコピーを作成します。スプレッドシートがコピーされると、添付されているスクリプトもコピーされます。スプレッドシートにアクセスできるユーザーは誰でもスクリプトをコピーできます。（閲覧権限のみを持つ共同編集者は、元のスプレッドシートでスクリプト エディタを開くことはできません。ただし、コピーを作成すると、コピーのオーナーになり、スクリプトを表示できます）。
- スクリプトを Google スプレッドシート エディタ アドオンとして公開します。
コンテナ バインド スクリプトはすべて、コンテナと同じアクセスリストを共有します。 つまり、スプレッドシートの編集権限を持つユーザーは、添付されている 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;
}
この方法では、JavaScript の
map
メソッドを使用して、Array オブジェクトの 2 次元配列の
セルから各行を取得し、各行に対して 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;
}
これらの手法は、スプレッドシート全体で繰り返し使用されるほぼすべてのカスタム関数に適用できますが、実装の詳細は関数の動作によって異なります。