GASを使用したデータの集計方法について

Question

宜しくお願いします。

現在webからHTMLのフォームに入力されたデータをDB替わりのスプレッドシートに溜めていき、分析したい場合はHTML上で年度を選択してボタンを押すとDBから条件をもとにデータが集計され表示されるというプログラムを組んでいます。

そこで質問なのですが、現在はDBのデータをすべて取得して、その中から年度と月と選択区分を条件でフィルタリングし、配列のlengthで件数を取得するやり方をとっているのですが、無駄にソースが長くなっていってわかりづらいので、なにかほかに年度を指定した際の配列の要素数を取得するシンプルな方法があればご教授頂きたいのです。

//2次格納用配列
  var ary_list = [];
  
  /*相談件数データ************************************************************************************************************************************************************************************************/
  var inv04 = mst.filter(function(e){return e[44] === e_year && e[45] === "04" && e[10] === "相談"});
  var inv05 = mst.filter(function(e){return e[44] === e_year && e[45] === "05" && e[10] === "相談"});
  var inv06 = mst.filter(function(e){return e[44] === e_year && e[45] === "06" && e[10] === "相談"});
  var inv07 = mst.filter(function(e){return e[44] === e_year && e[45] === "07" && e[10] === "相談"});
  var inv08 = mst.filter(function(e){return e[44] === e_year && e[45] === "08" && e[10] === "相談"});
  var inv09 = mst.filter(function(e){return e[44] === e_year && e[45] === "09" && e[10] === "相談"});
  var inv10 = mst.filter(function(e){return e[44] === e_year && e[45] === "10" && e[10] === "相談"});
  var inv11 = mst.filter(function(e){return e[44] === e_year && e[45] === "11" && e[10] === "相談"});
  var inv12 = mst.filter(function(e){return e[44] === e_year && e[45] === "12" && e[10] === "相談"});
  //年度繰り越し
  var inv01 = mst.filter(function(e){return e[44] === strYear && e[45] === "01" && e[10] === "相談"});
  var inv02 = mst.filter(function(e){return e[44] === strYear && e[45] === "02" && e[10] === "相談"});
  var inv03 = mst.filter(function(e){return e[44] === strYear && e[45] === "03" && e[10] === "相談"});

  //件数
  var sou04 = inv04.length;
  var sou05 = inv05.length;
  var sou06 = inv06.length;
  var sou07 = inv07.length;
  var sou08 = inv08.length;
  var sou09 = inv09.length;
  var sou10 = inv10.length;
  var sou11 = inv11.length;
  var sou12 = inv12.length;
  var sou01 = inv01.length;
  var sou02 = inv02.length;
  var sou03 = inv03.length;
  //年度合計数
  var sou_sum = sou04+sou05+sou06+sou07+sou08+sou09+sou10+sou11+sou12+sou01+sou02+sou03;
  //年度平均
  var sou_ave = sou_sum/12;
  var sou_fix = sou_ave.toFixed(1);
  //1次配列格納用
  var ary_sou = [];
  //格納
  ary_sou.push(sou04);
  ary_sou.push(sou05);
  ary_sou.push(sou06);
  ary_sou.push(sou07);
  ary_sou.push(sou08);
  ary_sou.push(sou09);
  ary_sou.push(sou10);
  ary_sou.push(sou11);
  ary_sou.push(sou12);
  ary_sou.push(sou01);
  ary_sou.push(sou02);
  ary_sou.push(sou03);
  ary_sou.push(sou_sum);
  ary_sou.push(sou_fix);

//ラベルを入れる
    var html = "<table class='table table-bordered' id='table3'>"
    
    html += "<thead>"
    html += "<tr>"    
    html += "<th scope='col'>　</th>"
    html += "<th scope='col'>4月</th>"
    html += "<th scope='col'>5月</th>"
    html += "<th scope='col'>6月</th>"
    html += "<th scope='col'>7月</th>"
    html += "<th scope='col'>8月</th>"
    html += "<th scope='col'>9月</th>"
    html += "<th scope='col'>10月</th>"
    html += "<th scope='col'>11月</th>"
    html += "<th scope='col'>12月</th>"
    html += "<th scope='col'>1月</th>"
    html += "<th scope='col'>2月</th>"
    html += "<th scope='col'>3月</th>"
    html += "<th scope='col' class='border_b'>年度合計</th>"
    html += "<th scope='col' >年度平均</th>"    
    html += "</tr>"
    html += "</thead>"
    
    html += "<tbody>"
    
    html += "<tr>"
    html += "<td>相談件数</td>"
    //4月
    html += "<td align='right'>" + json[0][0] + "</td>"
    //5月
    html += "<td align='right'>" + json[0][1] + "</td>"
    //6月
    html += "<td align='right'>" + json[0][2] + "</td>"
    //7月
    html += "<td align='right'>" + json[0][3] + "</td>"
    //8月
    html += "<td align='right'>" + json[0][4] + "</td>"
    //9月
    html += "<td align='right'>" + json[0][5] + "</td>"
    //10月
    html += "<td align='right'>" + json[0][6] + "</td>"
    //11月
    html += "<td align='right'>" + json[0][7] + "</td>"
    //12月
    html += "<td align='right'>" + json[0][8] + "</td>"
    //1月
    html += "<td align='right'>" + json[0][9] + "</td>"
    //2月
    html += "<td align='right'>" + json[0][10] + "</td>"
    //3月
    html += "<td align='right'>" + json[0][11] + "</td>"
    //年度合計
    html += "<td align='right' class='border_c'>" + json[0][12] + "</td>"
    //年度平均
    html += "<td align='right'>" + json[0][13] + "</td>"
    html += "</tr>"
    

上記のような組み方だと1項目集計するだけでこんな書き方になっちゃいます。
HTML上で帳票みたいな集計表示をしていくとなるとやむなしなのかなと思っていたのですがちょっとでも簡略化できればと思いましてご質問させて頂きました。

よろしければアドバイス頂けますと幸いです。

宜しくお願いします。

Answer 1

なかなか冗長になってますね。。。

SS（スプレッドシート）上でsql文使えるのでそれを使うのも手かもしれません。
Google SpreadsheetでSQLが書ける?Query関数が便利な件。

共通の箇所が多いのでfor文で減らすことは可能だと思います。

また、DB上の日付をunixタイムに変更して（もしくは１列追加）して、その差異によって期間中のデータを抽出した方が簡単かもしれません。
その場合は、dayjs等の日付操作のライブラリの使用をおすすめします。

dayjsの使い方

//検索したいを含む日付を選択　複数ある場合は、　配列にして["2021/4/1,"2021/5/1"]回して下さい。
var period = "2021/5/1"
var periodMonth = dayjs.dayjs(period).format("YYYY年MM月)

var start = dayjs.dayjs(period).startOf('month').unix() //2021/5/1 0:00:00の　unixタイム => 1619794800
var end = dayjs.dayjs(period). endOf('month').unix() //2021/5/31 23:59:59の　unixタイム => 1622473199


//DBの全データを取得
var values = sheet.getDataRange().getValues()

//検索結果 結果を連想配列の形式にして保存する。
var reslust = {}


//valuesの配列を全て確認
values.forEach((e)=>{
  var taskDate = e["日付のindex"]; //　Db上の日付 unixであればそのまま、YYYY/MM/DD  形式なら、dayjsでunixタイムに変換
  var taskName = e["項目のindex"]; 
　
　//この比較で対象期間にあるDBの値を取得できます。
　if(start < taskDate && taskDate < end && taskName=="相談"){
　　　reslust[periodMonth].push(e)
　}
})

//デバッグ等で中身を確認いただくとこんな感じになっているはずです。
reslust = {2021年5月:[DBの該当する行の情報1,DBの該当する行の情報2,DBの該当する行の情報3,]}


//あとは、このreslustを加工してあげてばいいかと思います！

わざわざunixタイムにしなくても、dayjsで直接比較できますが、都度dayjsを通すので件数が多いと処理が遅くなりそうなので、あらかじめunixタイムにしておけば普通の比較だけで処理が可能です。

for分に関しても、valuesが日付どおりに並んでいるのであれば途中でbreak等で止めてもいいと思います。

gasやノーマルのjsは日付操作が非常に面倒なので、こういった日付操作のライブラリを使うと便利です。

html側に関しては innerHTMLとfor分を使って作る方法もあります。
https://developer.mozilla.org/ja/docs/Web/API/Element/innerHTML

gasでも使えるvueなどのフレームワーク入れてあげるとさらに楽に作れてと思いますよ！

Comments

[yamadaman_q]

非常にわかりやすい説明ありがとうございます。

参考にさせて頂きます。

[Tatsunosuke]

お役に立てたようであれば幸いです！
同じようなものは、僕もいくつか作りましたので使える部分もあるかと思います。
もし、jsに不慣れでしたら、forEachや連想配列、dayjsなど理解がしにくい部分もあるかもしれませんが、
このあたりを使えるようになれば、ぐっと進みやすくなると思います！

頑張ってください！応援してます