はじめに
前回は、スプレッドシートでGASコードを実行する方法と合計欄を生成するスクリプトについて、お伝えしました。
reiyax.hatenablog.com
今日は、毎日の集計データを自動で加工、グラフ化するGASのコードを紹介します。
データの可視化には、グラフの利用が欠かせません。ただ、逐次変化するデータを監視したいとき、毎回作成するのは面倒です。デフォルトでの作成なら、即完成するかもしれません。ただ、グラフ作成の目的は、「見やすく、分かりやすく」ですから、是非ともコダワリたいものです。
デモ用のデータとして、下記に示した18種類のフルーツの人気投票の結果を使います。架空のデータです。
-1枚目のシート(Original_Data)の1列目に品名、2列目は2日分のデータの集計列とします(SUM関数を貼ります)。毎日の新しいデータを右の列に追加していくことを想定しています。
-2枚目(Processing_Data)は、空白のシートです。
この状態で、円グラフを作ると以下のようになります。
<Before>
第3位のフルーツは何でしょう?・・・ぱっと見、分かりませんよね?
GASのコードを使って、可読性を上げてみましょう。
サンプルコード
下記のコード実行します。
function Pie_Chart() {
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var Original_Data = spreadsheet.getSheetByName('Original_Data');
var data= Original_Data.getRange(2,1,Original_Data.getLastRow(),2).getValues();
var Processing_Data= spreadsheet.getSheetByName('Processing_Data');
if(Processing_Data.getLastColumn()){
Processing_Data.getRange(1,1,Processing_Data.getLastRow(),Processing_Data.getLastColumn()).clear();
}
var data_pie= Processing_Data.getRange(1,1,Original_Data.getLastRow(),2).setValues(data).getValues();
Processing_Data.getRange(1,1,Original_Data.getLastRow()-1,2).sort([{column: 2, ascending: false}]);
data_pie= Processing_Data.getRange(1,1,Processing_Data.getLastRow(),2).getValues();
var Total=Processing_Data.getRange(1,3).setFormulaR1C1("=SUM(Original_Data!R1C2:C2)").getValue();
for (var j=data_pie.length-1;j>=0;j--){
Processing_Data.getRange(j+1,2).setValue(data_pie[j][1]/Total*100);
}
data_pie= Processing_Data.getRange(1,1,Processing_Data.getLastRow(),2).getValues();
for (var i=data_pie.length-1;i>=0;i--){
if(data_pie[i][1]==0){Processing_Data.deleteRow(i+1);}
}
data_pie= Processing_Data.getRange(1,1,Processing_Data.getLastRow(),2);
var Tot_other=0;
var other_num=0;
data_pie= Processing_Data.getRange(1,1,Processing_Data.getLastRow(),2).getValues();
var threshold_val=3;
for (var k=data_pie.length-1;k>=0;k--){
if(data_pie[k][1]<threshold_val){Tot_other+=data_pie[k][1];
other_num+=1;
}
}
data_pie= Processing_Data.getRange(1,1,Processing_Data.getLastRow(),2).getValues();
for (var l=data_pie.length-1;l>=0;l--){
if(data_pie[l][1]<threshold_val){
Processing_Data.deleteRow(l+1);
}
}
data_pie= Processing_Data.getRange(1,1,Processing_Data.getLastRow(),2).getValues();
var array = [threshold_val+"%未満("+other_num+"種)",Tot_other];
data_pie.push(array);
var rows = data_pie.length;
var cols = data_pie[0].length;
Processing_Data.getRange(1,1,rows,cols).setValues(data_pie);
}
コードの処理内容として、
1.Original_Dataから、Processing_Dataへデータをコピー。
(以降の加工処理は、全てProcessing_Dataで行われる。)
2.票の多い順に項目をソートする。
3. 0票の項目を消去する。
4.票数から割合(%)に変換する。
5.一定の割合以下の項目を1カテゴリーにまとめる。(今回は3%に設定)
処理結果
処理が終了するとProcessing_Dataには、以下のように出力されます。
初回のみ、Processing_Dataから手動でグラフを作成します。(セルC1は、総票数でグラフ作成には不要です。)
<After>
如何でしょうか?初期のグラフと比べて、可読性が数段向上したものと思います。
第3位のフルーツは、オレンジでした。
2回目以降は、データを追加し、コードを実行するだけでグラフが自動更新されます。
おわりに
今回は、18項目のデータを使いました。紹介したコードは、項目数を増減しても、そのまま使えますので、是非試してみてください(ズレますので、1行目の見出し行を必ず付けてください)。実際にグラフを作成して、項目数が多いと感じたら、1カテゴリー化機能の閾値(threshold_val)を調節して、項目数を減らしてみましょう。また、1カテゴリー化機能が不要ならば、閾値を0に設定すれば、解除できます。
<1カテゴリー化機能OFF時>