【05_グラフ表示アプリ】グラフ表示アプリ〔実装編〕スプレッドシートおよびコードのご紹介

記事概要

グラフ表示アプリで使っているスプレッドシート、およびGASのスクリプトとテンプレートのコードを記載しています。宜しければ自己責任でご利用ください。

プログラムの著作は著者に帰属します。また、自己責任でご利用ください。何か上手く行かない場合も自力で解決してみてください・・・（公開設定の方法が適切でないとアクセスできない場合があります）。

これをひな型として、グラフを装飾したり、別の情報を管理したりと自由にアレンジしてみて頂ければと思います。

情報漏えいの恐れがあるのでくれぐれもセキュリティーにかかわる情報管理には利用しない様にしてください。

なお、このアプリについては以下の記事が大変参考になりました。この場をお借りして、貴重なノウハウを書いて下さった著者に感謝申し上げたいと思います。

canvas上にHTML要素を置く:JavaScriptメモ

前回の記事はこちら

トップページはこちら

インポートするスプレッドシート

スプレッドシートを新規作成し、以下のリンクからダウンロードしたエクセルファイルをインポートしてください。（式を含めたサンプルデータが入っています）

こちらから

インポート用のエクセルワークブックをダウウンロードする

エクセルのシートをGoogleスプレッドシートにインポートしてファイル名を適当に付けた後、スプレッドシートのＩＤを確認しておきます。

インポートするには、Googleドライブ上でスプレッドシートを新規作成し、ファイルメニューから「インポート」を選ぶとできます。取り込んだらファイル名は適当に修正しておきます。

また、シートの見出しを以下の様に確認しておきます。

「日毎集計」シートは、以下の様なシートがあります。これはグラフの描画データやリンク先の文字列を計算するシートです。

次にこのシートのH1セルの値を修正します。

このシートの、H1セルで、以下の☆～☆の部分を、この後紹介するGASプロジェクトのIDに置き換える様にしてください。（デプロイしてＵＲＬが確定した後に入れます）

https://script.google.com/macros/s/☆日別帳票表示のGASプロジェクトID☆/exec?SheetName=

「書式」シートは、以下の様なシートが入っています。これは日別の帳票シートのひな型シートです。

補足ですが、インポート用のエクセルシートをExcelで確認すると、関数が以下の様にエラー値として表示される場合がありますが、Googleスプレッドシートへのインポート後は正しく表示されるはずです。（もしエラーとなる場合は、申し訳ありませんが関数が適切でない場合は各自で修正してください。数式としては、単に直上のセルの値を足しているだけです）

その他には、サンプルとして日別の帳票が入っています。（特にユーザが修正する必要はありません）

GASの初期画面のスクリプトファイルとテンプレートファイルの作成

次に、以下のグラフを表示するプロジェクトを作成します。

プロジェクトを新しく作成し、「コード.gs」と「HTML.html」を作成しておきます。プロジェクト名は適当で結構です。

新しいプロジェクトは、GoogleドライブからGoogleAppsScriptを新規作成して作ります。「コード.gs」はデフォルトで作成されています。

「コード.gs」に以下のコードを貼り付けてください。

//★★★★doGet関数はURLから呼び出された時に実行する関数｜１つだけ定義できます★★★★
function doGet(e) {

  //アプリケーションを取得｜スプレッドシートのIDは各自のものを記入
　　　var mySperasSheet = SpreadsheetApp.openById('☆スプレッドシートのID☆');

　//シートを取得
　　　var mySheet = mySperasSheet.getSheetByName('日毎集計');

　//Canvasのサイズを取得
     var myCanvas_W = mySheet.getRange(18, 3,1,1).getValue();
     var myCanvas_H = mySheet.getRange(19, 3,1,1).getValue();

　//テンプレートを取得
  var myHTML = HtmlService.createTemplateFromFile('INDEX');

  //テンプレートに埋め込む値を渡す
  myHTML.Canvas_W = myCanvas_W;
  myHTML.Canvas_H = myCanvas_H;

  //HTMLファイルをホスティング｜メタタグを指定してスマホ表示に対応
  return myHTML.evaluate().addMetaTag("viewport", "width=device-width, initial-scale=1.0, minimum-scale=1.0, maximum-scale=10.0");
}



//★★★★ＨＴＭＬから呼び出されて実行する関数★★★★
function getGraphData(){

  
//アプリケーションを取得｜スプレッドシートのIDは各自のものを記入
var mySperasSheet = SpreadsheetApp.openById('☆スプレッドシートのID☆');

//シートを取得
var mySheet = mySperasSheet.getSheetByName('日毎集計');

//返り値を入れる配列を用意
var myGraphData = new Array(300) ;
 
//配列で番号０から14*3ｰ１までの要素は　データ類(3種)　を格納
for(var i=0;i<14;i++){
 //指定したシートのURLを取得
 myGraphData[i*3] =  mySheet.getRange(2+i, 1,1,1).getValue();
   
 //指定したシートの合計値を取得
  myGraphData[i*3+1] =  mySheet.getRange(2+i, 2,1,1).getValue();

//指定したシートの日付を取得
  myGraphData[i*3+2] = mySheet.getRange(2+i, 3,1,1).getValue();
 }

//配列で番号100以降の要素は　グラフの座標値（8種）を格納
for(var i=0;i<14;i++){
 //指定したシートの棒グラフ（長方形）の描画座標を取得

　for(var j=0;j<10;j++){
 　　myGraphData[100+10*i+j] =  mySheet.getRange(34+i, 4+j,1,1).getValue();
  }
 }

//URL結果を返す 
return myGraphData; 

  
}

上記は、doGET関数が含まれており、アクセスするとグラフを表示させるデータをテンプレートに引き渡すスクリプトです。テンプレート内から呼び出して追加のデータを引き渡す関数も含まれています。

☆スプレッドシートのID☆の部分（２か所あります）は、前述のスプレッドシートIDを入れておきます。

次にこのプロジェクトで呼び出す「INDEX.html」テンプレートに、以下のコードを貼り付けてください。

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  
  <style>
  .myClass {
      white-space: nowrap;
   }

  
  .myClass1  {
    font-weight:  bold;
    background-color: yellow;
    white-space: nowrap;
    
	-webkit-animation: blink 1s ease infinite;
	animation: blink 1s ease infinite;
    }

    @-webkit-keyframes blink {
	0% {opacity: 0;}
	100% {opacity: 1;}
    }
    
    @keyframes blink {
	0% {opacity: 0;}
	100% {opacity: 1;}
    }

  </style>
<body>

<!--キャンバス要素を設定-->
<div id="myArea" style="position:relative;white-space: nowrap;margin: 0;padding:0;">
　<canvas id="myCanvas" width= <?= Canvas_W ?> height= <?= Canvas_H ?> ></canvas>
</div>

<!--スクリプトで図形を描画-->
<script type="text/javascript">

//---グラフ描画のデータを取得する-----
google.script.run.withSuccessHandler(DrawGraph).getGraphData();
 
 
//GASの関数が成功したら実行する|resは関数の返り値
function DrawGraph(res){ 
 

 //描画対象
var myArea= document.getElementById("myArea");

 //過去のテキスト要素が無くなるまでループ
while(true){

      //Nameで"myText"要素があれば取得
      var myElms= document.getElementsByName("myText");
      
      //要素の数がゼロならループ終了
      if( myElms.length==0 ) break;
      
      //先頭の要素を削除
      myArea.removeChild(myElms[0]);
}

//カンバス要素を取得
 var myCanvas = document.getElementById('myCanvas');
   
//２D描画要素を取得
 var myContext= myCanvas.getContext('2d');
   
//描画要素を保存しペンを初期化
 myContext.save();  // 定型処理
 myContext.beginPath();


//描画要素の線を設定
 myContext.lineWidth=1;
 myContext.strokeStyle = 'black';
 




//描画する
 for( var iData= 0;iData < 14; iData++){

    //長方形を設定する
      myContext.beginPath();

     myContext.rect(res[100+10*iData] ,res[100+10*iData+1] ,res[100+10*iData+2] ,res[100+10*iData+3]);

      myContext.fillStyle = 'lightgreen';
      //長方形を描画する
      myContext.fill();
      myContext.stroke();
      
      //リンク要素を設定する
      var myAnchor= document.createElement("A");
      var myText= document.createElement("span");
      
　　　　//描画範囲の子要素として追加する
      myArea.appendChild(myAnchor);
      myArea.appendChild(myText);
      
　　　　//リンク要素とテキスト要素の位置を設定する


　　　　//リンク要素のスタイルを設定する
      myAnchor.style.position   = "absolute"; 
      myAnchor.style.left       = res[100+10*iData+6]+"px";
      myAnchor.style.top        = res[100+10*iData+7]+"px";

      myAnchor.name             = "myText";// ひとまとめで処理するための名称
      myAnchor.href             = res[iData*3];
      if(res[iData*3+2].substr(8, 1)=="●"){
      myAnchor.className        = "myClass1";      
      }else{
      myAnchor.className        = "myClass";
      }
      myAnchor.style.fontFamily = "Arial"; 
      myAnchor.style.fontSize   = "12px";
      

       //リンク要素を描画する
      myAnchor.appendChild(document.createTextNode(res[iData*3+2].substr(0, 8)));
      
      
      //テキスト要素のスタイルを設定する
      myText.style.position   = "absolute"; 
      myText.style.left       = res[100+10*iData+4]+"px";
      myText.style.top        = res[100+10*iData+5]+"px";
      myText.name             = "myText";// ひとまとめで処理するための名称
      myText.className        = "myClass"; 
      myText.style.fontFamily = "Arial"; 
      myText.style.fontSize   = "12px";

       //テキスト要素を描画する
      myText.appendChild(document.createTextNode(res[iData*3+1]));
      
      
    }  

var myText= document.createElement("span");
myArea.appendChild(myText);

myText.style.position   = "absolute"; 
myText.style.left       = res[100+8]+"px";
myText.style.top        = res[100+9]+"px";
myText.name             = "myText";// ひとまとめで処理するための名称
myText.className        = "myClass"; 
myText.style.fontFamily = "Arial"; 
myText.style.fontSize   = "20px";

//テキスト要素を描画する
myText.appendChild(document.createTextNode("直近支出推移"));
      

//描画要素の後処理
myContext.restore();  // 定型処理
} 




</script>
  </body>
</html>

以上は、スクリプトから引き渡されたデータをもとに、＜CANVAS＞要素にグラフを描画し、さらにHTML要素（テキストとリンクテキスト）を埋め込むHTMLコードです。

グラフのリンクから、テーブル形式フォームを開くGASの準備

もうひとつ別のプロジェクトを作成し、「コード.gs」と「HTML.html」を作成しておきます。プロジェクト名は適当で結構ですが、機能が分かり易い「日別帳票表示」などとしておく事をお奨めします。

このプロジェクトは、グラフの日付のリンク文字をクリックすると発動して、

日付をクエリーパラメータとして受けて日別の帳票を呼び出すものです。

このプロジェクトの「コード.gs」に以下のコードを貼り付けてください。

このスクリプトには、doGET関数が含まれており、クエリーパラメータ付きでアクセスすると、該当する日付のシートから、テーブル形式フォームとして表示させるデータをテンプレートに引き渡します。

//★★★★doGet関数はURLから呼び出された時に実行する関数｜１つだけ定義できます★★★★
function doGet(e) {

  //アプリケーションを取得｜スプレッドシートのIDは各自のものを記入
  var myApp = SpreadsheetApp.openById('☆スプレッドシートのID☆');

  //★★★対象シートをプロジェクトのURLから指定して取得★★★
  var mySheet = myApp.getSheetByName(e.parameter.SheetName);
  
  
//★★★対象シートが無ければコピー★★★
  var myFrmSheet = myApp.getSheetByName('書式');
  if(!mySheet){
    var mySheet = myFrmSheet.copyTo(myApp);
    mySheet.setName(e.parameter.SheetName);
  }
  
  //データ記録範囲として、行数と列数を記載
  var nRow=11;
  var nCol=6;

  //データ記録範囲を指定して範囲を取得
  var myCells = mySheet.getRange(1,1,nRow,nCol);

  //HTMLファイルのテンプレートをファイル名を指定して取得
  var myHTML = HtmlService.createTemplateFromFile('INDEX');
  
  //★★テンプレートに埋め込む変数値を指定する★★  
  myHTML.myMSG ='ボタンをクリックしてください';   
  myHTML.myCells = myCells.getValues();
  myHTML.myRows = nRow;
  myHTML.myCols = nCol;
  myHTML.mySheetName = e.parameter.SheetName;
  
  //HTMLファイルをホスティング｜メタタグを指定してスマホ表示に対応
  return myHTML.evaluate().addMetaTag("viewport", "width=device-width, initial-scale=1.0, minimum-scale=1.0, maximum-scale=10.0");
}


//★★★★ＨＴＭＬから呼び出されて実行する関数★★★★

//HTMLテーブルの値myCellsをシートに書き込む関数
//★★書き込むシート名を引数に追加★★
function SetSheet(myCells, myRows, myCols, mySheetName){

  //アプリケーションを取得｜スプレッドシートのIDは各自のものを記入
  var myApp = SpreadsheetApp.openById('☆スプレッドシートのID☆');

  //対象シートをシートの名前を指定して取得
  var myDate =mySheetName 
  var mySheet = myApp.getSheetByName(mySheetName);

  //データ記録範囲として、行数と列数を記載
  var nRow = myRows;
  var nCol = myCols;

  //スプレッドシートにmyCellsの値を書き込み 
   for (var iRow = 0 ; iRow < nRow; iRow++) {
　　   for (var iCol = 0 ; iCol < nCol; iCol++) {

        //見出し部分は除外して書き込み 
         if(!(iRow == 0 || iCol == 0 || iCol == 5)){
         mySheet.getRange(iRow + 1,iCol + 1,1,1).setValue(myCells[iRow][iCol]);
	     }
      }
    }
  
  let mySheet2 = myApp.getSheetByName('日毎集計');
//-----関数の再計算のために強制的に同じ内容で同じ位置に貼り付け直し-----
//コピー対象のセル範囲を選択する
let copyRange = mySheet2.getRange(5, 9,1,7);
//貼り付け先のセル範囲を選択する
let pasteRange = mySheet2.getRange(5, 9,1,7);
//コピー対象のセル範囲のデータを貼り付け先のセルにコピーする
copyRange.copyTo(pasteRange);

//コピー対象のセル範囲を選択する
let copyRange2 = mySheet2.getRange(8, 9,1,7);
//貼り付け先のセル範囲を選択する
let pasteRange2 = mySheet2.getRange(8, 9,1,7);
//コピー対象のセル範囲のデータを貼り付け先のセルにコピーする
copyRange2.copyTo(pasteRange2);
//-----関数の再計算のために強制的に貼り付け直し-ここまで----

 
 //返り値の変数を定義 
 var myTotal = new Array(2) ;

　myTotal[0] = 0 ;
　myTotal = 0 ;
  
  //ループを回して6列目を取得（シートの列数は0から始まるので数字は5） 
  for (var iRow = 2; iRow <= nRow ;iRow++){

      //値が数字なら実行） 
     if(typeof mySheet.getRange(iRow,5,1,1).getValue() == 'number'){
        //個数カウント 
        myTotal[0]++;

　　　　　//合計計算 
　　     myTotal+= mySheet.getRange(iRow,5,1,1).getValue();
     }
  }
  //結果を返す 
  return myTotal;

}

以上にはスプレッドシートの値をテンプレートに引き渡す部分と、テンプレートから書き込み時に呼び出されて作動する関数が含まれています。

アクセスすると、goGet()関数が引数付きのURL（クエリーパラメータ）を受け取って作動しますが、goGet()関数が１プロジェクト１つしか設定できないので、別プロジェクトとなっています。

☆スプレッドシートのID☆の部分（２か所あります）は、最初に設定したスプレッドシートIDを入れておきます。

次に上記コードで呼び出す「INDEX.html」テンプレートには、以下のコードを貼り付けてください。

<!DOCTYPE html>
<html>
<head>
<!---著作：particlemethod------------>
<!---無断複製・転載・配布を禁じます---->
<base target="_top">

<!---スタイル【↓ここから】---->
<style>
    tr th { border:#aaa solid 1px; padding:5px; 
         background-color:#999; color:white; }
    tr td { border:#aaa solid 1px; padding:5px; }
<!---スタイル【↑ここまで】---->
</style>

</head>
<body>

<!---- Bodyタグの間にコード記入--【↓ここから】-->

<!---★★ 【テンプレートに渡された変数値からテーブル名を表示】　★★--->
<p>簡単データベース：参照シート名【<?=mySheetName ?>】</p>

<!---★★★★★★★★ 【テンプレートに渡された変数値からテーブルを表示】　★★★★★★★★--->

<!---- テーブルタグとID名称宣言----->
<table id="TableBody">

<!---- JavaScriptのインライン記述-【↓ここから】---->
<? 

// GsValuesの各行 iRow に対して実行。myCellsはGASから渡される。
for(var iRow in myCells) {

   // 各行を Row に代入
   var Row = myCells[iRow]; 

   // 行頭タグ　「tr」｜タグは　？＞・・・＜？で挟む
   ?><tr><?

   // カレント行の 各列 iCol に対して実行
   for(var iCol in Row) {

      // 該当行の 各列 iCol をCellに代入
      var Cell = Row[iCol];

      // 見出しや末尾の列の場合はthタグ記述
      if (iRow == 0 || iCol == 0 || iCol == 5) { 
               ?><th><?=Cell ?></th><?
               
      // 他の場合tdタグ記述
      } else {  
           if (iCol == 1 || iCol == 4) { 
                ?><td contenteditable="true" inputmode="numeric"><?=Cell ?></td><?            
           } else {
               ?><td contenteditable="true" inputmode="text"><?=Cell ?></td><?            
           }
        } 
    } 
   ?></tr><?
}
?>
<!---- JavaScriptのインライン記述-【↑ここまで】---->
</table>


<!---★★★★★★★★ 【テーブルの値を取得してGASに渡す】　★★★★★★★★--->
<!---- JavaScriptの記述-【↓ここから】---->
<script>
 //関数の宣言
//★★書き込むシート名を引数に追加★★
function doWriteSheet(myRows, myCols, mySheetName){
 
   //テーブルの取得
   var myTable = document.getElementById('TableBody');
   
   //テーブルの行数、列数の宣言
   var nRow = myRows;
　　var nCol = myCols;
  
   //テーブルの行数だけ要素のある配列を宣言
   var myCells = new Array(nRow);
   
   //テーブルの列数だけ子要素を宣言
   for (var iRow = 0; iRow < nRow; iRow++){
　　　　myCells[iRow] = new Array(nCol);
　　}
   

   //テーブルのrowsコレクションで行数を取得| 各行でループ
   for (var iRow = 0 ; iRow < myTable.rows.length; iRow++) {
   
	  //iRow行のcellsコレクションで列数を取得| 各列でループ
	  for (var iCol=0 ; iCol < myTable.rows[iRow].cells.length; iCol++) {
      
	 	//iRow行目のiCol列目のセルのテキスト値を取得
		myCells[iRow][iCol] = myTable.rows[iRow].cells[iCol].textContent;
	  }
    }
   //★★テーブルのセルの値をGASのSetSheet 関数に渡し、成功したらonSuccessを実行★★
   //★★書き込むシート名を引数に追加★★
   google.script.run.withSuccessHandler(onSuccess).SetSheet(myCells, nRow, nCol, mySheetName);
 }
 
 //GASの関数が成功したら実行する
 function onSuccess(res){
 
      //メッセージのテキストを取得する
      var msg = document.getElementById("msg");
      
      //メッセージのテキストの中身を変更する
      msg.textContent = '書き込みました。現在、購入数：' + res[0] + '個、合計金額：' +　res+ '円　です。';
 }
</script>
<!---- JavaScriptの記述-【↑ここまで】----> 

<!---- コメント｜変数値はdoGET関数からの渡し値---->
 <p id="msg"><?=myMSG ?></p>

<!---- ボタンの表記---->
<!----★★書き込むシート名を引数に追加★★--->
 <button onclick="doWriteSheet(<?=myRows ?>, <?=myCols ?>, <?=mySheetName ?>)">書込み</button>


</body>
</html>

テンプレートにはインラインコードによるテーブルの作成、およびテーブルの編集結果をスプリプを呼び出してスプレッドシートに上書きする内容が含まれています。

テーブルを上書きした際、スプレッドシートが結果を再計算してくれない場合があるので、データ部分の関数を（無意味ですが）強制的に同じ内容でコピー＆ペーストしています。