初心者でも簡単!Google Apps Scriptでスプレッドシートの自作関数を作る方法

★気に入ったらシェアをお願いします!


customize

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を返す関数

などなど、汎用的に使える関数もたくさんあります。個人や職場で独自に使いたい関数もきっとたくさんあるものと思います。

アイデア次第で無限に広がりますね。

Google Apps Scriptで税込計算をする独自関数を作る

独自のスプレッドシートの関数を作るにはGoogle Apps Script(通称GAS)を使います。

Google Apps Scriptはエクセルで言うVBAのようなもので、Googleのサービスを操作するのが得意なJavaScriptベースのスクリプト環境です。

詳しくはこちらの記事をご覧下さいね。

超初心者へGoogleAppsScriptを始めるメリットをこれでもかと説明します
Gmailやスプレッドシート、カレンダーなど数々のGoogleサービスを操作することができるGoogle Apps Script…仕事効率化において、学び活用するメリットをこれでもかとお伝えします。

では、このGASを使って、消費税の税込計算をする関数を作ってみましょう!

スクリプトエディタを開く

GASはスクリプトエディタという画面で書きます。

まず、スプレッドシートのメニューから「ツール」→「スクリプトエディタ」を選択しましょう。

スプレッドシートからスクリプトエディタを開く

スクリプトエディタが開きます。

プロジェクト名が「無題のプロジェクト」になっていますので変更しましょう。「無題のプロジェクト」をクリックすると、「プロジェクト名の編集」とうウィンドウが開きますので、好みのプロジェクト名を入力して「OK」として下さい。

スクリプトエディタでプロジェクト名を変更する

これで準備は完了です。

自作関数のスクリプトを書く

次にスクリプトエディタにスクリプトを書きます。

エディタにはデフォルトで

とスクリプトが書かれていますが、それは全部削除しちゃってOKです。その代わりに、以下スクリプトを書いてみて下さい。

書いたら、忘れずに保存をしましょう。ディスク型のアイコンをクリック、またはショートカットキー Ctrl + S です。

Google Apps Scriptのスクリプトを保存する

これで、もう税込価格を返す自作関数は完成しました。

税込価格を返す関数

簡単に、税込み価格を返す関数ZEIKOMIのスクリプトについて説明しますね。

関数の書き方…function

まずGASの場合、関数はfunctionを使って以下のように書きます。

function ファンクション名(引数){
 //処理
}

と書きます。波括弧の間が、その関数が呼び出されたときに実行される処理となります。

引数は、その関数が呼び出されたときに外部から渡される値です。今回のスクリプトの場合は、numという変数に格納されます。税込価格を返す関数を作りたいので、この引数の値に1.08を掛け算して返せばOKということになります。

変数の宣言…var

GASでは使う変数は全て宣言が必要です。変数を宣言する場合はvarを使って

var 変数名;

です。また、宣言と同時に値を代入したい場合は

var 変数名 = 値;

と書く事もできます。

今回の例でいうと

は、関数ZEIKOMIの外で宣言をしているので、「コード.gs」で書く全ての関数で共通の変数としてSALES_TAX_RATEを使うことができます。これを、特定の関数内に書いた場合は、その変数はその関数内でのみ利用できます。

なお、GASの場合は、変数宣言に限らず、全ての処理について最後にセミコロン(;)を入れる必要があります。これ、けっこう入れ忘れてエラーが連発するので注意です。

コメントの書き方

GASの場合、コメントの書き方は以下の二通りです。

まず、スラッシュとアスタリスク~アスタリスクとスラッシュでコメント囲むパターン。こちらはコメント内容が複数行でもコメントとして認識されます。

/* コメント内容 */

もう一つが、二つのスラッシュ以降にコメントを書くパターンです。この場合は、その行だけがコメントとして認識されます。

// コメント内容

コメントは自由に書くことができますので、関数や変数の説明、スクリプトの補足説明などについて小まめに書いておくと後々とても助かります。

returnで値を返す

値を返す場合はreturnを使って

return 値;

とします。今回の例では

ですから、引数numに(1+税率)を乗算した値をリターンするということになります。

スプレッドシートで自作関数を使う

では、実際にスプレッドシートでこの関数を使ってみましょう。

例えば、A列に適当な値を入力して、B列に数式を入力します。

スクリプトエディタにで関数「ZEIKOMI」を使いましたので、他のスプレッドシート関数と同じように、イコールからつづけて
例えば、B1セルであれば

=ZEIKOMI(A1)

とします。色々な値を入力してみました。

スプレッドシートで自作関数を使用する

A1~A3は数値ですから、B1~B3に税込の値がきちんと戻っていますね。

A4は試しに、日付を入力してみましたが、なんだか不思議な数値が出てしまいました。A4は文字列ですが、エラーになりましたね。

まとめ

以上、Google Apps Scriptを使って、自作のスプレッドシート関数を作る方法についてお伝えしました。

もし、消費税の計算を使っているスプレッドシートがあれば、このスクリプトを仕込んで関数ZEIKOMIを使うことで、もし消費税率が上がっても

などと変更するだけで、対応が可能になりますよね。

次回ですが、もう一つ便利な自作関数、西暦から和暦変換をするスプレッドシート関数をご紹介します。

Google Apps Scriptで西暦を和暦変換するスプレッドシート関数を作る
Google Apps Scriptを使ってスプレッドシートの自作関数を作る方法です。今回は西暦から和暦を求める関数の作成方法。if~else if文、比較演算子、数値と文字列の演算なども含めてお伝えします。

どうぞお楽しみに!

連載目次:Google Apps Scriptで自作スプレッドシート関数

本シリーズではGoogle Apps Script初心者向け、自作のスプレッドシート関数を作っていきます。一からとっても丁寧な解説を心がけておりますので、どうぞチャレンジしてみて下さい。
  1. 初心者でも簡単!Google Apps Scriptでスプレッドシートの自作関数を作る方法
  2. Google Apps Scriptで西暦を和暦変換するスプレッドシート関数を作る
  3. Google Apps ScriptでREST APIを使って郵便番号住所変換スプレッドシート関数を作る