【03_家計簿アプリ】家計簿アプリ〔実装編〕スプレッドシートおよびコードのご紹介

記事概要

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

みなさんは家計簿をつけたことはありますか？私はつけたことがありますが続けることが難しいものです。そんな時、”どこでも”つけられる家計簿があると便利です。そんな家計簿をGASで作ってみましょう。GASはブラウザさえあれば、スマートフォンからでもパソコンからでも”どこでも”つけれられるので、継続しやすくなるのではないでしょうか！また、データはスプレッドシートに保存されるので、貯めたデータは自分の好みで如何ようにでも編集できます。この記事は、「テーブル形式フォーム」を応用したものです。ドロップダウンの実装方…

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

これをひな型として、列を増やしたり、パスワードの管理方法を変えたりと自由にアレンジしてみて頂ければと思います。

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

前回の記事はこちら

トップページはこちら

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

まず、Googleドライブでスプレッドシートを作成し、そのスプレッドシートに以下からダウンロードしたエクセルのひな型をインポートしてください。これには式を含めたサンプルデータが入っています。

こちらから

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

エクセルのシートをGoogleスプレッドシートにインポートしたら、シートの見出しを確認しておきます。また、スプレッドシートはお好みで名前を変えておきます。

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

その他、サンプルとしていくつかの日付名のシートが入っています。

フォームを立ち上げるとその日の日付シートが自動作成されます。また、日付を指定して読み出すと、指定した日付のシートが自動作成されます。

スプレッドシートを作成したら、ブラウザの上方にあるURLから、スプレッドシートのIDを確認しておきます。

GASのテーブル形式フォームのスクリプトファイルとテンプレートファイルの作成

このアプリの以下の画面を呼び出すには、「コード.js」と「INDEX.HTML」が必要になります。

まず、Googleドライブから　新規作成　→　GoogleAppsScript によりプロジェクトを新規作成します。

次に、プロジェクト画面より「コード.js」の元のコードを削除した上で、以下のコードを貼り付けてください。

☆各自のスプレッドシートID☆　のところは、各自のスプレッドシートのURLを参照してIDを貼り付けてください。

//---著作：particlemethod------------
//---無断複製・転載・配布を禁じます----
//★★★★doGet関数はURLから呼び出された時に実行する関数｜１つだけ定義できます★★★★
function doGet(e) {

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

  //日付tDateを取得してシート名 myDateにする
  var tDate = new Date();
  var mNames = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
  var mWeeks = ['日', '月', '火', '水', '木', '金', '土'];
  var myDate = "D" + tDate.getFullYear() + mNames[tDate.getMonth()]+ String(100+Number(tDate.getDate())).substring(1)+"_" + mWeeks[tDate.getDay()];

//対象シートをシートの名前を指定して取得
  var mySheet = myApp.getSheetByName(myDate);
  
//対象シートが無ければコピー
  var myFrmSheet = myApp.getSheetByName('書式');
  if(!mySheet){
    var mySheet = myFrmSheet.copyTo(myApp);
    mySheet.setName(myDate);
  }
  //データ記録範囲として、行数と列数を記載
  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.myDate = myDate;   
  
  //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, myDate0){

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



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

  

  //データ記録範囲として、行数と列数を記載
  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]);
	     }
      }
    }
  
 
 //返り値の変数を定義 
 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;

}

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

//シートの値をHTMLに返す関数
function GetSheet(myRows, myCols, myDate0){

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



  
  //対象シートをシートの名前を指定して取得
  var myDate =myDate0  
  var mySheet = myApp.getSheetByName(myDate);
  
//対象シートが無ければコピー
  var myFrmSheet = myApp.getSheetByName('書式');
  if(!mySheet){
    var mySheet = myFrmSheet.copyTo(myApp);
    mySheet.setName(myDate);
  }
  
  

　//取得範囲として行数、列数を設定
　var nRow=myRows;
  var nCol=myCols;

  //シートの値を返り値として渡す
  var myCells = mySheet.getRange(1,1,nRow,nCol).getValues();

  return myCells;
}

上記スクリプトは、doGet関数が含まれており、テンプレートから呼び出されて発動します。

また、プロジェクト画面で、上記コードで呼び出すテンプレートファイル「INDEX.html」を作成し、以下のコードを貼り付けます。

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

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<!---- JQuery導入↑---->

<!---スタイル--↓-->
<style>
　　input[type="number"] { width: 15em; }

    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タグの間にコード記入--【↓ここから】-->

<!---- 日付設定-myDateGASから渡される。---->
<p>簡単家計簿アプリ<a href= "https://gas-de-app.info/kakei1/" >説明はこちら</a></p>


<p>日付設定<br>
<input type="number" id="mY" class="myDate" value="<?=Number(myDate.substring(1,5)) ?>">年<br>
<input type="number" id="mM" class="myDate" value="<?=Number(myDate.substring(5,7)) ?>">月<br>
<input type="number" id="mD" class="myDate" value="<?=Number(myDate.substring(7,9)) ?>">日<br>

<span　>読込みシート名</span>
<span id="myrDate"><?=myDate ?></span>
</p>

<!---- テーブルタイトル----->
　<hr>
 <span　>簡単データベース：シート名</span>
 <span id="myDate"><?=myDate ?></span>

 
<script>
$(function() {
 
  //セレクトボックスが切り替わったら発動
  $('.myDate').change(function() {
  
  var tDate = new Date($("#mY").val(),$("#mM").val()-1,$("#mD").val());
  var tNames = ['01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'];
  var tWeeks = ['日', '月', '火', '水', '木', '金', '土'];
  var newDate = "D" + tDate.getFullYear() + tNames[tDate.getMonth()]+ String(100+Number(tDate.getDate())).substring(1)+"_" + tWeeks[tDate.getDay()];
 

 
    //選択したvalue値をp要素に出力
    $('#myrDate').text(newDate);
  });
});
</script>



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

<!---- テーブルタグと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 {  
           // 4列目は　inputmode="numeric"　にする
           if (iCol == 4) { 
                ?><td contenteditable="true" inputmode="numeric"><?=Cell ?></td><?
                
           // 2列目は　Selet 要素 にする
           } else if (iCol == 2){
                 // td で iD名を　「R行番号」　にする
                ?><td contenteditable="true" inputmode="text" id="R<?=iRow ?>">
                    <select >
                      <!-- 値に応じて「selected」を設定する -->
                      <option value="" <? if(Cell == ''){ ?> selected <? } ?>>－</option>
　　　　　　　　　　　    <option value="食費" <? if(Cell == '食費'){ ?> selected <? } ?>>食費</option>
                      <option value="雑貨" <? if(Cell == '雑貨'){ ?> selected <? } ?>>雑貨</option>
                      <option value="衣類" <? if(Cell == '衣類'){ ?> selected <? } ?>>衣類</option>
                      <option value="交通"<? if(Cell == '交通'){ ?> selected <? } ?>>交通</option>
                    </select>                               
                </td><? 
                
           }else{
               ?><td contenteditable="true" inputmode="text"><?=Cell ?></td><?            
           }
        } 
    } 
}
?>
<!---- JavaScriptのインライン記述-【↑ここまで】---->
</table>




<!---★★★★★★★★ 【テーブルの値を取得してGASに渡す】　★★★★★★★★--->
<!---- JavaScriptの記述-【↓ここから】---->
<script>
 //関数の宣言
 function doWriteSheet(myRows, myCols){
 
   //テーブルの取得
   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列目のセルのテキスト値を取得
      
        if(iCol == 2){
           //---- JQuery記述-【↓ここから】---->

            //IDに追応じてテキスト値を取得し変数に代入
　　　　　　　var myID = "#R"+ iRow;
            myCells[iRow][iCol] =$(myID).closest('tr').find('select').val();
         
           //---- JQuery記述-【↑ここまで】----> 
        } else{
        
		myCells[iRow][iCol] = myTable.rows[iRow].cells[iCol].textContent;
        }
	  }
    }
   //★★テーブルのセルの値をGASのSetSheet 関数に渡し、成功したらonSuccessを実行★★
    <!---- JQuery記述-【↓ここから】-->
     var myDate0 = $('#myDate').text();
    <!---- JQuery記述-【↑ここまで】-->
   google.script.run.withSuccessHandler(onSuccess).SetSheet(myCells, nRow, nCol, myDate0);
 }
 
 //GASの関数が成功したら実行する
 function onSuccess(res){
 
      //メッセージのテキストを取得する
      var msg = document.getElementById("msg");
      
      //メッセージのテキストの中身を変更する
      msg.textContent = '書き込みました。現在、購入数：' + res[0] + '個、合計金額：' +　res+ '円　です。';
 }
</script>
<!---- JavaScriptの記述-【↑ここまで】----> 

<!---★★★★★★★★ 【GASからのデータでテーブルの値を更新】　★★★★★★★★--->
<!---- JavaScriptの記述-【↓ここから】---->
<script> 
//★★GASのGetSheet 関数を実行してSheetのセル値を取得｜成功したらonSuccess2を実行★★


    
 function doUpdateTable(myRows, myCols){
    <!---- JQuery記述-【↓ここから】-->
     var nDate = $('#myrDate').text();
     $('#myDate').text(nDate)
     var myDate0 = $('#myDate').text();
    <!---- JQuery記述-【↑ここまで】-->
    google.script.run.withSuccessHandler(onSuccess2).GetSheet(myRows, myCols, myDate0);
 }
 
//GASの関数が成功したら実行する|resは関数の返り値
 function onSuccess2(res, myRows, myCols){

      //テーブルの取得      
      var myTable = document.getElementById('TableBody');

      //テーブルの行数、列数の宣言      
      var nRow = myRows;
　　   var nCol = myCols;

      //テーブルの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列目のセルの値をGASから返されたresで更新
         //2列目のセルの場合
		   if(iCol == 2 ){
               //セルの値を取得           
               var Cell = res[iRow][iCol];
           
               //行数に応じたID名を設定           
               var myID = "#R"+ iRow;
           
           <!---- JQuery記述-【↓ここから】---->

              //行数に応じたID名の選択肢をセルの値をもとに設定  
              $(myID).closest('tr').find('select').val(Cell);
         
           <!---- JQuery記述-【↑ここまで】---->          
           } else {
            //セル値に応じた値をテーブルに設定  
           myTable.rows[iRow].cells[iCol].textContent = res[iRow][iCol] ;
	       }
          }
      }

      //コメントの取得 
       var msg = document.getElementById("msg");

       //コメントの更新 
       msg.textContent = '表を更新しました。';
 }

</script>
<!---- JavaScriptの記述-【↑ここまで】---->

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

<!---- ボタンの表記---->
 <button onclick="doWriteSheet(<?=myRows ?>, <?=myCols ?>)">書込み</button>
 <button onclick="doUpdateTable(<?=myRows ?>, <?=myCols ?>)">読込み</button>

</body>
</html>