記事概要
以下記事の家計簿アプリで使っているスプレッドシート、およびGASのスクリプトとテンプレートのコードを記載しています。宜しければ自己責任でご利用ください。
目次
インポートするスプレッドシート
まず、Googleドライブでスプレッドシートを作成し、そのスプレッドシートに以下からダウンロードしたエクセルのひな型をインポートしてください。これには式を含めたサンプルデータが入っています。
エクセルのシートをGoogleスプレッドシートにインポートしたら、シートの見出しを確認しておきます。また、スプレッドシートはお好みで名前を変えておきます。
ひな型では、以下の様な「書式」シートが入っています。
その他、サンプルとしていくつかの日付名のシートが入っています。
スプレッドシートを作成したら、ブラウザの上方にあるURLから、スプレッドシートのIDを確認しておきます。
GASのテーブル形式フォームのスクリプトファイルとテンプレートファイルの作成
このアプリの以下の画面を呼び出すには、「コード.js」と「INDEX.HTML」が必要になります。
まず、Googleドライブから 新規作成 → GoogleAppsScript によりプロジェクトを新規作成します。
次に、プロジェクト画面より「コード.js」の元のコードを削除した上で、以下のコードを貼り付けてください。
//---著作：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>
以上になります。プログラムはWEBアプリとしてデプロイすることで、URLにアクセスするだけで使える様になりますが、デプロイの方法詳細は、以下を参照してください。
