まいこーmemog

「あっこれなんか使える」と思ったことをメモしたり、なんとなくその日考えたことを書いたりします。

Google Apps Scriptで遊んでみる

RasPi工作進めたいながらも突然のPIC工作課題やサークル仕事で滞る今日この頃です

サークルの方はそろそろ引退時期になってくるのでRasPiの本格再始動は数週間後に...

 

サークルといえば僕は会計をしているのですが、会計に関わる人間は数人いる上、活動場所の物理距離が離れているので一時期お金の管理が非常に大変でした

そこで試行錯誤ののち使い始めたのがGoogleAppsScript(略称GAS)

今回は導入までの経緯と使えるようになるところまでmemoしておきます

 

以下目次です

 

会計にGASを利用するようになるまでの経緯

僕の属するサークルは性質上支出が額的にも回数的にも多く、それに伴いメンバーの立替も多くなっていました。

さらに活動拠点が主に2箇所ですが物理的に距離がそこそこあり、会計が一人だと立替をしてくれたメンバーへの返金が滞ります。

 

以上の理由から会計は複数人いて、ゆうちょ銀行を利用することで2つの活動拠点それぞれで返金対応を可能としています。*1

 

しかし、お金の出入り口が1つでないということは、お金の正確な管理の難易度が上がったことを意味しています。

Aに対する支払いを相手がしたものと思っていたら実はBに対する同額の支払いだったなんてことも起こり得ます。

 

LINEで報告をし合うようにはしていましたが、会計の仕事に関すること以外の連絡も当然同じトーク画面で行うので漏れがあったり、本当に処理したのか後から確認することは困難でした。

 

そこで、物理的距離を解消し、かつ瞬時に正確に同じ情報を共有する目的でクラウドを利用することを思いつき、色々と調べた結果GASが一番手軽になんとか機能を実現できそうだということになったのです。

 

GoogleAppsScriptとは

Google Apps Script(通称GAS)は、Googleが提供する11のサービスをクラウド上でスクリプトを実行することで操作できるサービスです。スクリプトの言語は、JavaScriptをベースとしています。

<引用元>

uxmilk.jp

 

 簡単にいえば、Googleのサービスを自分好みに利用するためのツールというイメージかと思います。

JavaScriptベースということで少し勉強すればなんとか利用可能な状態にはなるかと思います

 

ただし!それは導入ができれば、の話です。

サイトってどうなってるんだ、ページ間の結びつきは?と分からないことだらけだった僕は結構苦戦してしまいました...

今回作ったものとそれに利用したもの、利用の方法など見ていきましょう

会計の補助ツールとして作ったもの

  • 登録に用いるフォーム
  • フォーム入力内容が飛ばされる表

以上2つです

フォームはGoogleフォームというのもありますがパッと見た感じ作りたい内容のフォームが作れなかったので独自で作りました

 

表はGoogleSpreadSheetを利用します

普通に表計算ツールですが、GASと組み合わせることで様々な機能を実装できます

では、GASの導入方法を見ていきます

 GASの導入

Googleアカウントがあれば誰でも利用可能ですが、導入に少し操作が必要なので見ておきます

Googleドライブにアクセスして

新規>>その他>>アプリを追加 を選択

f:id:maikocho:20171022122801p:plain

GoogleAppsScriptを探します

よく利用されるのでトップに表示されていましたが見当たらない場合は検索しましょう

f:id:maikocho:20171022123035p:plain

ポインタをGASの位置に移動すると以下のようになるので「接続」をします

f:id:maikocho:20171022123135p:plain

以上でGASが利用できるようになりました!

もう一度ドライブで新規をクリックするとGoogleAppsScriptが増えていることがわかると思います。

f:id:maikocho:20171022123307p:plain

GASでフォーム等のアプリケーションを作る手順

f:id:maikocho:20171022124555p:plain

先ほどの続きでGASを開くとこんなページが出てくるかと思います

関数を定義して動かしていくので最初から

 

function myFunction(){

 

}

という記述があります。

これで色々な処理を記述していくわけですが、処理の書き方はJavaScriptやGASのリファレンス等を参考に勉強します

developers.google.com

最終どんなものをどんな形で公開するか、を考えないといけないです

「公開」の項目は次のようになっています

f:id:maikocho:20171022125121p:plain

各項目の詳細についてはここでは割愛します

今回僕自身が利用したのは一番上の「ウェブアプリケーションとして導入...」です

これによって独自のフォームを作っています

 

しかし、拡張子.gsのコードだけではページの中身はできません

HTMLファイルが必要になるので「ファイル」よりHTMLファイルを追加してページな中身を書きましょう

f:id:maikocho:20171022130114p:plain

 

書けたらウェブアプリケーションとして導入していきます

公開>>ウェブアプリケーションとして導入...

を選択すると次のような画面が出てきます

f:id:maikocho:20171022130740p:plain

  • プロジェクトバージョン
    プログラムを更新するたびに新しくしましょう
  • 次のユーザーとしてアプリケーションを実行
    これは「自分」と「ウェブアプリケーションにアクセスしているユーザー」の2つから選びます
    一般にみんなに使ってもらうようにする場合「自分」でいいかと思います
  • アプリケーションにアクセスできるユーザー
    これは「自分だけ」「全員」「全員(匿名ユーザーを含む)」から選択します
    今回はとにかくサークルメンバー全員に使ってもらいたいので「全員(匿名ユーザーを含む)」を選択しましょう

これで導入をクリックすれば共有のためのリンクが生成されて利用可能になります

GASとSpreadSheetの結びつけ

ここは全く無知だった僕にとって結構な難所となった部分です

2つの機能をどうやって結びつけて利用するのか

具体的に見ていきます

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のことです

したがって別の変数名にした場合は適宜変えるようにします

「シートの名前」は以下の画像に示す部分の名前をそのまま入れます

f:id:maikocho:20171022144818p:plain

この画像の場合は

 

var sheet = ss.getSheetByName("シート1"); 

 

とすればこのシートを指定できるということです

 

シートへの処理は基本的に

  1. セルを指定する
  2. セルのデータを取得するorセルに値をセットする

の流れで行うことになるでしょう

 

ここまでで別々に作ったファイルどうしの相互利用みたいな部分はわかるようになったかと思います

次はシートからシートに付属したような形のスクリプトエディタの扱いを見ていきましょう

 

スクリプトエディタからシートのデータを扱ってみる

 スプレッドシート内のデータをいろいろ触るための処理を記述して必要な機能を追加してみましょう

ツール>>スクリプトエディタ をクリック

f:id:maikocho:20171022174142p:plain

するとドライブから新規でGASを選択した時と全く同じページが現れます

ここに表を触るための処理を書いていきます

スプレッドシートの指定

先ほどはidからシートを指定していきましたが、実はもう一つ方法があります

それは以下のような指定方法です

 

var ss = SpreadsheetApp.getActiveSpreadsheet();

 

おそらくこれはシートに最初から紐づいたGASだから使える指定方法だと思います

これで先ほどのidで指定したようなスプレッドシートのファイル全体を指定する変数をssとできました

 

詳しいことが書かれたページを見つけたので貼っておきます

qiita.com

 

次に何かの機能を実際につけてみましょう

条件に合う項目の金額を足し合わせる

会計の補助ツールとして開発しているのであると便利な機能です

f:id:maikocho:20171022175509p:plain

こんな形の表において

  1. A2セルに氏名を入力する
  2. 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 シートにデータのある最終列の行番号を取得しています。上の画像の場合、具体的な金額等の登録データが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()という特殊な関数でしたが、自分で様々な処理を行う関数を用意すると思います。

すると、シートから処理を指定して使いたいこともありますね

次はそんな時に便利な関数です

スプレッドシートに独自の処理メニューを追加する

f:id:maikocho:20171022185139p:plain

赤丸で囲んだ部分はシートに関するいろんな機能が入ってますね

ここに自分の書いた処理を実行できるメニューを追加しましょう

 

プログラムは以下の通りです

 

function showMenu(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menu = [
  {name: "機能名1", functionName: "function1"},
  {name: "機能名2", functionName: "function2"},
  {name: "機能名3", functionName: "function3"},
  ];
  ss.addMenu("処理メニュー", menu);
}

 

意外と短い処理です

機能名と書いた部分は関数名で指定した処理にふさわしい名前を書けば良いです

また、メニューはシートを開いたら自動であの欄に追加されて欲しいので「トリガー」という機能を使います

 

f:id:maikocho:20171022190853p:plain

 時計のようなマークがトリガーです

GASで書いたプログラムは書いただけではただの処理の羅列で起動するタイミングを与えてやらないといけません

そこで利用するのがトリガーというわけですね

f:id:maikocho:20171022191057p:plain

 押すとこんな画面が出るので今すぐ追加しましょう

f:id:maikocho:20171022191137p:plain

関数を選んでいつ処理するかを指定していきます

今回はシート起動に合わせてshowMenu()というプログラムが実行されて欲しいので以下のようにします

f:id:maikocho:20171022191257p:plain

これで保存完了したら一旦シートを閉じて再度開き直してみましょう

もし保存後に承認が云々というページが出てきたら承認して動かせるように進めて行ってください

f:id:maikocho:20171022191455p:plain

シートを再度開くとしっかり「処理メニュー」が追加されます

処理メニューとかいた部分は他の名前でも大丈夫ですのでお好みでつけてくださいね

 

まとめ

GASめちゃくちゃ便利です

今回は独自フォームについて詳しく触れませんでしたがまた機会があればそちらのソースも公開していきます

 

様々なデータ管理に一役買ってくれそうなGAS、今後も活用術考えていきたいです!

そして次こそはRasPi触っていきたい所存。それでは

*1:ゆうちょ銀行はキャッシュカードのみ、通帳のみでATMからの引き出しが可能