GASは集計が苦手？

自分、あんまりプログラム言語の得意不得意得手不得手には詳しくないんですが、Pythonに比べて集計するための情報があんまりないですよね(あるけどお前が知らんだけって言われちゃうと終わるのでそこは伏せておいて！）

で、ここではJavaScriptの集計関数、メソッドに詳しくなくても基本の集計やピボット化ができるよ、なお話です。

読んでくれると嬉しい対象者

会社でGoogleWorkSpaceを使える環境にある。
プログラムの導入環境がセキュリティ上色々厳しい。
GoogleのLookerStudioを使っているが動作が重くて困っている。
GAS触ったことないからコピペで何かできるか体験してみたい。

じゃあレッツゴー。

とりあえずデータを用意する。

ここに家計調査から引っ張ってきたお米の消費金額データを置いておきます。
家計調査

まずは基本

このデータの中から、以下の条件で集計表を作ります。

全国を除外
米とパンの消費金額を都道府県の平均で集計

※ データ取りやすくするためにスプレッドシートでやっているので便利さが実感ないかもしれませんが、実務的にはcsvファイルを複数取得して結合して集計するなどする便利運用しています。

準備

スプレッドシートに2枚のシートを用意します。
1枚目のシート名を家計調査にして上記リンク先のデータを貼りつける。
2枚目のシート名を集計にする。

で、早速コードは以下な感じ。

function create_Table_Average() {
  
  const sheetName = "家計調査";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(sheetName);
  const allRecords = sh.getDataRange().getValues();
  console.log(allRecords);
  
  //クエリ関数を作っておく。
  const queryFormula = `=query('家計調査'!A:G,"select B,AVG(F) where D != '全国' AND (B contains('米') OR B contains('パン')) group by B label AVG(F) '平均消費金額(円)' format AVG(F) '#,##0' ")`

  //集計先のシートを指定
  const shTargetName = "集計";
  const targetSheet = ss.getSheetByName(shTargetName);
  targetSheet.getRange(1,1).setValue(queryFormula);

  //式を入れっぱなしだと不意に壊れる、関数参照に不都合だから値として入れなおす。

  const setedRecords = targetSheet.getDataRange().getValues();
  targetSheet.getDataRange().setValues(setedRecords);
  

}

どうでしょう？
これでお米とパンの平均消費金額が分かりましたね！

いやいや、米とパン以外の集計したいし。

そらそうよ。
もっと動的に運用したいよね！

なので、ひとまずGASで関数の中に変数を入れられるようにしてみよう！

変数格納する方法は複数ありますが、クエリ関数の本体に' や　" を使うので、${}気泡は結構ややこしくなるため今回は + で対応します。

function createTableAverage() {
  const sheetName = "家計調査";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(sheetName);
  const allRecords = sh.getDataRange().getValues();

  // クエリ関数に入れる消費項目を変数で宣言、格納準備。
  const var01 = "米";
  const var02 = "消費支出";

  // クエリ関数を作っておく。
  //const queryFormula = `=QUERY('家計調査'!A:G, "select B, AVG(F) where D != '全国' AND (B contains ${var01} OR B contains ${var02}) group by B label AVG(F) '平均消費金額(円)' format AVG(F) '#,##0' ")`;
  const queryFormula = `=QUERY('家計調査'!A:G, "select B, AVG(F) where D != '全国' AND (B contains ('` + var01 + `') OR B contains ('` + var02 + `') ) group by B label AVG(F) '平均消費金額(円)' format AVG(F) '#,##0' ")`;


  console.log(queryFormula)

  // 集計先のシートを指定
  const shTargetName = "集計";
  const targetSheet = ss.getSheetByName(shTargetName);
  targetSheet.getRange(1, 1).setValue(queryFormula);

  // 式を入れっぱなしだと不意に壊れる、関数参照に不都合だから値として入れなおす。
  const setedRecords = targetSheet.getDataRange().getValues();
  targetSheet.getDataRange().setValues(setedRecords);
}

こんな感じ。
また次回はコードをいじれない人に動的に抽出方法を提供する方法に踏み込みます。