2016年12月13日火曜日

Google App Scriptでつくる簡易超簡易データベースとWebWPI

初めましての方ははじめまして。そうでない方はお久しぶりです。メカヲタ。です。

この記事はFUN Advent Calendar 201612日目の記事です。
今年はクレカとかプリカとかの話はやめにして珍しく技術系の話題をやろうと思います。


皆さん、Google Driveはご存知ですよね?これのGoogle Docks、例えばSpreadsheetsDocumentPages等を使っている方は多いかと思います。
Microsoft Officeの代わりになるオフィスソフト・サービスですが、これ、MS Officeとかのマクロ機能の代わりとなる機能が実はしっかり実装されています。それは「Google App Script」です。

Google App Script(以下、GAS)Javascript(以下、JS)ベースで動作するサーバーサイドのスクリプト言語です。ただ、あくまでもベースがJavascriptなだけなので一部使えない機能もありますが、概ねJavascriptのコードはほとんど動くと思われます。JSが動くと言うだけで、MSofficeのマクロなんかよりできる範囲も広く、GAS自体にWebアプリケーションやWebアプリとして走らせる機能や各種GoogleサービスのAPIと連動したGAS専用関数が用意されており、非常に簡単にWebサービスを作れます。

イベントリスナー(GASではトリガーと言います)も豊富で、各種ドキュメントやシートを開いたときや編集時Googleフォームから送られてきた時、あるいは一定時間ごとをトリガーとしてスクリプトを実行させたりも出来る為使い方次第ではなんにでも化けます。



ということで、本学のプロジェクトではこれを使ってスプレッドシートを”””簡易データベース”””として使う変態実装を行いました。


こんな感じで1行目を各項目名、2行目以降を各種データとしてデータベース化しています。この並び、Googleフォームをスプレッドシートで集計に利用したことがある人ならおなじみのカタチだと思います。では、これを例にしてスクリプト書いてみたいと思います。
GASのプロジェクトはグーグルドライブ上から直接生成する方法と、スプレッドシート上に直接生成する方法の大きく2つがあります(最近Googleフォーム上でも作れるようになったらしいけど知らない)
今回は色々面倒が少ない、スプレッドシートに紐付いたGASを書いていくので、このデータベース化したいスプレッドシートを開いて、

メニューから「ツール→スクリプト エディタ…」クリックしてエディタ画面に移ります。
これが編集画面です。エディタを開くと自動的にプロジェクトファイルが生成されます。スプレッドシートに紐付いたGASの場合はプロジェクトファイルがグーグルドライブ上からは見えません。ちょっと(だいぶ)不便なところです。ではこの開いた画面、多分「コード.gs」の編集画面になってると思うので、ここに各種スクリプトをガリガリ書いていきます。

まず、どのシートを使うのかを指定します。
スプレッドシートをGASで扱う際は「スプレッドシートそのものを指定→どのシートかを指定→扱うセルの位置を指定→各種作業」といった流れで扱っていきます。それぞれが大体オブジェクトなので、先に変数を用意して何回も扱うレベルまで指定したらそれを変数に入れて用意しておくと楽ちんです。
まず先に、スプレッドシートを開いた時のURLのhttps://docs.google.com/spreadsheets/d/XXX/edit......
と書かれている、XXXの部分がスプレッドシートIDです。
これを使って、関数を作ります。スプレッドシートIDを使ってシートを指定するまでのコードはこんな感じになります。

function selectCell() {
  var id = "(スプレッドシートID)";//スプレッドシートID
  var sheet = "(シート名)";//シート名
  var ss = SpreadsheetApp.openById(id);//スプレッドシートIDからスプレッドシートを開く
  var sh = ss.getSheetByName(sheet);//シート名からシートを開く
}

ちなみに、今回はスプレッドシートが直接紐付いているのでスプレッドシートIDを使わなくても
function selectCell() {
  var sh = SpreadsheetApp.getActiveSheet();
}

これだけでシートを開くことも出来ます。スプレッドシートを開いた時にデフォルトで開かれるシートが指定されます。叩いてるメソッド名から分かる通り、「現在アクティブなシートを開く」メソッドです。

これで、sheetオブジェクトを開けました。

あとは、このsheetオブジェクトからセルを指定して値を書き換えたり、削除したり出来ます。
例えば、こんな風にすると
function selectCell() {
  var id = "(スプレッドシートID)";//スプレッドシートID
  var sheet = "(シート名)";//シート名
  var ss = SpreadsheetApp.openById(id);//スプレッドシートIDからスプレッドシートを開く
  var sh = ss.getSheetByName(sheet);//シート名からシートを開く
  var row = sh.getLastRow();
  var column = sh.getLastColumn();
  sh.getRange(row, column).setValue('114514')
}

○sheetオブジェクト利用メソッド
getLastRow():最後の行数を取得。返り値はString
getLastColumn():最後の行数を取得。返り値はString
getRange(n, m):n行m列のセルを指定(n,mそれぞれint型整数値)
getRange('A1'):A1のセルを指定
getRange(n,m,h,w):n行m列から縦にh、横にwの範囲内のセルを指定

○rangeオブジェクト(?)の利用メソッド
setValue('XXX'):XXXへ書き換え

これをメニュー下のボタンから実行ボタン(▶)を押して実行すると

先にGoogleの各種データ類へのアクセス権限を求めるダイアログが出るので「許可を確認」を押します。
すると無事実行され、スプレッドシートを確認してみると


書き込まれている一番最後の列の一番最後の行に「114514」と書き換えられています。成功です!
さらに、メニューの「リソース→すべてのトリガー」を押して


出てきたダイアログをこのように設定すれば、スプレッドシートに紐付けられているGoogleフォームから送信された時に自動で選択した関数(今回はこのselectCell()という関数)が自動で実行されます。フォームが送信されるだけで勝手に臭くなっていく謎のスプレッドシートが完成です。

これを応用すれば、Googleフォームから投稿された内容を使って統計に使いやすいカタチに書き換えてセルに書き込んだり、正規表現を使ってフォームに書かれたURLからパラメータだけを抽出するなど様々に利用できます。また、YoutubeやGmailなどGoogleの主要なサービスと連携できる各種クラスが用意されているので、APIをいちいち叩かなくてもかんたんにGoogleのサービスと連携した何かを作れます。更になんか任意のウェブサイトから情報をパースするとかも出来るらしいです。詳しくは触ってないので知りません。
また、スプレッドシートの内容をJSONやXML等好きなカタチで外部へ出力したりそれをWebAPIで利用できるようにさせられるなど、使い方次第で夢が広がるので、SQLの使い方は全くわからないけど簡易的にでいいのでデータをストックしておきたい、Googleフォームをウェブアプリの入力受付部分の代わりにそのまま使いたいなど、多方面に使えると思います。

僕はこれで関数作りまくってスプレッドシートをデータベースのように操作できるようにしてウェブアプリを実装してしまいました。本学のプロジェクト学習というカリキュラム上での話です。
また、他にも色んな利用例が見つかるので「GAS hoge」と検索してみるといいと思います。


最後に







GASを書くのは構わないが、GASをデータベースとしてマジ使いする可能性は感じないで欲しい




ありがとうございました。


※注意:テキトーかつ素人が書いているので言葉の使用間違いしかなさそうなのでお気をつけください。

参考URL

・Google Apps Script Reference(SpreadSheet)
https://developers.google.com/apps-script/reference/spreadsheet/

0 件のコメント:

コメントを投稿