見出し画像

【GAS】直近2週間の支出がリンク付き棒グラフで自動表示される家計簿アプリ・・・クリックすると日別の帳票が開いて便利！(解説編3）棒グラフ表示用のデータをワークシート関数を使って作成する

Particlemethod

GoogleスプレッドシートをうまくGoogle Apps Script（GAS）と組み合わせると簡易なWEBアプリになります。

WEBアプリの表示ページは、テンプレートにHTMLコードを記載することで作成します。HTMLコードは文字だけではなく図形も表現できますので、工夫次第で、グラフなどデータに対応した図形を掲載することも可能です。

そんな、GASで作ったグラフ付きWEBアプリの一例を、以下の記事で機能をご紹介しています。

これは、帳票から読み取った直近2週間の支出額を、棒グラフで表示させる家計簿アプリです。

1／20の土曜日のもの：1／7～1／20を表示

黄色い日付け（本当は点滅しています）は、当日の日付けで、日が経って週が変わると、自動的に表示期間が変わり、当日を含んだ直近2週間が表示される様に自動更新されます。

1／21の日曜日になると、一週間進んだ1／14から1／27までの表示に変わる



このページは家計簿の目次ページになっていて、下線付きで表示されているリンク文字をクリックすると、日別の帳票ページにアクセスして記帳できる様になっています。

01／09（火）をクリックすると・・・


1／9の帳票が開きます
スプレッドシートには該当するシートが追加されています


目次代わりのグラフの表示ページから、記帳したい日付けにアクセスし、支出額もビジュアルに判る・・・そんな家計簿アプリです。

WEBアプリなので、スマートフォンでもPCでも利用でき、結果はスプレッドシートに蓄積されていくという、優れものです。

－－－－－－－－－－－－－－－－

このアプリは、以下のページでご説明したアプリを進化させたものです。

ここでは、目次は日別の支出額の一覧として表示されています。この支出額をグラフ化したのが本アプリです。


棒グラフの表示の解説はこちら。グラフはHTMLコードの<CANVAS>要素に長方形を描くことで実装しています。

また、棒グラフに重ねたリンク文字の表示の実装の解説はこちら。JavaScriptのappendChild関数を使って、座標を指定した<SPAN>要素（文字）や<A>要素（リンク文字）を子要素として追加しています。

－－－－－－
前置きが長くなりました。

これらのグラフ付きページの実装には、棒グラフやテキストの座標データの準備が必要になります。2週間分なので14セット、結構なデータ量です。

これらの座標データは通常はループ文をプログラムを組んで計算しますが、GASによるWEBアプリでは、せっかくGoogleスプレッドシートと連携できるので、ワークシート関数にこの処理を任せることにしました。

今回の記事では、WEBページ生成のための座標データを、Googleスプレッドシートを利用して計算する方法についてご説明します。


多数の要素の諸元を計算する際に、ワークシート関数を使う利点


このWEBアプリの表示ページには、既に説明した様に、2週間分の棒グラフ、テキスト、リンク文字を配置しています。


これらを実装するには、各日の座標を始めとする諸元を用意しなければなりませんが、長方形（棒グラフ）・文字・リンク文字を14セット（2週間分）用意するので、結構な量のデータが必要です。

数字や日付けなどの文字情報は、日別の帳票から引用してきますが、座標データは帳票にない情報であり、別途計算が必要です。


WEBページにおける各要素の表示に必要なデータの算出要領

WEBページの表示に必要なデータは以下の手順で算出します。

まず、グラフを表示するための各日の原点となる座標を求めます。

ページ全体の原点であるキャンバス要素の原点は画面の左上になり、X座標は右向き、Y座標は下向きで設定されています。Y座標が下向きに正である点に気をつけ、各日の原点を求めていきます。

これに必要なのは、X座標については、週頭の位置とグラフ間のピッチ、Y座標については今週と先週の高さです。図にすると以下の諸元です。


棒グラフとその上下の文字を配置するために必要な諸元は、棒グラフの巾と支出額に応じた高さのデータ、および文字要素のための文字データとその各座標です。

図にすると以下の様になります。


そして、座標値は、各日の原点に、棒グラフとその上下の文字を配置するための諸元を足し込んで最終的なデータとします。

多数の要素の諸元の用意・・・アマチュアにはワークリート利用がおすすめ


さて、こうした諸元のデータ準備は、一般的にはプログラム・コードで対応することと思います。単純な計算なので式も簡単です。

ところが、この簡単そうなプログラム記述は、慣れていないと意外と手こずるものです。

沢山の変数・・・デバッグが結構つらい

プログラム中では、XやYなど似た名前の変数が多数登場するはずなので、デバッグ（だいたい手直しは発生します）する際に、修正箇所を勘違いしがちです。

演算子ｘ－*や変数のスペルミスなどちょっとしたミスが発見できず、しかも余計な修正をしてしまい途方に暮れた経験はないでしょうか。


数値を試行錯誤するためのルーチンがストレス

また、こうしたグラフィック系のプログラムでは、プログラムミスは無くても、数値をどう設定するかで試行錯誤することが多いものです。

文字の位置やグラフの間隔など、結果を見てから、基本設定に関する数値を手直しすることを繰り返すことになるのですが、ちょっとした数値修正のために、エディタで編集し、デプロイし、実行させてみるルーチンをこなさなければならないのが、ストレスです。

表示処理で扱う座標値は、ディスプレイなどデバイスによる微調整が必要なケースもあります。

数値設定や計算をプログラムから切り離し、スプレッドシートで管理する

そこで、アマチュアにおすすめの方法が、数値設定や計算はプログラムに記載せずに切り離し、スプレッドシート上で表現してしまうことです。

スプレッドシートとWEBアプリの連携が容易にできる、GASならではの解決策ですね！

スプレッドシートは、例えばこんな風に用意します。

以下が基本となる数値です。

そして、以下がこれらから算出した座標値です。

中央付近を拡大します。

セル内にはワークシート関数が入力されています。


こうした管理方法には、以下のメリットがあります。

・算出の過程がワークシート関数なのでわかりやすい
・扱う要素の数が増えても対応が容易
・セルの値を修正するだけで数値調整の結果を得られる


プログラムへの取り込み～HTMLコード内～

スプレッドシート上のデータの取り込みは、簡単です。

HTMLコード内で使う場合は、Ｇスプリプト内でテンプレートを取得し、その子要素として代入します。

WEBページ.キャンバス巾 = シート.セル範囲１.getValue();

Ｇスクリプトの全体概要は以下の様になります。

  //テンプレートを名前を指定して取得
  var WEBページ = HtmlService.createTemplateFromFile('テンプレート名');
  
  //★★テンプレートに埋め込む変数値を指定する★★  
  WEBページ.キャンバス巾 = シート.セル範囲１.getValue();
  WEBページ.キャンバス高さ = シート.セル範囲２.getValue();

　//WEBページのホスティング
　return WEBページ.evaluate();

こうしておいて、テンプレート内では、スクリプトレットタグ<?　?>で挟んでこの変数を表記しておくだけです。

<canvas width= <?=キャンバス巾 ?>・・・

全体概要は以下の様になります。

<body>

  <!--＜DIV＞要素内に＜CNVAS＞要素を設定-->
  <div >
    <canvas  width= <?=キャンバス巾 ?> height= <?= キャンバス高さ ?> ></canvas>
  </div>

</body>


プログラムへの取り込み～JavaScriptコード内～

一方、テンプレート内のJavaScriptコードで利用する場合は、まずGスクリプト内に、扱うデータを配列に格納する関数を作っておきます。

以下の様な感じです。（JavaScriptの配列はエクセルVBAなどと異なり、配列は１次元配列しか原則使えないので、格納するには少々工夫します）

function スクリプト内の関数()
{
  ・・・  
  //返り値を入れる配列を用意｜要素数は300としています
  var グラフデータ = new Array(300) ;

 for(ループ){
    グラフデータ[要素i] = スプレッドシート.セル範囲(行、列).getValue();
 }

 return グラフデータ;

 }

この関数を、google.script.run　関数でテンプレート内から呼び出し、返り値（res）として得られる配列から、必要な値を取り出して利用します。

以下の様な感じです。（上記はGスクリプト、下記はテンプレート内のJavaScriptです。似た言語なので混同しない様にしましょう）

<script type="text/javascript">

・・・

  //---グラフ 、図中の文字の描画のデータをスプレッドシートから取得する-----
  google.script.run.withSuccessHandler(返り値の利用).スクリプト内の関数();
 
 
  //返り値を得たら実行する
  function 返り値の利用(res){

  ・・・  
   Ｘ座標_グラフ１ = res[要素1];
   Ｙ座標_グラフ１ = res[要素2];
   巾_グラフ１ = res[要素3];
   高さ_グラフ１ = res[要素4];

  ・・・  
  図形の描画のコード

 }
・・・
</script>


一旦この様なコードとスプレッドシートを作成しておくと、少し変えるだけで様々な用途に応用できると思います。

次の記事では、実装のための具体的なスプレッドシートやコードをご紹介します。


←前の記事はこちら

次の記事はこちら⇒


この記事が気に入ったらサポートをしてみませんか？