【06_エクセル風外観】「JExcel」でエクセル風にする〔実装編〕スプレッドシートおよびコードのご紹介

記事概要

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

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

これをひな型として、ドロップダウンを工夫したり、別の情報を管理したりと自由にアレンジしてみて頂ければと思います。

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

前回の記事はこちら

トップページはこちら

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

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

こちらから

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

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

インポートするには、Googleドライブ上でスプレッドシートを新規作成し、ファイルメニューから「インポート」を選ぶとできます。ファイル名は適当に修正しておきます。また、スプレッドシートのIDをブラウザのヘッドラインで確認しておきます。

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

以下の様な「支出」シートがあるはずです。

GASのスクリプトファイルとテンプレートファイル

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

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

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

//-----------------------------------------
//----JExcelを使う--------------------------
//---著作：Particlemethod-2021年02月01日-----
//---無断複製・転載・配布を禁じます-------------
//-----------------------------------------

function doGet(e) {
  //★★★★☆スプレッドシートのID☆　部分は　IDは各自のものに打ち換え★★★★
  var myApp = SpreadsheetApp.openById('☆スプレッドシートのID☆');
  var mySheet = myApp.getSheetByName('支出'); 

　var nRow=11;
　var nCol=6;

  //テーブルの行数だけ要素のある配列を宣言
  var myCells = new Array(nRow);
   
  //テーブルの列数だけ子要素を宣言
  for (var iRow = 0; iRow < nRow; iRow++){
　　　myCells[iRow] = new Array(nCol);
　}

  //セルの値を格納
  for (var iRow = 0; iRow < nRow; iRow++){
    for (var iCol = 0; iCol < nCol; iCol++){
　　  　myCells[iRow][iCol] = mySheet.getRange(iRow+1,iCol+1,1,1).getValue();;
　  }
　}


var myHTML = HtmlService.createTemplateFromFile('INDEX');
//★★テンプレートに埋め込む変数値を指定する★★  
myHTML.myMSG ='ボタンをクリックしてください'; 
myHTML.myCells=myCells;
myHTML.myRows=nRow;
myHTML.myCols=nCol;

  //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){

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

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

  //データ記録範囲として、行数と列数を記載
  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;

}

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

[/jin-iconbox01]

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

<!DOCTYPE html>
<html>
  <head>
  <!----------------------------------------->
  <!----JExcelを使う-------------------------->
  <!---著作：Particlemethod-2021年02月01日----->
  <!---無断複製・転載・配布を禁じます------------->
  <!----------------------------------------->

    <base target="_top">
    
    <script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script>
    <link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" />
    <script src="https://jsuites.net/v3/jsuites.js"></script>
    <link rel="stylesheet" href="https://jsuites.net/v3/jsuites.css" type="text/css" />

  
  </head>
<body>

<!---- Bodyタグの間にコード記入--【↓ここから】-->
<!---★★★- ☆スプレッドシートへのリンク☆　は各自のものに打ち換え-★★★--->

<p>簡単データベース(１１行目以降は無視されます)<a href= "☆スプレッドシートへのリンク☆" >直接編集はこちら</a></p>

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


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

<!---- 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>

<div id="myExcelBody"></div>

<!---- テーブルの値の出力--【↓ここから】---->
<script>
   //テーブルの取得
   var myTable = document.getElementById('TableBody');
   
   //テーブルの行数、列数の宣言
   var nRow = myTable.rows.length;
　　var nCol = myTable.rows[0].cells.length;
  
   //テーブルの行数だけ要素のある配列を宣言
   var myData = new Array(nRow-1);
   var myTitle = new Array(nCol-1);

   //テーブルの列数だけ子要素を宣言
   for (var iRow = 0; iRow < nRow; iRow++){
　　　　myData[iRow] = new Array(nCol-1);
　　}
   

   //テーブルのrowsコレクションで行数を取得| 各行でループ
  for (var iRow = 1 ; iRow < myTable.rows.length; iRow++) {
  
    //iRow行のcellsコレクションで列数を取得| 各列でループ
	  for (var iCol=1 ; iCol < myTable.rows[iRow].cells.length; iCol++) {
      
	 	//iRow行目のiCol列目のセルのテキスト値を取得
		  myData[iRow-1][iCol-1] = myTable.rows[iRow].cells[iCol].textContent;
    }
  }

// <!---- テーブルの値の出力--【↑ここまで】---->





myExcelBody = jexcel(document.getElementById('myExcelBody'), {
    data:myData,
        columns:[
        { title:<?= myCells[0] ?>, type: 'dropdown',source:['','現金','クレカ','suica'] , width:80 },
        { title:<?= myCells[0] ?>, type: 'dropdown',source:['','食費','雑貨','交通費','その他'] ,width:80 },
        { title:<?= myCells[0] ?>,type:'text',width:100 },
        { title:<?= myCells[0] ?>,type:'numeric',width:90 },
        { title:<?= myCells[0][5] ?>, type:'numeric',readOnly:true,width:100 }
    ]
});

// <!---- テーブルの値の出力--【↑ここまで】---->
// <!---★★★★★★★★ 【テーブルの値を取得してGASに渡す】　★★★★★★★★--->
// <!---- JavaScriptの記述-【↓ここから】---->
//関数の宣言
function doWriteSheet(myRows, myCols){ 
  
  //テーブルの行数、列数の宣言
  var nRow = myRows;
　var nCol = myCols;

  //テーブルの行数だけ要素のある配列を宣言
   var myCells = new Array(nRow+1);
   
   //テーブルの列数だけ子要素を宣言
   for (var iRow = 0; iRow < nRow+2; iRow++){
　　　　myCells[iRow] = new Array(nCol+1);
　　}
   
   //各行でループ
  for (var iRow = 0 ; iRow < nRow; iRow++) {
  
    //各列でループ
	  for (var iCol=0 ; iCol < nCol; iCol++) {
      
	 	//iRow行目のiCol列目のセルのテキスト値を取得
    //getValueFromCoordsは、列、行の順の指定なので注意
		  myCells[iRow+1][iCol+1] = myExcelBody.getValueFromCoords(iCol,iRow);
	  }
  }
   
   //★★テーブルのセルの値をGASのSetSheet 関数に渡し、成功したらonSuccessを実行★★
   google.script.run.withSuccessHandler(onSuccess).SetSheet(myCells, nRow, nCol);
}

 //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 ?>)">書込み</button>

</body>
</html>