【GAS】データ加工とグラフ作成を自動化【スプレッドシート】

はじめに

前回は、スプレッドシートで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();
  
  //コピーを加工用シート（Processing_Data）にペースト。
  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();
  
  //0%を削除
  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();
  
  //3%未満のデータを１カテゴリーにまとめる。
  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(); 
  
  //3%未満の元データ削除
   for (var l=data_pie.length-1;l>=0;l--){
     if(data_pie[l][1]<threshold_val){
       Processing_Data.deleteRow(l+1);
     }
   }
  
  // 一番下の行に3%未満のカテゴリーを追加。
  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カテゴリー化機能が不要ならば、閾値を０に設定すれば、解除できます。

 <1カテゴリー化機能OFF時>

【GAS】すぐに使えるコード付き！スプレッドシートに集計行を挿入する方法。

はじめに

スプレッドシートの集計を行う方法で、ぱっと思いつくのは、シートの機能であるSUM関数を使う方法だと思います。ただ、SUM関数を手入力するのも面倒ですよね。それが毎日のルーチンワークなら尚更です。Google Apps Script（GAS）を使って自動化しましょう。

スプレッドシート

シート１の1行目、2行目に適当な数値を入力しておき、下記のコードを実行してみましょう（行数、列数は、いくらあっても構いませんが、1行目から数値を入れて下さい）。

スクリプトの実行方法

1．デモ用のスプレッドシートを用意します。

2．スプレッドシートのメニューバーにある［ツール］→［スクリプトエディタ］で、スクリプトエディタを起動します。

2．function myFunction() {}とありますが消去して、代わりにサンプルコードをコピペします。
3．フロッピーマークをクリックして、スクリプトを保存します。スクリプトに名前を付けるよう求められますが、適当で結構です。

４．2個右にある▶（実行ボタン）をクリックします。

５．初回のみ、「承認が必要です」とポップアップメニューが出るので、「許可を確認」をクリックし、ご自身のログインアカウントを選択します。

６．「このアプリは確認されていません。」と不安を煽るページが現れますが、左側の「詳細」をクリックします。

７．「（安全ではないページ）に移動」をクリックします。

（5-7は、初回実行時のみ必要な操作です。）

3行目に1，2行目の合計が入っていれば、成功です。

サンプルコード１

・GASを使って、最終行の下にSUM関数に埋め込むコード。

function myFunction() {
var spreadsheet=SpreadsheetApp.getActiveSpreadsheet();
var sheet=spreadsheet.getSheetByName("シート1");　//”シート1”は、集計したいシートの名前に変える。

var formulas=[];
for(i=0; i<sheet.getLastColumn(); i++){
　formulas[i]= "=SUM(R1C:R[-1]C)";　//1行目が見出し行の場合は、R1CからR2Cに変える。
}
sheet.getRange(sheet.getLastRow()+1,1,1,sheet.getLastColumn()).setFormulasR1C1([formulas]);
　　//1列目が見出し列の場合は、(sheet.getLastRow()+1,2,1,sheet.getLastColumn()-1)に変える。
}

行数、列数に拘わらず、コピペで使えるよう凡庸性を持たせていますが、見出し列、見出し行がある場合は、コメントに従って調節してください。

サンプルコード２

・SUM関数を使わずにGASに直接集計させるコード。

function myFunction2(){
  var spreadsheet=SpreadsheetApp.getActiveSpreadsheet();
  var sheet=spreadsheet.getSheetByName("シート1");
  
  var Row_Tot=[];
  for (var h=0;h<sheet.getLastColumn();h++){          //列数分の0で配列を埋めておく。
    Row_Tot[h]=0;
  }
 var Row=sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
 for (var i=0;i<sheet.getLastColumn();i++){　  //1行目から順にRow_Totに足していく。 
   for (var j=0;j<Row.length;j++){
      Row_Tot[i]+=Row[j][i];
    }
  }
  sheet.getRange(sheet.getLastRow()+1,1,1,sheet.getLastColumn()).setValues([Row_Tot]);
}

おわりに

今回は、簡単な合計欄の作成を題材に、スプレッドシートでのスクリプトの実行方法をお伝えしました。今後もこのブログで、すぐに使える便利なスクリプトコードをお届けしたいと考えています。上記の流れで実行していただくことを想定しています。

合計欄を作るスクリプトを2通り紹介しました。計算結果は両者同じですが、計算時間は、前者の方がほんの一瞬だけ短い気がします。今回のように場合分けなど複雑な処理を必要とせず、かつ便利な組み込み関数がある場合は、それを使った方がシンプルなコードが書けますね。また、組み込み関数をSUMから、AVERAGEに変更すれば、合計値から平均値に変更できますし、いろいろな応用が可能です。