Googleスプレッドシートで作るチャットアプリ
皆さんはGoogleスプレッドシートを使ったことありますか？
Googleドライブを使ったことがあればきっと誰でも経験したことがあると思いますが、エクセルのようなやつです。エクセルは、インストールしたPC上で動くマイクロソフトのアプリケーションですが、スプレッドシートは表計算の概念としてエクセルのもう一段上の枠組みを指す言葉です。
Googleスプレットシートはクラウド上にあるためインストール不要です。ブラウザさえあれば誰でもどこでもタダで使えますので、とても重宝します。さらには同じファイルを誰とでも共有して一緒に編集できるため、旅行の持ち物リストや、イベントの会計等を仲間で共有する際に利用した事のある方も多いのではないでしょうか。Googleのサービスでは企業向けにG Suiteというプランがあり、メールやカレンダーなどコミュニケーションツールまとめて提供していることから、企業でも取り入れているところが増えていると思います。
という事でここからはスプレットシートを使ったことがあるという方向けのお話になります。
ところでGoogleのこれらのシステムでは、Google Apps Script(GAS)という言語で書かれたプログラムを組み込むことによって、スプレットシートをWebシステムにしたりAPIを公開したりすることが出来るのです。
Excelでは想像も出来ませんでしたが、クラウド上にあること、データはスプレッドシートUIはGASで分離することによって実現出来る技術ですので、Googleスプレッドシートならではと言えます。さらに先に述べたように、ブラウザさえあれば特に開発環境も不要で、タダでシステムが作れてしまうので、ビジネス向けに発展させるととても大きな可能性があるのではないかと思っています。
少し専門的な内容となりますが、プログラミング言語はGoogle Apps Scriptという、JavascriptとVBAを併せたようなものです。多くのエンジニア経験者には馴染みも多いと思います。今回は試作してみたチャットアプリをご紹介したいと思います。とても簡単ですので、皆さんでも是非チャレンジしてみてください。
Google スプレッドシートで作るチャットアプリ
SNSでやるようなチャットアプリにあえてビジネス向けの目的を付け加えるとすると以下のようなところでしょうか。
・イベントなどで会場の方から質問を集めて双方向で進行したいとき
・SNSのスレッドやトークを一般にも解放してみたいとき
こんなときにQRコードだけ配布しておけば、自由にコメントや意見が集められるのでインタラクティブなやり取りが出来そうですね。もっとも今はsli.doという便利なサービスがありますので、こちらのアプリが登場する出番はなさそうですが・・^^;
それではアプリをご紹介します。こちらはまずスプレッドシートの中身です。表計算シートに普通にデータが入っていますね。
そして下記がGASで作ったアプリです。
ブラウザで開いた画面ですが、上記のデータをブラウザ上に表示して、投稿フォームも準備されています。投稿ボタンを押すとデータが追記されて再描画することによってチャットのように機能しています。
早速投稿してみましょう。
入力ボックスにテキストとGoogle検索のリンクを入れ、投稿ボタンを押してみます。
すると以下のように画面に表示されました。
そしてスプレッドシートを開くと投稿したデータも以下のように普通にシートに記載されています。因みに画面には表示していませんでしたが、データを送ってきた人のメールアドレスも自動で取得するようにしています。
チャットアプリの仕組み
このチャットアプリは、スプレッドシートの内容を画面(ブラウザ)に表示する処理と、投稿データを書き込む処理の２つから成っています。それを順番に動かすことによってチャットを実現させています。
①表示処理
指定のスプレッドシートの内容をブラウザへ表示する。画面には投稿フォームを配置しておく
②書き込み処理
投稿フォームから送信された内容を指定のスプレッドシートに書き込む。その後表示処理を動かす
チャットアプリの作り方
次にどのようにして作ったのかを解説します。本当に簡単です。Googleドライブにある自分のスプレッドシートを開き、[メニュー]⇒[ツール]⇒[スクリプトエディタ]の順に進んでいくと、プログラムを書くためのスクリプトエディタというものが立ち上がります。以下の画面です。
そしてスクリプトエディタ内で2つのファイルを作ります。ファイルの作り方は[ファイル]⇒[新規作成]でスクリプトファイル及びhtmlファイルです。
①htmlファイル：index.html
まずは上記の表示処理に該当するのがhtmlファイルです。表示するための処理も含まれているため少し長いですが、このコードはスプレッドシートの内容をブラウザに出力しています。<?～?>で囲まれている範囲以外は一般的なHTMLですので、実行ステップは最低10行ということになります。そのままコピってしまいましょう。[～]の部分はアプリ固有の値になりますので、それぞれの環境でアプリURLやスプレッドシートIDに置き換えてやるとその環境で動きます。
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<style>
#header-fixed{
border: 5px solid #fff; /* 表示領域を白枠で囲う */
position: fixed; /* ヘッダーの固定 */
padding:10px 0 20px; /* 上10px、下20pxをあける */
top: 0px; /* 位置(上0px) */
left: 10px; /* 位置(右0px) */
width: 100%; /* 横幅100% */
height:100px; /* 縦幅140px */
background-color:#FFF /* バックの色 */
}
#content{
top: 100px; /* 位置(上0px) */
left: 10px; /* 位置(右0px) */
padding:150px 0 0px;
width: 100%; /* 横幅100% */
background-color: "#000000" /* バックの色 */
}
#menu{
font-size : 20px;
border : 1px;
padding : 10px 20px 0px 0px;
}
input {
width: 60%;
max-width: 500px; /* レスポンシブの場合の対策 */
box-sizing: border-box; /* [borde-box]で右の飛び出しを回避 */
-webkit-box-sizing: border-box;
-moz-box-sizing: border-box;
}
textarea {
width: 60%;
max-width: 800px;
box-sizing: border-box;
-webkit-box-sizing: border-box;
-moz-box-sizing: border-box;
}
a {
font-size : 10px;
}
input[type="submit"] {
max-width: 100px; /*送信ボタンの幅を指定*/
}
</style>
<div id='header-fixed'>
<a id='menu' href="https://script.google.com/macros/s/[ここにアプリケーションIDを設定]/exec?name=view">再表示</a>
<form method='POST' action='https://script.google.com/macros/s/[ここにアプリケーションIDを設定]/exec?name=postData' id='form'>
<br>
<textarea col="2" name='content' placeholder='メッセージを入力してください。' autocomplete='on'></textarea><br>
<input type='text' name='url' placeholder='参照先URLがある場合はこちらにお願いします。'>
<input type= 'submit' value="投稿" id='botton'>
</form>
</div>
<div id='content'>
<?
var id ='[自分のGoogleドライブのスプレッドシートIDを設定]';
var mySheet = SpreadsheetApp.openById(id).getSheetByName("[スプレッドシートのシート名]");
var endrow = mySheet.getLastRow();
var myData = mySheet.getRange(2, 1 , endrow-1 , 4).getValues();
myMail = Session.getActiveUser().getEmail();
for(var i=myData.length-1;i>-1;i--){
mail = myData[i][0];
time = myData[i][1].toString('yyyy/MM/dd HH:mm:ss');
content = escape_html(myData[i][2]);
url = myData[i][3];
output.append('<font color="#999999" size="1">' + time +'</font>：<font style="bold">'+ content +'</font>'+ '<a href="' +url + '" target="_blank" size="1">' +url + '</a>' +'<br>');
}
?>
<?
function escape_html (string) {
if(typeof string !== 'string') {
return string;
}
return string.replace(/[&'`"<>]/g, function(match) {
return {
'&': '&',
"'": ''',
'`': '`',
'"': '"',
'<': '<',
'>': '>',
}[match]
});
}
?>
</div>
</body>
</html>②スクリプトファイル：chat.gs
次に投稿フォームの書き込み処理です。少しわかりにくいのですが、このファイルは厳密にいうと、URLパラメータによって処理を分岐するところから始まっています。パラメータに何もついてなければ①のindex.htmlが呼び出されます。一方別のパラメータの"・・exec?name=postData"が付いている場合にはまず書き込み処理が動き、その後index.htmlを呼び出しています。書き込み処理の部分はデータをまとめて指定のIDのスプレッドシートに追記しています。こちらも10行未満で実現出来ていますね。コードをコピってchat.gsに貼り付け、スプレッドシートIDを書き換えれば、出来上がりです。
function doPost(e){
doGet(e);
return HtmlService.createTemplateFromFile("index").evaluate();
}
function doGet(e) {
if (e.parameter.name == undefined) {
return HtmlService.createTemplateFromFile("index").evaluate();
}
if (e.parameter.name == 'view') {
return HtmlService.createTemplateFromFile("index").evaluate();
}else if (e.parameter.name == 'postData'){
//スプレッドシート名指定
var id ='[スプレッドシートのIDを設定]';
var sheet = SpreadsheetApp.openById(id).getSheetByName("チャット");
//セッション情報からユーザID(メールアドレス)を取得
var objUser = Session.getActiveUser();
var mail = objUser.getEmail();
//タイムスタンプ取得
var time = Utilities.formatDate( new Date(), 'Asia/Tokyo', 'yyyy/MM/dd HH:mm:ss');
//Getした値を配列にする
//こんな感じなのを以下のように配列してappendRowで追記する
var array = [ mail, time, e.parameter.content , e.parameter.url];
//シートに配列を書き込むことで、指定したスプレッドシートに行が追加されていく
sheet.appendRow(array);
return HtmlService.createTemplateFromFile("index").evaluate();
}
}上記でファイルが出来たら、Webアプリとして公開してみましょう。
そしてWebアプリケーションのURLをコピーしてブラウザでアクセスして、チャットの画面が出てきたら完成です。
如何でしたでしょうか。今回はチャットアプリについての解説でしたが、スプレッドシートをWebアプリにする、という考え方で行くと色んなシートがアプリに出来てしまうということでもあります。世の中のほとんどのシステムはOracleやMySQL、PostgreSQLなどのリレーショナルデータベースであるため、表形式でデータを持っているというのはWebアプリとしても都合が良いのですね。データベースと異なるのは、スプレッドシートは一つのファイルシステムであり、誰でも内容を編集出来たり、ユニーク制約など一貫性を持たせるといった制御が苦手です。その結果不整合が生じてしまい、システムとしてうまく機能しなくなるということが生じてしまいます。Googleスプレッドシートの場合は同時アクセスにも欠落なく耐えられるようですので、プラットフォームとして確立すれば企業にとっても強力なツールになる可能性があると思います。少なくとも、少人数でビジネスを回す上では、本当にタダでシステムを使いこなすことが出来るかもしれませんね。
ということで次回はもう少しシステムらしいものの解説をしたいと思います。
2022/4/14追記：
本記事についてお問い合わせがありましたので追記しておきます。 index.html について、65行目は指定されたIDのスプレッドシートからチャットスレッドを一括で取得する部分ですが、その範囲の求め方として、
・行については、2行目～最終行の行数を求めて指定
・列については、1列～4列を固定で指定
となっています。
そして最終行を求める処理において、データがない場合に2行目～1行目、もしくは0行という、範囲として不正な状態で取得することになり、エラーが表示されると思います。
そのため、上記コードのまま試す場合はスプレッドシートの方にヘッダ及びデータを1件追記しておく必要がありますので、ご注意ください。
L64 var endrow = mySheet.getLastRow();
L65 var myData = mySheet.getRange(2, 1 , endrow-1 , 4).getValues();