はじめに
今回はGoogleAppsScriptを使ってみよう(前編)で告知をしていたとおりGoogleスプレッドシートの「カスタム関数」と「スプレッドシートとカレンダーの連携」の二つの内容でより実践的なGoogleAppsScriptの使い方を紹介していこうと思いましたが、「カスタム関数」の内容でだいぶ膨らんでしまったため今回は内容を「カスタム関数」に絞って解説していきます。
後日おまけ編として「スプレッドシートとカレンダーの連携」の記事を書きますのでそちらに興味のある方はぜひフォローをお願いしますmm
前回と同様に、ちょっとした手作業を少しでも減らしたい人やプログラミングに興味があるけどちょっとやるにはハードルが高いなと感じている方を対象とした内容となっていますので、肩の力を抜いて読んでいただければ幸いです。
事前のGoogleAppsScriptを使えるまでの準備は前回の内容と同様ですので準備がまだの方はそちらを参照して準備の方を行ってください。
でははじめましょう。
カスタム関数
スプレッドシートにはあらかじめ使用できる関数が用意されています。
四則演算や合計、平均、数えたり抜き出したりする際にはとても便利です。
しかし下の画像のE2のセルに表示された文章を扱う場合に入力が複雑になり見通しが悪くなる場合があります。
式で表すと下の画像のような感じでしょうか
かなり簡単な文章を表示したつもりですが、すでに式が何を表したいのかがわかりません。
ここでカスタム関数を使ってみます。
まずはスクリプトエディタを立ち上げmyFunctionの下にgoukeiという名前の関数を作成します。
function myFunction()
{
}
function goukei(goods, price, num)
{
var total_price = price * num;
return goods + 'は' + num + '個' + 'で' + total_price + '円';
}
function goukei(goods, price, num)と記述している行の説明を行います。
function 任意の文字列() と「()」の中が空の形は前編でも見かけた形です。
今回の例ではgoods, price, numと3つの名前が出てきました。
GoogleAppsScriptではこの「()」の中に名前を記述することで関数の中で使用できる変数を渡すことができます。
そしてこの変数のことを「引数」と呼んでいます。
関数の中での変数の宣言や取り扱いについては前回説明したとおりですが、この引数はどこからくるのでしょうか。
ひとまずコードを保存してスプレッドシートに戻りましょう。
先ほど「Aは２個で160円」と表示されていたセルを選択します。
セルで関数を使用する場合には「=関数名(セル)」の形でした。
GoogleAppsScriptで定義した関数もこの形式で使用することができます。
さらにgoukei関数では3つの引数を渡すことができる定義にしていました。
ここではそれぞれの変数に該当するセルを指定します。
goodsは品物の名前として「B2」を。
priceは品物の値段として「C2」を。
numは購入する数として「D2」を指定します。
結果を確認してみましょう。
うまく表示されたでしょうか。
スクリプトエディタに戻り先ほどのコードをみてみましょう。
function myFunction()
{
}
function goukei(goods, price, num)
{
var total_price = price * num;
return goods + 'は' + num + '個' + 'で' + total_price + '円';
}
引数は変数を渡すと説明しました。
スプレッドシートで指定したのは「B2」「C2」「D2」というセルの場所を表す名前でした。
しかし表示された文字列には「B2」「C2」「D2」という内容は全く現れませんでした。
これはカスタム関数の引数にセルを指定した場合、その中身を引数とするというルールが存在するためです。
このためセルに指定した値が引数として渡されスクリプトで処理されたのです。
ここまでがGoogleAppsScriptとカスタム関数の仕組みのお話になります。
スクリプトではまた新しい内容が出てきていますのでそちらについても説明を行なっていきます。
スクリプトの解説
function goukei(goods, price, num)
{
var total_price = price * num;
return goods + 'は' + num + '個' + 'で' + total_price + '円';
}
今回初めて見るものは「*」と「+」と「return」です。
プログラムやスクリプトを少し学んだことのある方はすでに理解した内容かもしれませんので読み飛ばしていただいても、復習のつもりで読み進めていただいても大丈夫です。
演算子
突然難しい言葉が出てきましたが身構える必要はありません。
先ほどコードの中に出てきた「＊」と「+」は演算子と呼ばれています。
例ではpriceとnumの間に「＊」が記述されています。取り扱うプログラミング言語やスクリプトにより演算子の振る舞い方や意味が異なる場合がありますが、数値と数値の間に記述された「＊」は乗算を表す場合が多いです。
今回の例においても難しい使い方はしていないので乗算を表しています。
変数の宣言と代入については前回説明したとおりですので難しいところはないと思います。
var total_price = price * num;
はtotal_priceという名前で変数を宣言してpriceとnumを掛け算した結果をtotal_priceに代入するというスクリプトになります。
次に「＋」です。
GoogleAppsScriptで「＋」演算子は数値と文字列に使用することができます。
数値同士の間に記述される場合には加算を示し、数値と文字列もしくは文字列と文字列の間に記述される場合には文字として連結されます。
//引数として渡されたgoodsの中身は'A'、numは2、total_priceは80*2の結果の160
return goods + 'は' + num + '個' + 'で' + total_price + '円';
goodsは'A'、numは2、total_priceは160が入っています。
goodsは文字(文字列)、numは数値、total_priceは数値となっており、先ほど説明したルールから文字列と数値で構成されるものは文字列として連結されます。
わかりやすいように中身を展開します。
//引数として渡されたgoodsの中身は'A'、numは2、total_priceは80*2の結果の160
return 'A' + 'は' + '2' + '個' + 'で' + '160' + '円';
という内容になり、
return 'Aは2個で160円';
という文字列に連結されます。
「＊」や「+」はその左右に記述されている内容により振る舞い方を変えはしますが、左右に記述されているものの内容を別のものに変えるという性質があります。一般的に単一もしくは３文字程度の記号の組み合わせで表現されるこれらのものを総称して演算子と呼びます。
return文
return 'Aは2個で160円';
この「return」の記述で始まる行を「return文」と呼びます。
スクリプトが上の行から下の行へと実行されていき、この「return文」へたどり着いた時に関数は呼び出された場所へ戻ります。
また今回の例では「return」に続き、「Aは2個で160円」を記述しているので、呼び出し元に戻る時に「Aは2個で160円」を一緒に返します。
プログラムやスクリプトではこの返す値のことを「返り値」や「戻り値」と呼びます。
戻り値や返り値を指定せずに呼び出すことも可能です。
その場合には何も指定しないため「undefined」を返します。
スクリプトを書き換えます。
function myFunction() {
var return_value = goukei('A', 80, 2); //戻り値を変数に代入する
Logger.log(return_value); //変数を表示
Logger.log(goukei('A', 80, 2)); //変数で受け取らずそのまま戻り値を表示
Logger.log(return_only()); //戻り値を省略した例
Logger.log(no_return()); //return文を書かなかった例
}
function goukei(goods, price, num)
{
return goods + 'は' + num + '個' + 'で' + price * num + '円';
}
function return_only()
{
return;
}
function no_return()
{
}
保存してスクリプトエディタからmyFunctionを選択して実行してみましょう。
[xx-xx-xx xx:xx:xx:xxx xxx] Aは2個で160円
[xx-xx-xx xx:xx:xx:xxx xxx] Aは2個で160円
[xx-xx-xx xx:xx:xx:xxx xxx] undefined
[xx-xx-xx xx:xx:xx:xxx xxx] undefined
ログを見ると上記の内容で表示されています。
上から順番にスクリプトを見ていきましょう。
var return_value = goukei('A', 80, 2); //戻り値を変数に代入する
Logger.log(return_value); //変数を表示
スプレッドシートで呼び出していた内容に合わせて引数を指定してgoukei関数を呼び出しました。
goukei関数の最後の行で戻り値を指定してreturnを呼び出していたので戻り値をreturn_valueに代入しました。
そしてLogger.logで出力するという内容です。
このように戻り値は変数に代入することができ、Logger.logでわかるように再利用することも可能です。
次の例を見ましょう。
Logger.log(goukei('A', 80, 2)); //変数で受け取らずそのまま戻り値を表示
先ほどの例では戻り値を一旦変数に代入しましたが、再利用する必要がない場合にはこのように直接Logger.logへ渡すことも可能です。
次に進みましょう。
Logger.log(return_only()); //戻り値を省略した例
戻り値を指定せずにreturnを呼び出した場合です。
前編で変数の宣言のみ行なった例がありましたがその時と同様にundefinedになりました。
返すものを指定していないので当たり前といえば当たり前の結果ですね。
最後の例を見てみましょう。
Logger.log(no_return()); //return文を書かなかった例
「return文」を書かなかった場合にもundefinedが表示されました。
これは実際にスクリプトを書いているときにたまにあるのですがreturnを呼び出していないのに戻り値を受け取る形を取ってもエラーとはなりません。関数の内容をしっかり書いたつもりなのに、結果がなんかおかしい時はreturn文を忘れていることがあります。
頭の片隅に置いておきましょう。
カスタム関数のどこが便利か
ここまでカスタム関数、演算子、return文の説明を行いましたが、こんなに覚えることがあるなら用意されているスプレッドシートの機能でなんとかした方が楽！と思った方、多いのではないでしょうか(笑)
ということで少しだけスプレッドシートに情報を追加して、表示する内容を状況に応じて変更するようにしてみましょう。
- countryを追加
- goukei関数の引数の最後にcountryを指定する
- countryが日本の時のpriceの単位は円、アメリカの時のpriceの単位はドル
- goodsがAの時のnumの単位は個、Bの時のnumの単位はパック
この条件で表示しましょう。
セルで共通の式を使いまわそうと思った時にとても辛くなることが想像できそうじゃないですか？
例えば、countryの種類を欧州全てに対応したい。goodsの種類を増やした時に増やしたものによって単位を変えたい等、一つの式で表現した時にとても見通しが悪くなります。
GoogleAppsScriptであれば見通しよくそれなりに簡単に対応することができます。
早速スクリプトを書いてみましょう。
function myTestFunction() {
}
function goukei(goods, price, num, country)
{
var price_unit = getPriceUnitFromCountry(country); //国別で価格の単位を取得
var goods_unit = getGoodsUnitFromGoods(goods); //品物ごとに数える単位を取得
return goods + 'は' + num + goods_unit + 'で' + price * num + price_unit;
}
//国別で価格の単位を取得
function getPriceUnitFromCountry(country)
{
switch (country) {
case '日本':
return '円';
case 'アメリカ':
return 'ドル';
default:
return '未定義'
}
}
//品物ごとに数える単位を取得
function getGoodsUnitFromGoods(goods)
{
switch (goods) {
case 'A':
return '個';
case 'B':
return 'パック';
default:
return '未定義'
}
}
それではgoukei関数から見ていきましょう。
function goukei(goods, price, num, country)
{
var price_unit = getPriceUnitFromCountry(country); //国別で価格の単位を取得
var goods_unit = getGoodsUnitFromGoods(goods); //品物ごとに数える単位を取得
return goods + 'は' + num + goods_unit + 'で' + price * num + price_unit;
}
goukei関数には国別に価格を取得するためのgetPriceUnitFromCountry関数と品物ごとに数える単位を取得するためのgetGoodsUnitFromGoods関数を追加しました。
price_unitに国別の価格の単位を、goods_unitに品物ごとの数える単位を代入して、最後のreturn分のところで戻り値として返す文字列を組み立てています。
次にgetPriceUnitFromCountry関数の中身を見ていきます。
//国別で価格の単位を取得
function getPriceUnitFromCountry(country)
{
switch (country) {
case '日本':
return '円';
case 'アメリカ':
return 'ドル';
default:
return '未定義'
}
}
ここでも新しい処理があります。
switchで始まるコードがありますが、これはswitch文と呼びます。
switchに続く「()」で囲まれる値を条件として、case で指定する値と一致する場合にcaseに続く行を処理します。defaultを定義した場合にはcaseに一致する値がなかったとき実行されます。
ここではcountryと日本が一致する場合には円を、アメリカが一致する場合にはドルを、一致しない場合には未定義を戻り値として返すように処理が記述されています。
いかがでしょうか。仮に国をいくつか追加しても価格の単位を追加する対応が比較的容易にできそうな気がしませんか？
次にgetGoodsUnitFromGoods関数を見ていきます。
getPriceUnitFromCountry関数と同様にswitch文を使用して品物に対応する数え方の単位を定義しています。
//品物ごとに数える単位を取得
function getGoodsUnitFromGoods(goods)
{
switch (goods) {
case 'A':
return '個';
case 'B':
return 'パック';
default:
return '未定義'
}
}
ここではgoodsを条件として、'A'と一致する場合には'個'を、'B'と一致する場合には'パック'を、一致するものがない場合には未定義を戻り値として返すように処理を記述しています。
それではスプレッドシートに戻り、countryを追加したgoukei関数の呼び出しを行ってみましょう。
画像のようにE列をcountry、日本、アメリカと編集します。
そしてF2に「=goukei(B2,C2,D2,E2)」を、F3に「=goukei(B3,C3,D3,E3)」を入力します。
どうでしょうか。
F列には画像のように表示されたでしょうか。
まとめ
スプレッドシートにあらかじめ用意されている機能を用いることで誰でも容易に考えている処理を行う式を扱うことができます。
一方で複雑な表現を式のみで表現しようとしたときに一見しただけではどんな表示をするための式なのか判断するのが難しい式も作れてしまいます。
式の書き方しかわからない場合には式で表現するしか無くなりますが、今回紹介したカスタム関数を使用すれば複雑な処理はカスタム関数に任せ単純な式は従来のままというハイブリッドな形式でスプレッドシートを扱うことが可能になります。
前回の内容より実践的な内容を紹介したつもりですがいかがだったでしょうか。
これを機に少しでもGoogleAppsScriptに興味を持ち、触ってみようという方が増えれば幸いです。
冒頭で触れましたが今回記事にできなかったカレンダーとスプレッドシートの連携についても、後日記事の方を書きますので少しでも気になりましたらフォローの方をよろしくお願いします。
おまけ
GoogleAppsScriptで単体テスト、というか自分の動作確認用に作ったけど解説する時間が足りなくて載せなかったやつ。
function myTestFunction()
{
myUnitTest("getPriceUnitFromCountry", "'日本'", '円');
myUnitTest("getPriceUnitFromCountry", "'アメリカ'", 'ドル');
myUnitTest("getPriceUnitFromCountry", "'youtube'", '円');
myUnitTest("getGoodsUnitFromGoods", "'A'", '個');
myUnitTest("getGoodsUnitFromGoods", "'B'", 'パック');
myUnitTest("getGoodsUnitFromGoods", "'C'", 'クリック');
myUnitTest("goukei", "'A',80,2,'日本'", 'Aは2個で160円');
myUnitTest("goukei", "'B',1.5,1,'アメリカ'", 'Bは1パックで1.5ドル');
myUnitTest("goukei", "'C',0.1,1,'youtube'", 'Cは1クリックで0.1円');
}
function myUnitTest(func_name_string, args_string, expect_result)
{
var call_string = func_name_string + "(" + args_string + ")"; // func_name_string(args_string) という関数の呼び出しの文字列を作る
var result = eval(call_string); //eval 文字列を関数として実行できる
if (result == expect_result) { //多分 expectedのデータ型によっては期待通りに動作しない。特に浮動小数点
//テスト結果がokなら詳細は表示しない
Logger.log("test_result: ok");
} else {
//テスト結果がngならどのテストがngなのかわかる程度に詳細を表示する
Logger.log("test_result: ng " + func_name_string + "(" + args_string + ") -> " + " result: " + result + ", expected: " + expect_result);
}
}
テスト結果
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ok
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ok
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ng getPriceUnitFromCountry('youtube') -> result: 未定義, expected: 円
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ok
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ok
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ng getGoodsUnitFromGoods('C') -> result: 未定義, expected: クリック
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ok
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ok
[xx-xx-xx xx:xx:xx:xxx XXX] test_result: ng goukei('C',0.1,1,'youtube') -> result: Cは1未定義で0.1未定義, expected: Cは1クリックで0.1円