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

０．初めに

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

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ベースだがすこし古い仕様

１．作ったもの

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

【画面を開いた時】

2か月後の年月を最上部に表示
一番上のパスワード入力エリアはなにも表示しない
対象年月の直近の投稿が2番目のテキストエリアに表示
3番目のテキストエリアにはなにも表示しない
対象年月のそれ以前の投稿は画面最下部にまとめて表示

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

パスワードをチェック
対象年月の直近の投稿内容を前回以前投稿にマージしてSpreadSheetにセット
対象年月の最新の投稿内容をSpreadSheetにセット
対象年月のそれ以前の投稿を画面最下部に再表示
3番目のテキストエリアにOK/NGを表示する

２．SpreadSheetのイメージ

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

年(0)	月(1)	最新内容(2)	前回以前内容(3)
2018	05	5月の最新投稿	5月の前回以前の投稿
2018	06	6月の最新投稿	6月の前回以前の投稿
2018	07	7月の最新投稿	7月の前回以前の投稿

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

３．参考にしたサイト

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

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

４．コード

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();

    // 他のプロセスでロックされてる場合は１０秒待つ
    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列目、２列目の内容を取得
          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>

５．まとめ

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

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

以　上