Google Apps Scriptで遊んでみる
RasPi工作進めたいながらも突然のPIC工作課題やサークル仕事で滞る今日この頃です
サークルの方はそろそろ引退時期になってくるのでRasPiの本格再始動は数週間後に...
サークルといえば僕は会計をしているのですが、会計に関わる人間は数人いる上、活動場所の物理距離が離れているので一時期お金の管理が非常に大変でした
そこで試行錯誤ののち使い始めたのがGoogleAppsScript(略称GAS)
今回は導入までの経緯と使えるようになるところまでmemoしておきます
以下目次です
- 会計にGASを利用するようになるまでの経緯
- GoogleAppsScriptとは
- 会計の補助ツールとして作ったもの
- GASの導入
- GASでフォーム等のアプリケーションを作る手順
- GASとSpreadSheetの結びつけ
- スクリプトエディタからシートのデータを扱ってみる
- まとめ
会計にGASを利用するようになるまでの経緯
僕の属するサークルは性質上支出が額的にも回数的にも多く、それに伴いメンバーの立替も多くなっていました。
さらに活動拠点が主に２箇所ですが物理的に距離がそこそこあり、会計が一人だと立替をしてくれたメンバーへの返金が滞ります。
以上の理由から会計は複数人いて、ゆうちょ銀行を利用することで２つの活動拠点それぞれで返金対応を可能としています。*1
しかし、お金の出入り口が１つでないということは、お金の正確な管理の難易度が上がったことを意味しています。
Aに対する支払いを相手がしたものと思っていたら実はBに対する同額の支払いだったなんてことも起こり得ます。
LINEで報告をし合うようにはしていましたが、会計の仕事に関すること以外の連絡も当然同じトーク画面で行うので漏れがあったり、本当に処理したのか後から確認することは困難でした。
そこで、物理的距離を解消し、かつ瞬時に正確に同じ情報を共有する目的でクラウドを利用することを思いつき、色々と調べた結果GASが一番手軽になんとか機能を実現できそうだということになったのです。
GoogleAppsScriptとは
Google Apps Script（通称GAS）は、Googleが提供する11のサービスをクラウド上でスクリプトを実行することで操作できるサービスです。スクリプトの言語は、JavaScriptをベースとしています。
<引用元>
簡単にいえば、Googleのサービスを自分好みに利用するためのツールというイメージかと思います。
JavaScriptベースということで少し勉強すればなんとか利用可能な状態にはなるかと思います
ただし！それは導入ができれば、の話です。
サイトってどうなってるんだ、ページ間の結びつきは？と分からないことだらけだった僕は結構苦戦してしまいました...
今回作ったものとそれに利用したもの、利用の方法など見ていきましょう
会計の補助ツールとして作ったもの
- 登録に用いるフォーム
- フォーム入力内容が飛ばされる表
以上２つです
フォームはGoogleフォームというのもありますがパッと見た感じ作りたい内容のフォームが作れなかったので独自で作りました
表はGoogleSpreadSheetを利用します
普通に表計算ツールですが、GASと組み合わせることで様々な機能を実装できます
では、GASの導入方法を見ていきます
GASの導入
Googleアカウントがあれば誰でも利用可能ですが、導入に少し操作が必要なので見ておきます
Googleドライブにアクセスして
新規>>その他>>アプリを追加 を選択
GoogleAppsScriptを探します
よく利用されるのでトップに表示されていましたが見当たらない場合は検索しましょう
ポインタをGASの位置に移動すると以下のようになるので「接続」をします
以上でGASが利用できるようになりました！
もう一度ドライブで新規をクリックするとGoogleAppsScriptが増えていることがわかると思います。
GASでフォーム等のアプリケーションを作る手順
先ほどの続きでGASを開くとこんなページが出てくるかと思います
関数を定義して動かしていくので最初から
function myFunction(){
}
という記述があります。
これで色々な処理を記述していくわけですが、処理の書き方はJavaScriptやGASのリファレンス等を参考に勉強します
最終どんなものをどんな形で公開するか、を考えないといけないです
「公開」の項目は次のようになっています
各項目の詳細についてはここでは割愛します
今回僕自身が利用したのは一番上の「ウェブアプリケーションとして導入...」です
これによって独自のフォームを作っています
しかし、拡張子.gsのコードだけではページの中身はできません
HTMLファイルが必要になるので「ファイル」よりHTMLファイルを追加してページな中身を書きましょう
書けたらウェブアプリケーションとして導入していきます
公開>>ウェブアプリケーションとして導入...
を選択すると次のような画面が出てきます
- プロジェクトバージョン
プログラムを更新するたびに新しくしましょう - 次のユーザーとしてアプリケーションを実行
これは「自分」と「ウェブアプリケーションにアクセスしているユーザー」の２つから選びます
一般にみんなに使ってもらうようにする場合「自分」でいいかと思います - アプリケーションにアクセスできるユーザー
これは「自分だけ」「全員」「全員(匿名ユーザーを含む)」から選択します
今回はとにかくサークルメンバー全員に使ってもらいたいので「全員(匿名ユーザーを含む)」を選択しましょう
これで導入をクリックすれば共有のためのリンクが生成されて利用可能になります
GASとSpreadSheetの結びつけ
ここは全く無知だった僕にとって結構な難所となった部分です
２つの機能をどうやって結びつけて利用するのか
具体的に見ていきます
GASからSpreadSheetを指定する
様々な処理は先ほど述べたようにGASの拡張子.gsとついているファイルの中で関数の形で書いていきます。
この処理の対象としてシートを指定したい、そこでいろいろ調べてみると「シートのid」を指定しましょうと書かれています
シートのidって何？
概念的にはわかるけどどれのことか詳しく言ってくれないと分からないよ！
と怒りながらも探し続けてようやくわかりました
Googleドライブより
新規>>Googleスプレッドシート を選択して新しいファイルを作って見てください
開いたシートのURLの部分を見てみると以下のような形になっています
https://docs.google.com/spreadsheets/d/(アルファベットと数字の文字列)/edit#gid=0
勘の良い方はお気づきでしょう
この(アルファベットと数字の文字列)と書いた部分、ここがシートのidとなります
この文字列で扱うシートを指定するんですね
GASでこのシートを指定するときは例えば以下のように記述します
var id = "(アルファベットと数字の文字列)";
var ss = SpreadsheetApp.openById(id);
ここまでで"ss"という変数によって先ほど作成したスプレッドシートのファイルまでは指定できるようになりました
しかし、ここまでではスプレッドシート全体を指定しているので複数枚作れるシートについてどのシートに関して処理を行うのかも指定しないといけませんね
そこで以下のような記述を追加しましょう
var sheet = ss.getSheetByName("シートの名前");
ここで赤字にしたssというのは先ほど定義したssのことです
したがって別の変数名にした場合は適宜変えるようにします
「シートの名前」は以下の画像に示す部分の名前をそのまま入れます
この画像の場合は
var sheet = ss.getSheetByName("シート１");
とすればこのシートを指定できるということです
シートへの処理は基本的に
- セルを指定する
- セルのデータを取得するorセルに値をセットする
の流れで行うことになるでしょう
ここまでで別々に作ったファイルどうしの相互利用みたいな部分はわかるようになったかと思います
次はシートからシートに付属したような形のスクリプトエディタの扱いを見ていきましょう
スクリプトエディタからシートのデータを扱ってみる
スプレッドシート内のデータをいろいろ触るための処理を記述して必要な機能を追加してみましょう
ツール>>スクリプトエディタ をクリック
するとドライブから新規でGASを選択した時と全く同じページが現れます
ここに表を触るための処理を書いていきます
スプレッドシートの指定
先ほどはidからシートを指定していきましたが、実はもう一つ方法があります
それは以下のような指定方法です
var ss = SpreadsheetApp.getActiveSpreadsheet();
おそらくこれはシートに最初から紐づいたGASだから使える指定方法だと思います
これで先ほどのidで指定したようなスプレッドシートのファイル全体を指定する変数をssとできました
詳しいことが書かれたページを見つけたので貼っておきます
次に何かの機能を実際につけてみましょう
条件に合う項目の金額を足し合わせる
会計の補助ツールとして開発しているのであると便利な機能です
こんな形の表において
- A2セルに氏名を入力する
- E列からA2の名前と合致する名前を見つけ、以下の条件に合う列の金額を合計して「返還金額」に表示する
<条件>
・C列の「分類」が「支出」であること
・K列の「領収書」にチェックが入っていること
・I列の「返還」にチェックが入っていないこと
このような機能を実装しましょう
条件は結局、立て替えてくれたお金の中で領収書も会計がちゃんと確認したもののうち、まだ返金が済んでいないお金を探し出すようになっています
実際のプログラムは以下のようになっています
1 function onEdit(){
2 var ss = SpreadsheetApp.getActiveSpreadsheet();
3 var sheet = ss.getSheetByName("会計報告");
4 var lastRow = sheet.getLastRow();
5 var range = sheet.getRange(2,1);
6 var sum = 0;
7 for(var i = lastRow; i > 3; i--){
8 var range1 = sheet.getRange(i,9);
9 var range2 = sheet.getRange(i,5);
10 var range3 = sheet.getRange(i,11);
11 if(range1.getValue() == false && range.getValue() === range2.getValue() && 12 range3.getValue() != false){
13 var range4 = sheet.getRange(i,7);
14 var value = range4.getValue();
15 sum += value;
16 }
17 }
18 var range5 = sheet.getRange(2,2);
19 range5.setValue(sum);
20 }
上から要点を解説しましょう
1 関数の名前です。onEdit()は少し特別な関数名でシートに編集が加わった際に自動で行われる処理を記述できます。
逆に言えば、シートに変更を加えるだけで自動で起動してほしい処理を書くために使う関数名です。
4 シートにデータのある最終列の行番号を取得しています。上の画像の場合、具体的な金額等の登録データが４行目以降に入りますが、まだ空なのでlastRowには3という値が入ります。
5 2行1列目、つまりA2のセルを取得しています。A2という値で指定することもできます。その場合は(2,1)の部分を("A2")と書けば良いです。
6 最終的に合計金額の値を入れるsumという変数を初期値0で定義しています。
合計金額を入れる箱を準備したイメージです。
7-17 最終行から4行目までをチェックしていく処理をfor文で書いています。
range1〜range3はそれぞれ条件として吟味する列のセルの位置を指定しています。
11 から始まるif文では先ほど定義した各rangeに対してgetValueでセル内の値を引っ張ってきて条件判断しています。具体的には以下のように対応しています
①I列の「返還」にチェックが入っていないこと↔︎range1.getValue() == false
②A2に入力された名前と吟味する行の氏名列の値が一致していること↔︎Erange.getValue() === range2.getValue()
③K列の「領収書」にチェックが入っていること↔︎range3.getValue() != false
④C列の「分類」が「支出」であること↔︎？？？
実は分類が支出かどうかは、収入分類であれば最初から「返還」にチェックを入れるプログラムを別で動かしているので①で判断できてしまいます。
このプログラム内でこの条件を入れる場合はrange6 = sheet.getRange(i,3)のような形で「分類」の列を取得してきてif文の条件式内に入れましょう。
さて、そして13-15の部分で条件に当てはまる金額の値を取ってきてsumに足し合わせています。
ここまででsumに表示したい金額が入ったのであとは表示させるだけですね
18-19 B2セルを取得し、sumの値をセットする処理を書いています。
長かったですが以上がプログラムの内容の解説です。
初心者なので至らないところも多々ありますがその辺はご勘弁を...
実際動かしてみると楽しいものです
今回はonEdit()という特殊な関数でしたが、自分で様々な処理を行う関数を用意すると思います。
すると、シートから処理を指定して使いたいこともありますね
次はそんな時に便利な関数です
スプレッドシートに独自の処理メニューを追加する
赤丸で囲んだ部分はシートに関するいろんな機能が入ってますね
ここに自分の書いた処理を実行できるメニューを追加しましょう
プログラムは以下の通りです
function showMenu(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menu = [
{name: "機能名１", functionName: "function1"},
{name: "機能名２", functionName: "function2"},
{name: "機能名３", functionName: "function3"},
];
ss.addMenu("処理メニュー", menu);
}
意外と短い処理です
機能名と書いた部分は関数名で指定した処理にふさわしい名前を書けば良いです
また、メニューはシートを開いたら自動であの欄に追加されて欲しいので「トリガー」という機能を使います
時計のようなマークがトリガーです
GASで書いたプログラムは書いただけではただの処理の羅列で起動するタイミングを与えてやらないといけません
そこで利用するのがトリガーというわけですね
押すとこんな画面が出るので今すぐ追加しましょう
関数を選んでいつ処理するかを指定していきます
今回はシート起動に合わせてshowMenu()というプログラムが実行されて欲しいので以下のようにします
これで保存完了したら一旦シートを閉じて再度開き直してみましょう
もし保存後に承認が云々というページが出てきたら承認して動かせるように進めて行ってください
シートを再度開くとしっかり「処理メニュー」が追加されます
処理メニューとかいた部分は他の名前でも大丈夫ですのでお好みでつけてくださいね
まとめ
GASめちゃくちゃ便利です
今回は独自フォームについて詳しく触れませんでしたがまた機会があればそちらのソースも公開していきます
様々なデータ管理に一役買ってくれそうなGAS、今後も活用術考えていきたいです！
そして次こそはRasPi触っていきたい所存。それでは
*1:ゆうちょ銀行はキャッシュカードのみ、通帳のみでATMからの引き出しが可能
