カスタム関数を作っていていろいろとハマったので、その説明文書(Custom Functions in Google Sheets)を訳してみました。リンク先や訳文は、日本語環境に合わせて若干変更してある箇所があります。
Google シートにおけるカスタム関数
Google シートでは AVERAGE, SUM, VLOOKUP といった、多くのビルトイン関数を提供しています。これらの関数だけでは機能的に不足する場合、Google Apps Script を使ってカスタム関数を書くこともできます。具体例としては、メートルをマイルに変換したり、インターネットから現時点のコンテンツを取って来るといったことが挙げられます。Google シートの中で作成したカスタム関数は、ビルトイン関数と同じように利用できます。
開始方法
カスタム関数は標準の JavaScript を使って作成します。まだ JavaScript を使ったことがない場合、Codeacademy で初心者向けの魅力的な学習コースをご提供しています。（このコースは Google が作成したものではなく、Google とは無関係ですのでご注意ください）。
まずサンプルとして、以下のようなシンプルなカスタム関数を考えます。これは DOUBLE という名前で、入力値を２倍にするものです。
function DOUBLE(input) {
return input * 2;
}
JavaScript の書き方がわからないんだけど学習しているヒマもないという向きには、アドオンストアをチェックして、自分が必要とするカスタム関数を誰かすでに作ってくれたりしていないかどうかを調べてみてください。
カスタム関数を作成する
カスタム関数を書く手順は以下の通りです。
- Google シートでスプレッドシートを作成(create)またはオープンします。
- メニューから[ツール]＞[スクリプトエディタ]を選択します。ようこそ画面が表示された場合、左側の[空白のプロジェクト]をクリックして新しいプロジェクトを開始してください。
- スクリプトエディタ内にあるコードをすべて削除します。その後、前述の DOUBLE 関数をコピーしてスクリプトエディタに貼り付けます。
- メニューから[ファイル]＞[保存]を選びます。スクリプトプロジェクトに名前をつけて[OK]をクリックします。
- これでカスタム関数を使えるようになりました。
アドオンストアからカスタム関数を取得する
アドオンストアではGoogle シートのアドオンとしてカスタム関数がいくつか提供されています。以下の要領でこれらのアドオンを使ったり探したりできます。
- Google シートでスプレッドシートを作成(create)またはオープンします。
- メニューから[アドオン]＞[アドオンを取得]を選択します。
- シート用のアドオンストアが開きます。右上隅の検索ボックスをクリックし、"custom function" とタイプして Enter を押します。
- 使ってみたいカスタム関数アドオンが見つかったら[無料]をクリックしてインストールします。
- アドオンの認可を要求するダイアログボックスが表示される場合があります。その場合、注意書きをよく読んでから、（アカウントを選択して）[許可]をクリックします。これでアドオンがインストールされます。
- これでアドオンがこのスプレッドシート内で有効になりました。別のスプレッドシート内でそのアドオンを使いたい場合、別のスプレッドシートを開いてメニューから[アドオン]＞[アドオン名]を選択します。
カスタム関数を使う
いったんカスタム関数を書いたりアドオンストアからインストールした場合、以下の手順でビルトイン関数のように手軽に使えるようになります。
- その関数を使いたい場所のセルをクリックします。
- たとえば =DOUBLE(A1) のように、等号(=)に続けて関数名をタイプし、入力値を指定して [Enter] を押します。
- そのセルが Loading ... という表示になり、しばらくして結果が返されます。
カスタム関数のガイドライン
自作のカスタム関数を書く前に、いくつか知っておいていただきたいガイドラインがあります。
ネーミング
JavaScript 関数のネーミングに関して、標準の命名規則以外に、以下の点にも注意してください。
- カスタム関数の名前は、SUM() のようなビルトイン関数名と明確に区別できる必要があります。
- カスタム関数名はアンダースコア(_)で終わってはなりません。ちなみにこれは、Apps Script ではプライベート関数を意味します。
- カスタム関数名は var myFuntion = new Function() 形式ではなく、function myFunction() 形式で宣言されなければなりません。
- スプレッドシート関数の名前は伝統的に大文字を使いますが、カスタム関数では大文字小文字どちらを使っても構いません。
引数
ビルトイン関数と同様に、カスタム関数も入力値として引数を取ることができます。
- 引数として（=DOUBLE(A1)のように）単一のセルを指定した場合、引数はそのセルの値となります。
引数として（=DOUBLE(A1:B10)のように）セル範囲を指定した場合、引数はそれらのセルの値の２次元配列となります。例として以下のスクリーンショットを見てください。=DOUBLE(A1:B2) の引数は Apps Script によってdouble ([[1,3],[2,4]]) と解釈されます。ちなみに前述の DOUBLE のサンプルコードは、入力として配列を受け付けるように書き換える必要がありますのでご注意ください。
カスタム関数の引数は決定的でなければなりません。つまり、計算のたびに異なった値を返す NOW() や RAND() のようなビルトインのスプレッドシート関数は、カスタム関数の引数としては指定できないということです。もしカスタム関数がこのようなビルトイン関数に基づく揮発性の値を返そうとすると、Loading ... が永久に表示され続けることになります。
戻り値
カスタム関数は、以下のように表示可能な値を返さなければなりません。
- カスタム関数が単一の値を返す場合、その関数が呼ばれたセルにはその値が表示されます。
- カスタム関数が２次元配列の値を返す場合、そこに隣接したセルの値が連続して空である間、それらのセルに値が溢れて流れ込みます。溢れた値が既存のセルの中身を上書きすることになる場合、カスタム関数は代わりにエラーを投げます。具体例としては、カスタム関数の最適化の章をごらんください。
- 値を返さないカスタム関数は、セルに影響を与えることができません。つまり、カスタム関数が編集できるのは、その関数を呼出したセル、およびそれに隣接したセル（＋それに続くセル）のみであり、カスタム関数から任意のセルの編集をすることはできないということです。任意のセルの編集をしたい場合、カスタムメニューを使ってカスタム関数を実行します。
- カスタム関数は 30 秒以内にリターンしなければなりません。もしこれを超えた場合、そのセルには Internal error executing the custom function が表示されます。
データ型
Google シートは、さまざまなデータをそれらの性質に基づく異なったフォーマットで格納します。これらの値がカスタム関数内で使われる場合、Apps Script はこれを JavaScript における適切なデータ型として取り扱います。ここはもっとも混乱しやすい部分です。
- シートにおける時刻と日付は、Apps Script 内部では Date オブジェクトになります。（レアケースではありますが）もしスプレッドシートとスクリプトで異なったタイムゾーンを使っている場合、カスタム関数内でこれを補正してやる必要があります。
- シート内で期間を表す値も Date オブジェクトになりますが、これへの対応はかなり複雑になる可能性があります。
- シート内で百分率を表す値は、Apps Script では小数になります。たとえば 10% の値を持つセルは、Apps Script では 0.1 になります。
オートコンプリート
Google シートではビルトイン関数と同様に、カスタム関数でもオートコンプリートをサポートしています。セルに関数名の一部をタイプすると、それにマッチするビルトイン関数とカスタム関数の一覧が表示されます。
以下の DOUBLE() の例のように、スクリプトに JsDoc の ＠customfunction タグが含まれていれば、候補一覧の中にカスタム関数が表示されます。
/**
* 入力値に２をかける
*
* @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 における他のほとんどのタイプとは異なり、カスタム関数は個人データへのアクセスの認可を要求することはありません。つまり、カスタム関数は個人データへのアクセス権を持たないサービスしか呼び出せないということです。特に以下のものが挙げられます。
| サポートされる サービス |
備考 |
|---|---|
| Cache | 動きますが、カスタム関数で特に有用ということはありません。 |
| HTML | HTML を生成できますが、それを表示できません（よって、ほとんど意味はありません）。 |
| JDBC | |
| Language | |
| Lock | 動きますが、カスタム関数で特に有用ということはありません。 |
| Maps | 距離を計算できますが、マップを表示することはできません。 |
| Properties | |
| Spreadsheet | リードオンリーです（ほとんどの get*() を使えますが set*() は使えません）。 他のスプレッドシートを開けません（SpreadsheetApp.openById() や SpreadsheetApp.openByUrl()） |
| URL Fetch | |
| Utilities | |
| XML |
あなたの関数が You do not have permission to call X service. というエラーメッセージを投げる場合、そのサービスがユーザーの認可を必要としているため、カスタム関数内では使えないことを表しています。
前述の一覧で提示されたもの以外のサービスを利用する場合、カスタム関数を記述する代わりにカスタムメニューを使って Apps Script 関数を実行してください。メニューからトリガーされる関数は、必要であればユーザに対して認可を問合わせるため、すべての Apps Script のサービスを利用できます。
共有
カスタム関数は、それらが作成されたスプレッドシートに関連付けられた形(bound)で開始されます。つまり、あるスプレッドシートで書かれたカスタム関数は、以下のいずれかの方法を取らない限り、他のスプレッドシートでは使えないということです。
- [ツール]＞[スクリプトエディタ]をクリックしてスクリプトエディタを開き、元のスプレッドシートからスクリプトの本体をコピーして、別のスプレッドシートのスクリプトエディタに貼り付けます。
- カスタム関数を含むスプレッドシートで[ファイル]＞[コピーを作成]をクリックしてコピーを作成します。スプレッドシートがコピーされると、それに関連付けられたスクリプトもコピーされます。スプレッドシートにアクセス権のあるユーザーは誰でもスクリプトのコピーを作成できます。（参照アクセス権しか持たない共同作業者は、元のスプレッドシートでスクリプトエディタを開けません。しかし、いったんコピーを作成すれば、そのコピーの所有者になるのでスクリプトが見えるようになります。）
- Google シートのアドオンとしてスクリプトを公開する。
★ コンテナと結合したスクリプトは、いずれもそのコンテナと同じアクセスリストを持ちます。つまり、そのスプレッドシートの編集権を持つ人なら誰でも、それに付随した Apps Script のコードを編集できるということです。
最適化
スプレッドシート内でカスタム関数が使われるたびに、Google シートは Apps Script サーバに対して毎回呼び出しを行います。もしあなたのスプレッドシートに数十の（または数百や数千の！）カスタム関数がある場合、処理がかなり遅くなる可能性があります。
このため、もしあなたが広範囲のデータに対して複数回のカスタム関数を使おうと思っているなら、入力として２次元配列を取り、元のセルとその隣接セルに対して二次元の値を返すような形にカスタム関数を修正することを検討してください。
たとえば、前述の DOUBLE() についても、以下の通り単一のセルでもセル範囲でも取ることができるように書き換えることができます。
/**
* 入力値に２をかける
*
* @param {number} input The value or range of cells to multiply.
* @return The input multiplied by 2.
* @customfunction
*/
function DOUBLE(input) {
if (input.map) { // 入力が配列かどうかを判定し、
return input.map(DOUBLE); // 配列の場合は配列に対して再帰的に呼び出す。
} else {
return input * 2;
}
}
この例では JavaScript の Array オブジェクトの map メソッドを使って、セルの２次元配列にあるそれぞれの値について再帰的に DOUBLE を呼び出すようなアプローチを取っています。この方法により、以下のスクリーンショットにあるように、DOUBLE を一回呼び出すだけで多数のセルの計算を一気に行なうことができます。（map の呼び出し以外にも、ネストされた if ステートメントについても同様のことが可能かもしれません）。
以下の例も同様に、インターネットから現時点のコンテンツを効率的に取得して、二次元配列を使って１回の関数呼び出しだけで２カラムを持つ結果の一覧を表示しています。それぞれのセルが毎回関数呼び出しをするようになっている場合、Apps Script サーバは毎回 XML フィードをダウンロードしてパースしなければならなくなり、その動作はかなり長時間に及ぶことになるでしょう。
/**
* G Suite 開発者ブログの最初の投稿ページのタイトルと日付を表示する
*
* @return Two columns of data representing posts on the G Suite
* 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;
}
これらのテクニックは、１個のスプレッドシートを通して繰り返し使われるほとんどすべてのカスタム関数に対して適用可能ですが、その実装の詳細は、その関数の振る舞いにより異なります。
Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
最終更新日: 5月 18, 2017