Edited at

ドシロウトがGASでSpreadSheetをデータベース替わりにWebアプリケーション作ってみた

More than 1 year has passed since last update.


0.初めに

私はエンジニアではないただのドシロウトです。

GASを最近勉強しており、何かデータを保存して表示できるWebアプリケーションを作ってみたくなりました。

データを保存するためにはデータベース的な物が必要ですがGoogle SpreadSheetに保存して表示できるか試しました。

GASでWebアプリケーションを作るメリ/デメは以下。

【メリット】


  • 無料

  • サーバー不要

  • 簡易的なデータベースとしてGoogleスプレッドシートが使える

  • FireBaseもつかえるらしい

  • ロック機能もある

  • ブラウザーだけで作れる

  • トリガーを使えばバッチ処理もできる

【デメリット】


  • 上限がある


GASの上限は以下に書かれています。その他Google Driveの上限もあります(15GBなど)

Quotas for Google Services

https://developers.google.com/apps-script/guides/services/quotas



  • GASのネット上の情報はスプレッドシート周りが多く、Webアプリケーションは少ない

  • GASで使うHTMLはセキュリティの為かiframe要素に変換される

  • GAS、JavaScriptのエラーの調査の仕方がちょっとわかりにくい

  • 外部にJS、CSSを持ちたい場合の使い方が独特

  • GASはJSベースだがすこし古い仕様


1.作ったもの

以下の画像の通りです。ちょっと変な仕様です。

Clipboardtsn2.jpg

【画面を開いた時】


  • 2か月後の年月を最上部に表示

  • 一番上のパスワード入力エリアはなにも表示しない

  • 対象年月の直近の投稿が2番目のテキストエリアに表示

  • 3番目のテキストエリアにはなにも表示しない

  • 対象年月のそれ以前の投稿は画面最下部にまとめて表示

【登録ボタンを押した時】


  • パスワードをチェック

  • 対象年月の直近の投稿内容を前回以前投稿にマージしてSpreadSheetにセット

  • 対象年月の最新の投稿内容をSpreadSheetにセット

  • 対象年月のそれ以前の投稿を画面最下部に再表示

  • 3番目のテキストエリアにOK/NGを表示する


2.SpreadSheetのイメージ

分かりづらいかもしれませんが下表の通りです。(カッコ内は列番号)

年(0)
月(1)
最新内容(2)
前回以前内容(3)

2018
05
5月の最新投稿
5月の前回以前の投稿

2018
06
6月の最新投稿
6月の前回以前の投稿

2018
07
7月の最新投稿
7月の前回以前の投稿

行は年月単位で事前にSpreadSheetを作成しました。


3.参考にしたサイト

参考にしたサイトは以下です。他にも一杯ありましたが忘れてしまいました。

Google Apps Script 事始め Webアプリ編

https://tech.actindi.net/2016/12/08/beginning-of-google-apps-script-webapp.html

Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法

https://tonari-it.com/gas-spreadsheet-speedup/

第30回.並べ替え

https://excel-ubara.com/apps_script1/GAS030.html


4.コード

gsファイル1本、HTMLファイル2本です。

HTMLファイルの内一つは画面用、残りはJSを外出しする為のGAS固有のindex.js.htmlファイルです。

まずはgsファイル


tosenkka.gs

function doGet() {

// tosenkka.htmlを開く
return HtmlService.createTemplateFromFile('tosenkka').evaluate()
}

function include(filename) {
// index.js.htmlを読込、tosenkka.htmlに展開する
return HtmlService.createHtmlOutputFromFile(filename)
.getContent();
}

function updtosenspd2(codex,nen,tuki,naiyo) {
// 画面入力内容でスプレッドシートを更新する
var ansupdtosenspd2 = 'ng'

// パスワードチェック
if (codex == "*********") {
} else {
var passng = ":パスワードが正しくありません";
return ansupdtosenspd2+passng;
};

// Google Driveのファイルを指定
var files = DriveApp.getFilesByName('tosenkka2');

// ファイルが存在すれば以下の処理を実施
if (files.hasNext()) {

// スクリプトロックの取得
var lock = LockService.getScriptLock();

// 他のプロセスでロックされてる場合は10秒待つ
lock.waitLock(10000);

// スプレッドシートのオープン
var spreadsheet = SpreadsheetApp.open(files.next());

// シートを指定
var sheet = spreadsheet.getSheets()[0];

// シート内の全セル情報を取得
var var1=sheet.getDataRange().getValues();

// 各行ごとの処理
for (var rowidx in var1) {

// 同じ年、月の行かチェック
if (var1[rowidx][0]==nen) {
if (var1[rowidx][1]==tuki) {

// 過去の入力内容を3列目に結合してセット
var1[rowidx][3]=var1[rowidx][2]+"<hr>\n"+var1[rowidx][3];
// 今回入力した内容を2列目にセット
var1[rowidx][2]=naiyo;

};
};

}

// 行数と列数を取得
var rows = var1.length;
var cols = var1[0].length;

// シートを更新(ソート順(年、月))
sheet.getRange(1,1,rows,cols).setValues(var1).sort([1,2]);

// スクリプトロックの解除
lock.releaseLock();

ansupdtosenspd2 = "ok"
}
return ansupdtosenspd2;
}

function gettosenspd2(nen,tuki) {
// スプレッドシートに保管した内容の取得

var ansgettosenspd2 = 'ng'

// Google Driveのファイルを指定
var files = DriveApp.getFilesByName('tosenkka2');

// ファイルが存在すれば以下の処理を実施
if (files.hasNext()) {

// スプレッドシートのオープン
var spreadsheet = SpreadsheetApp.open(files.next());

// シートを指定
var sheet = spreadsheet.getSheets()[0];

// シート内の全セル情報を取得
var var1=sheet.getDataRange().getValues();

// 各行ごとの処理
for (var rowidx in var1) {

// 同じ年、月の行かチェック
if (var1[rowidx][0]==nen) {
if (var1[rowidx][1]==tuki) {

// 3列目、2列目の内容を取得
var zenkai = var1[rowidx][3];
var saisin = var1[rowidx][2];
ansgettosenspd2 = saisin + '@@x@@'+ zenkai;

};
};

}

}
return ansgettosenspd2;
}


次に画面用HTMLファイルです


tosenkka.html

<!DOCTYPE html>

<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<meta name="viewport" content="initial-scale=1.0, user-scalable=no">

<link href="https://fonts.googleapis.com/earlyaccess/mplus1p.css" rel="stylesheet" />
<style>
body {
font-family: "Rounded Mplus 1c";
font-size: 100%;
color: #ecf0f1;
background: #34495e;
}

#kkaf {
font-size: 1.5em;
margin: 10px;
}

.cont1 {
font-size: 1.2em;
}

.comm1 {
font-size: 0.8em;
margin: 5px 5px;
}

.contx {
color: #f1c40f;
font-size: 1.2em;
}

</style>

<title>GASAPI</title>

<style type="text/css">

#buttonx {
width:100px;
color:#ffffff;
background:#e67e22;
font-family: fantasy,sans-serif;
font-size:24px;
font-weight:bold;
text-shadow:0 1px 0px #143352,0 2px 0px #143352;
text-align:center;
display:inline-block;
text-decoration:none;
border:1px solid #225588;
padding:15px 0 12px 0;
border-radius:5px;
margin-bottom:20px;
margin-left:10px;
opacity: 0.7;
}

</style>

<style>

div#footer-fixed
{
position: fixed; /* フッターの固定 */
bottom: 0px; /* 位置(下0px) */
left: 0px; /* 位置(左0px) */
width: 100%; /* 横幅100% */
height: 70px; /* 縦幅70px */
}

div#body-bk{
padding: 0px 0 80px 0; /* 下に80pxを余白を取る */
}

input,textarea {
width: 80%;
font-size: 0.8em;
}

</style>

</head>

<body>
<h1>当選結果(v0.1)</h1>
<div>
<span id="nenx"></span>
<span id="tukix"></span>

</div>

<div id="body-bk">

<div id="kkaf">
<input type="text" id="xcode" name="xcode" placeholder="コードを入力">
<textarea id="naiyou" name="naiyou" placeholder="投稿内容" cols="30" rows="10"></textarea>
<textarea id="kekka" name="kekka" placeholder="結果" cols="30" rows="10" readonly></textarea>
<br>
<div class="contx">前回以前の登録内容</div>
<div id="conarea">前回以前表示エリア</div>
</div>

</div>

<div id="footer-fixed">

<!-- 登録ボタンを押した場合js関数tosenputを呼出 -->
<a id="buttonx" onmouseover="this.style.background='#e67e22'" onmouseout="this.style.background='#d35400'" onclick="this.style.background='#e74c3c';tosenput()">登録</a>

</div>

<!-- index.js.htmlを読込 -->
<?!= include('index.js'); ?>

</body>

</html>


最後にJSを外出しする為のGAS固有のindex.js.htmlファイルです


index.js.html

<script>

window.onload = function () {
// 画面読込完了時の処理

// 本日日付の取得
var today = new Date();

// 翌々年月の算出
var keituki = today.getMonth()+3;
console.log(keituki);

if (keituki <= 12) {
var keinen = today.getFullYear();
} else {
var keinen = today.getFullYear()+1;
keituki = keituki - 12;
};

var wknen = String(keinen);
var wktuki = ('0'+ keituki).slice(-2);

// 画面上に翌々年月をセット
document.getElementById('nenx').innerText = wknen;
document.getElementById('tukix').innerText = wktuki;

// 過去入力データ取得
tosenget();

};

function tosenput() {
// データ登録処理
// 画面からパスワード、年、月、入力内容を取得
var pass = document.getElementById('xcode').value;
var xnen = document.getElementById('nenx').innerText;
var xtuki = document.getElementById('tukix').innerText;
var toukou = document.getElementById('naiyou').value;

// GASで処理した後のコールバック後の処理
var callback = function(result) {

// 結果表示用テキストボックスにGASの関数戻り値を表示
document.getElementById('kekka').value = result;
if (result == 'ok') {
// 過去入力データ取得
tosenget();
}
};

// GASの関数updtosenspd2呼出
google.script.run.withSuccessHandler(callback).updtosenspd2(pass,xnen,xtuki,toukou);
}

function tosenget() {
// 過去入力データ取得処理
// 画面から年、月を取得
var xnen = document.getElementById('nenx').innerText;
var xtuki = document.getElementById('tukix').innerText;

// GASで処理した後のコールバック後の処理
var callback = function(result) {

// 結果を区切り文字列で分割
var resultx = result.split('@@x@@');

// 入力テキストエリアに直近登録内容、画面最下部にそれ以前の登録内容をセット
document.getElementById('naiyou').value = resultx[0];
document.getElementById('conarea').innerHTML = resultx[1].replace(/\n/g,'<br>');
};

// GASの関数gettosenspd2呼出
google.script.run.withSuccessHandler(callback).gettosenspd2(xnen,xtuki);
}

</script>



5.まとめ

たったこれだけでサーバー無しでWEBアプリケーションとして公開できます。

利用数が少ない小さなWEBアプリケーション用としていいかなと思います。

以 上