Google Apps Scriptでダイナミックなwebページを作る その１

webページからスプレッドシートに入力値を追加して合計をwebページに表示するサンプルをGoogle Apps Scriptを使って作ってみましたので、その手順を紹介します。

Googleフォームでもスプレッドシートに入力内容をまとめることはできますが、その結果を入力者に知らせる機能はなさそうなので、今回紹介する手法が使えると思います。

Google Apps ScriptはGoogleドライブの機能の１つですが、スプレッドシートに自動処理を組み込んだり、フォームと連携して処理を自動実行したりできます。それ以外にもhtmlのページと組み合わせてwebアプリを作ることが可能です。スプレッドシートやカレンダーと組み合わせて様々な処理を実行できます。PHPなどで作るよりはお手軽です。

Google Apps Scriptは「コード.gs」の書類と「index.html」の2つで構成されます。cssも追加できますが、今回はやってません。

コード全体

まずはhtmlの部分のコードです。

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <form>
      <input id="depositID">
    </form>
    <button onclick="appendSheet()">入金処理</button>
    <div id="return"></div>
  </body>
  
  <script>
    function appendSheet() {
      var deposit = document.getElementById('depositID').value;
      console.log("deposit= " + deposit);
      google.script.run.withSuccessHandler(showReturnValue).lock_and_AppendSheet(deposit);
    }
    
    function showReturnValue(returnString){
      console.log("returnString" + returnString);
      document.getElementById('return').innerHTML = returnString;
    }
    
  </script>
</html>

入金金額を入力して「入金処理」のボタンを押すと、ページの下に今までの入金金額の合計を表示します。

次にコード.gsの中身です。

function doGet() {
  // 表示したいHTMLのファイル名を指定(拡張子は不要)
  return HtmlService.createTemplateFromFile("index").evaluate();
}

function lock_and_AppendSheet(deposit){
  //https://docs.google.com/spreadsheets/d/スプレッドシート固有のSSID/edit?usp=sharing
  var ssId = 'スプレッドシート固有のSSID';// 上の共有リンクのSSID部分。SSIDからスプレッドシートの取得
  var ss = SpreadsheetApp.openById(ssId);
  var sheet = ss.getSheetByName("bankBook");// 指定されたシート名からシートを取得して返却
  
  Logger.log(deposit)
  
  sheet.appendRow([new Date(),deposit]);
  
  var bankBookArray = sheet.getDataRange().getValues();
  
  //合計を求める
  var total = 0;
  for (var i = 1 ; i<bankBookArray.length ; i++){
    total = total+ bankBookArray[i][1];
  }

  return total;
}

lock_and_AppendSheet()がindex.htmlのJavaScripから呼び出され、渡された値をスプレッドシートに記入し、スプレッドシートの全要素を配列に入れて合計金額を計算して合計値をretunでJavaScriptに返します。

コードの説明　コード.gs

それではコード.gsから内容を説明していきます。

function doGet() {
  // 表示したいHTMLのファイル名を指定(拡張子は不要)
  return HtmlService.createTemplateFromFile("index").evaluate();
}

この部分は決まり文句みたいなもんです。このスクリプトが呼ばれるとindex.htmlを返してブラウザに表示させます。（多分、そういうことだと思う）

function lock_and_AppendSheet(deposit){
  ...
}

この関数がJavaScriptから呼ばれます。depositに預金額が入ります。

  var ssId = 'スプレッドシート固有のSSID';// 上の共有リンクのSSID部分。SSIDからスプレッドシートの取得
  var ss = SpreadsheetApp.openById(ssId);
  var sheet = ss.getSheetByName("bankBook");// 指定されたシート名からシートを取得して返却

スプレッドシートのSSIDは共有リンクの真ん中あたりの文字列になります。そのIDで開くスプレッドシートを指定します。(SpreadsheetApp.openById)
bankBookはスプレッドシートのシート名です。ss.getSheetByNameでそのシートにアクセスする変数sheetをgetします。

sheet.appendRow([new Date(),deposit]);

sheet.appendRowでシートの最後に1行追加します。new Date()で現在の時間（タイムスタンプ）、変数depositはJavaScriptから渡された値。

var bankBookArray = sheet.getDataRange().getValues();

getDataRangeでシート全体を配列bankBookArrayに入れます。この配列は二次元配列になっています。

var total = 0;
  for (var i = 1 ; i<bankBookArray.length ; i++){
    total = total+ bankBookArray[i][1];
  }

ループで回して合計を求めてreturnで合計値をJavaScriptに返します。

コードの説明　index.html

次にhtmlの中身を説明します。htmlの中には<form> … </form>がありますが、その中には入力フィールドが１つあるだけです。そのフィールドに入力された値をボタンを押した時に動くJavaScriptのappendSheetでgetしています。

　<body>
    <form>
      <input id="depositID">
    </form>
    <button onclick="appendSheet()">入金処理</button>
    <div id="return"></div>
  </body>

inddex.htmlのbody部分は上記のようになります。
<form>
<button>
<div>
の３つの部分があります。<form>は入力フィールドを表示するため。<button>はクリックしてもらうボタンを表示するため。最後の<div>は返ってきた値を表示するためのものです。

html部分は単純です。処理はJavaScriptで行なっています。<script>…</script>の部分です。それではJavaScriptが何をしているのか説明します。2つの関数があります。1つはボタンから呼び出され実行される関数。もう１つはコード.gsとの通信終了後に実行される関数です。ネットワークを使っているので、処理が即座に終わることはありません。ネットワークの状況により待たされることがあります。そこでこのような２段階になります。

function appendSheet(){…}の中ではフィールドに入力された値を入手しコード.gsの関数を呼び出しています。その部分は次の部分です。

google.script.run.withSuccessHandler(showReturnValue).lock_and_AppendSheet(deposit);

withSuccessHandlerをつけて通信終了後にshowReturnValuを呼び出すようにします。lock_and_AppendSheetから返された値はshowReturnValuに渡されます。

function showReturnValue(returnString){
      console.log("returnString" + returnString);
      document.getElementById('return').innerHTML = returnString;
    }

コード.gsからreturnされた値はreturnStringに入っています。IDで指定したdivにinnerHTMLで表示しています。

ちなみにコード.gsから返される値は配列を使うことも出来ます。今回の例では表全体を配列にして、それを返してwebページ上に表示することも出来ます。ただしこの時にスプレッドシートのタイムスタンプをそのまま配列にするとJavaScriptの日付フォーマットと違うためか配列がnullになります。

そこで、コード.gs側で日付を文字列に変換するなどしてから返す必要があるようです。

さらに厄介なのはJavaScriptの日付処理関連はブラウザによって癖があるので特定ブラウザで動かなくなることがあるようです。日付を使った処理はサーバ側(コード.gs)で行なった方が安全なようです。