photo credit: ATOMIC Hot Links 1980 NHRA Winternationals via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
スプレッドシートには数百を超える関数がありますが、自分の仕事や職場など独自の計算や処理をしたいときありますよね。
そんなときは、自作のオリジナル関数を作っておくと便利です。
ちょっとだけGoogle Apps Scriptでスクリプトを組む必要があるのですが、とっても簡単です。
とっても簡単なので、初心者がGoogle Apps Scriptの練習をするには、うってつけのお題ですよ。
今回は、例として消費税の税込計算のスプレッドシートのカスタム関数を独自に作成していきたいと思います。
では、よろしくお願いいたします！
スプレッドシートでどのような関数を作れるか
Googleスプレッドシートでは300を超える様々な関数を使うことができます。そしてそのほとんどはエクセルと共通です。
そんなスプレッドシート関数ですが、Google Apps Scriptを使って独自のオリジナル関数を作ることができます。
例えば
- 税込を返す関数
- 西暦を和暦に変換する関数
- 日付が営業日かどうかを返す関数
- 郵便番号から住所を返す関数
- URLから短縮URLを返す関数
などなど、汎用的に使える関数もたくさんあります。個人や職場で独自に使いたい関数もきっとたくさんあるものと思います。
アイデア次第で無限に広がりますね。
スプレッドシートで使えるGASで作るオリジナル関数を、カスタム関数ともいいます。
Google Apps Scriptで税込計算をする独自関数を作る
独自のスプレッドシートの関数を作るにはGoogle Apps Script（通称GAS）を使います。
Google Apps Scriptはエクセルで言うVBAのようなもので、Googleのサービスを操作するのが得意なJavaScriptベースのスクリプト環境です。
詳しくはこちらの記事をご覧下さいね。
では、このGASを使って、消費税の税込計算をする関数を作ってみましょう！
スクリプトエディタを開く
GASはスクリプトエディタという画面で書きます。
まず、スプレッドシートのメニューから「ツール」→「スクリプトエディタ」を選択しましょう。
スクリプトエディタが開きます。
プロジェクト名が「無題のプロジェクト」になっていますので変更しましょう。「無題のプロジェクト」をクリックすると、「プロジェクト名の編集」とうウィンドウが開きますので、好みのプロジェクト名を入力して「OK」として下さい。
これで準備は完了です。
コンテナバインドスクリプト
ちなみに、このような手順で作成するスクリプトをコンテナバインドスクリプトといいます。
コンテナバインドスクリプトは、スプレッドシートに紐づく形で作成するスクリプトです。
GASでは、スプレッドシートのほかに、ドキュメント、フォーム、スライドについてコンテナバインドスクリプトを作成することができます。
コンテナバインドスクリプトで作成すると
- バインドしているスプレッドシートやシートをgetActive～メソッドで取得できる
- スプレッドシートのカスタム関数を作れる
- 図形にスクリプトを割り当てられる
- Uiオブジェクトを使ったUIの作成ができる
などのメリットがあります。
カスタム関数のスクリプトを書く
次にスクリプトエディタにスクリプトを書きます。
エディタにはデフォルトで
function myFunction() {
}
とスクリプトが書かれていますが、それは全部削除しちゃってOKです。その代わりに、以下スクリプトを書いてみて下さい。
/* 税込金額を返す関数ZEIKOMI */
function ZEIKOMI(price){
const taxRate = 0.1; //税率
return price * (1 + taxRate);
}
書いたら、忘れずに保存をしましょう。ディスク型のアイコンをクリック、またはショートカットキー Ctrl + S です。
これで、もう税込金額を返す自作関数は完成しました。
税込金額を返す関数
簡単に、税込金額を返す関数ZEIKOMIのスクリプトについて説明しますね。
関数の書き方…function
まずGASの場合、関数はfunctionを使って以下のように書きます。
//処理
}
と書きます。波括弧の間が、その関数が呼び出されたときに実行される処理となります。
引数は、その関数が呼び出されたときに外部から渡される値です。今回のスクリプトの場合は、numという変数に格納されます。
税込金額を返す関数を作りたいので、この引数の値に1.1を掛け算して返せばOKということになります。
定数の宣言…const
変数や定数を使うことで、値に名前をつけて取り扱うことができます。
別の値を割り当てる必要がない場合は、定数を使います。
定数を宣言するにはキーワードconstを使って以下のように記述します。
今回の例でいうと、以下の一文ですね。
const taxRate = 0.1;
なお、GASの場合は、定数の宣言に限らず、全ての処理について最後にセミコロン（;）を入れる必要があります。
コメントの書き方
GASの場合、コメントの書き方は以下の二通りです。
まず、スラッシュとアスタリスク～アスタリスクとスラッシュでコメント囲むパターン。こちらはコメント内容が複数行でもコメントとして認識されます。
もう一つが、二つのスラッシュ以降にコメントを書くパターンです。この場合は、その行だけがコメントとして認識されます。
コメントは自由に書くことができますので、関数や変数の説明、スクリプトの補足説明などについて小まめに書いておくと後々とても助かります。
returnで値を返す
値を返す場合はreturnを使って
とします。今回の例では以下の部分です。
return price * (1 + taxRate);
引数priceに（1+税率）を乗算した値をリターンするということになります。
スプレッドシートで自作関数を使う
では、実際にスプレッドシートでこの関数を使ってみましょう。
例えば、A列に適当な値を入力して、B列に数式を入力します。
スクリプトエディタにで関数「ZEIKOMI」を使いましたので、他のスプレッドシート関数と同じように、イコールからつづけて
例えば、B1セルであれば
とします。色々な値を入力してみました。
A1～A3は数値ですから、B1～B3に税込の値がきちんと戻っていますね。
A4は試しに、日付を入力してみましたが、なんだか不思議な数値が出てしまいました。
A5は文字列ですが、エラーになりましたね。
ドキュメンテーションコメント
さて、もうひとつテクニックがあるのでお伝えしておきます。
ドキュメンテーションコメントです。
「/**」ではじまり「*/」で終わるコメントで、関数の説明をほどこすもので、詳しくは以下記事をご覧ください。
ここで、「@customfunction」のタグを追加することで、スプレッドシート側で「いいこと」が起きます。
試しに以下のように入力して保存しましょう。
/**
* 税込金額を返す関数ZEIKOMI
*
* @param {Number} 金額
* @return {Number} 税込金額
* @customfunction
*/
function ZEIKOMI(price){
const taxRate = 0.1; //税率
return price * (1 + taxRate);
}
スプレッドシートでZEIKOMI関数を入力しようとすると、関数の入力中に補完の候補に上がるようになります。
さらに、 Tab キーを入力すると、以下のようにドキュメンテーションコメントに入力した内容の一部が表示されるのです。
かっこいいですよね！
そして便利なので、ぜひドキュメンテーションコメントもご活用ください。
まとめ
以上、Google Apps Scriptを使って、スプレッドシートのカスタム関数を作る方法についてお伝えしました。
もし、消費税の計算を使っているスプレッドシートがあれば、このスクリプトを仕込んで関数ZEIKOMIを使うことで、もし消費税率が上がっても、以下の数値を変更するだけで対応が可能になりますよね。
const taxRate = 0.1; //税率
（もう上がってほしくないですが…）
次回ですが、もう一つ便利な自作関数、西暦から和暦変換をするスプレッドシート関数をご紹介します。
どうぞお楽しみに！
