1ヶ月ごとの二次元配列を組んでデータをもってきたい

Question

日々の金額データをDBのようにためこんで、そこから月ごとの収支表を出してみたいと考えています。下記のコードは、ざっくりとこんな感じを想定して作っています。

【データ作成の流れ】
DBから日付のみを二次元配列でごっそりとってきて、yyyy/mmの形にする。
1　行数をもとに1番目のyyyy/mm(2019/01など)をとってきて、日付配列の中にそれがいくつ含まれているかを数える
2　最初のデータの行がわかれば、1のデータを足せばDB上で最後のデータの行がわかるため(大体ひと月31日とかになります)、1列ずつ縦にデータを足し、各項目の1ヶ月の総合金額が算出できるようにする。さらに、それらを集めて配列にし、1か月分の配列データを作る。

1,2をループさせ、1ヶ月分のデータを一つの二次元配列になるように押し込んでいく。ループ内で行数とyyyy/mmを取ってくる部分が増えていくため、重複なしで1か月分が取り出せる想定。

【DBの入力データ】

【想定の出力データ】

【未解決部分】
1　何回も実験したが、最終的にシートに張り付けた際に、最後に算出した月のデータしか張り付かなかった。(二次元配列化の失敗？)
2　張り付いたデータについて、1ヶ月目はきれいに合計が出ていたが、2か月目は、次の月の分まで加算した算出データとなってしまっていた。さらに、その次の月では、3か月分が合計されてしまった

非常に複雑になってしまったかと思います。すみません。
コード内で不審な点があれば、ご指摘をお願いします。

//実際にデータが一覧となるシートへ張り付けるための配列データをつくる
function dateChanging1(){
  var data1 = DBSheet.getRange(1, 1, 93, 1).getValues();
  for(var i = 0; i < data1.length; i++){
    data1[i][0] = Utilities.formatDate(data1[i][0],"JST","yyyy/MM");
  }
  var counter = 0;
  for(var row = counter + 1; row < 93; row = counter + 1){
    var originData = DBSheet.getRange(row, 1, 1, 1).getValues();
    originData = Utilities.formatDate(originData[0][0],"JST","yyyy/MM");
    counter = getDatacounter(originData,row,data1,counter);//最初のデータを取得・そのデータがいくつあるかを数える→counterのデータを返す
    var makeArrayData = makeData(row,originData,counter);//対象行の範囲データを取得
    //var insertplusCalculate = makeCalculate();//tsumekomiで作った配列に収支それぞれの計算結果を入れる
    var superarray = [];
    superarray.push(makeArrayData);
  }
  input(superarray);
}
//日付の配列から、探したい日付がいくつあるかを調べる
function getDatacounter(originData,row,data1,counter){
Logger.log(originData)
  for (var i = 0;  i < data1.length; i++) {
    if(data1[i][0] == originData){
        counter++;
    }
  }
  return counter;
}
//探したい日付の個数を実際のDBスプレッドシートにて行数とし、列ごとにデータを配列化する
function makeData(row,originData,counter){
  var N_DBData = [];
  var AAA = DBSheet.getRange(row, 1, counter, DBSheet.getLastColumn()).getValues();
  N_DBData.push(originData);
  for(var i2 = 1; i2 < DBSheet.getLastColumn(); i2++){
    var v = 0;
    for(var i1 = 0; i1 < counter; i1++){
      v = v + AAA[i1][i2];
    }
    N_DBData.push(v);
  }
  return N_DBData;
}
function input(superarray){
  MonthlySheet.getRange("C2:P2").setValues(superarray);
}

Comments

[Lhankor_Mhy]

入力データと想定される出力結果の例示をお願いします。

[Zuian]

ありがとうございます。質問内に、現在の状況と想定している出力データを入れましたので、参考にしていただければと思います。宜しくお願いします

Answer 1

想定する DBSheet シート

A	B	C	D	E	F
2/1	1	0	1	0	2
2/2	1	0	1	0	2
2/3	1	0	1	0	2
2/4	1	0	1	0	2
2/5	1	0	1	0	2
2/6	1	0	1	0	2
2/7	1	0	1	0	2
2/8	1	0	1	0	2
1/1	1	0	1	0	2
1/2	1	0	1	0	2
1/3	1	0	1	0	2
1/4	1	0	1	0	2
1/5	1	0	1	0	2
1/6	1	0	1	0	2
1/7	1	0	1	0	2
3/1	1	0	1	0	2
3/2	1	0	1	0	2
3/3	1	0	1	0	2
3/4	1	0	1	0	2
4/1	1	0	1	0	2
4/2	1	0	1	0	2
4/3	1	0	1	0	2
4/4	1	0	1	0	2
4/5	1	0	1	0	2
4/6	1	0	1	0	2
4/7	1	0	1	0	2
4/8
4/9	1	0	1	0	2

想定する MonthlySheet シート

A	B	C	D	E	F
2020/02/01	8	0	8	0	16
2020/01/01	7	0	7	0	14
2020/03/01	4	0	4	0	8
2020/04/01	8	0	8	0	16

// 実行メニューから「Chorme V8 を搭載した新しい Apps Script ランタイムを有効にする」を選択してください

const q241762 = () => {
    const DBSheet = SpreadsheetApp.getActive().getSheetByName("DBSheet");
    const groupByMonth = DBSheet.getDataRange().getValues().reduce((a,c)=> Object.assign(a, {[toYM(c[0])]: (a[toYM(c[0])] || []).concat([c])}),{});
    const subtotal = Object.values(groupByMonth).map(e=>e.reduce(rowMerger));
    const SubtotalSheet = SpreadsheetApp.getActive().getSheetByName("MonthlySheet");
    SubtotalSheet.getRange(1,1,subtotal.length,subtotal[0].length).setValues(subtotal);
}
const toYM = (date) => Utilities.formatDate(date,"JST","yyyy/MM");
const rowMerger = (a,c) => a.map((e,i) => i === 0 ? e : (e - 0) + ((c[i] - 0)));

補足：質問コードで気になった点

originData = Utilities.formatDate(originData[0][0],"JST","yyyy/MM");

ここ変数がないので、必ず 左上の年月しかとれない。まともに動かなさそう。

補足：質問でわからない点

1　何回も実験したが、最終的にシートに張り付けた際に、最後に算出した月のデータしか張り付かなかった。(二次元配列化の失敗？)

2　張り付いたデータについて、1ヶ月目はきれいに合計が出ていたが、2か月目は、次の月の分まで加算した算出データとなってしまっていた。さらに、その次の月では、3か月分が合計されてしまった

最後に 算出した月 っていうのがわからない。 算出した とはどういう行為をさすのでしょうか。
算出をここでやろうとしている月ごとの集計だと考えた場合、最後は普通 1 つしかないので、1ヶ月目、2ヶ月目のように月で集計を取ったものが複数あるというのは大変に不可思議です。

ということで、シート構成は想像なんで、間違ってたらまるっと的外れになります。

たぶんこの辺をまとめると、質問へのコメントがなされたようにどういうデータがどうなって、やりたいことはどうすることなの、といった話になるんじゃないかと思います。

蛇足：4/8 は未記入でも合計できるとするためあえて空欄です。 C から P のように横に長いデータなので、場合によっては数値として足してはいけないデータがあるのかもしれないですが、そのときは rowMerger だけを修正すれば対応できるだろうという見込。
あと、ヘッダとかありそうですが、ヘッダとか見ためをよくするやつは別シートにしてください。データをデータとして機械可読に保つシートと、それを人間に優しく表示するシートは役割がちがいます。シート!セルでも importrange でも参照ができるので、データとビューは分けましょう。

Comments

[Zuian]

いつもありがとうございます。
ヘッダの件についてはすみません、ちょっと意味が理解できていないので、こちらで確認してみます。取り急ぎ、ご回答いただいた内容についてです。
まず、作りたいものと現在のDBの状態はいただいた表の通りです。どんぴしゃです。
最後に算出した月とは、forループを回す中で最後に該当する月になります。(例えば頂いたシートでいくと、2/1→1/1→3/1の場合、3/1のことです。)
こちらの想定としては、二次元配列に突っ込む→次の月のデータをまとめて配列に突っ込むになるはずなので、月ごとに別々に出てくるはずなのですが、3/1の月で全てのデータがまとまって1つの配列となってしまっていることを指しています。

[papinianus]

MonthlySheetで C2 から記入をはじめているということはAB列や1行目に何らかの見出しがあることが推測されます。
そういうのは別の、整形用のシートでやったほうが無難です。

[papinianus]

> 最後に算出した月とは、forループを回す中で最後に該当する月になります。(例えば頂いたシートでいくと、2/1→1/1→3/1の場合、3/1のことです。)
うーん。makedataで1ヶ月分きりとっている N_DBData は B 列以降のデータしか取ってないので、何月のデータかという情報は失われます。
あと、3/1の月で全てのデータがまとまっている、としたら 「1ヶ月目は正しいが、2ヶ月目、3ヶ月目が全部集計される」の2ヶ月目、3ヶ月目がどういう事態になっているかわかりません。

[Zuian]

すみません。理解しました。
MonthlySheetシートの作りとしては、以下の形にしようと思っています。今回はとりあえず第一実装として日付とデータがとってこられることを重点にやっていました。
A日付　B(収入+雑益)　C(支出の総合計)　D収入　E雑益　F交通費　G生活費　H雑費　・・・　Pその他費用

N_DBDataでは日付以外のデータを入れ、そのあと新しい配列(「1か月分の配列」とします)の中に、日付、N_DBDataの中身を一つずつ詰め込む想定です。そして、「1か月分の配列」をさらに新しい配列に入れていき、二次元配列とすることで、MonthlySheetにそのまま貼付用としていました。