Google App Script で集計してグラフをつくるサンプル

例えば、たとえばですけど次のようなデータをもらったとします。

すると、

だいたい次のようにしたいと思います。

• セミコロンで分割して
• 要素をカウントして
• グラフをつくる

上のサンプルはカラムが3つくらいですが、僕がもらったデータは膨大で、一個一個つくるのが大変そうでしたので、Google App Scriptを用いることで、【ボタンをクリックしたら自動で、シート作成&グラフ表示】できるようにしてみました。

早すぎて、よくわからないと思いますが。

1. [処理メニュー] > [集計]をクリック
2. シートが３つ自動でできる
3. 各シートごとに文字列を分割して集計してグラフを設置

という流れで進んでいます。

以下のソースをスクリプトに設置すると動きます。
※急ぎでつくったので、ソースは改善の余地が多いと思います。

analytics.gs

/*
メニューに追加
*/
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menu = [
    {name: "集計", functionName: "creatSheet"}
  ];
  ss.addMenu("処理メニュー", menu);
}


/*
分析対象のカラム別にシートを作成。さらにグラフ作成
*/
function creatSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastColumn = sheet.getLastColumn();
  var lastRow = sheet.getLastRow();

  // 対象のカラムを配列で持ちたい.
  var columnNames = [
    '好きな食べ物',
    '嫌いな食べ物'
  ];

  // 対象カラムのcolumnを取得する。
  var targetColumn = 0;  
  for (i = 1; i <= lastColumn; i++) {
    var range = sheet.getRange(1, i);
    var columnName = range.getValue();    

    var exists = columnNames.indexOf(columnName);

    if (exists >= 0) {
      Logger.log(i);
      Logger.log(range.getValue());

      targetRange = sheet.getRange(1, i, lastRow, 1);
      var values = targetRange.getValues();

      // 存在しなければ挿入
      if (!ss.getSheetByName(columnName)) {
        ss.insertSheet(columnName);
        targetSheet = ss.getSheetByName(columnName);  
        for (j = 0; j < values.length; j++) {  
          var range = targetSheet.getRange(j + 1, 1);
          range.setValue(values[j]);    
        }
        createGraphBySheetName(columnName);
      }
    }
  }  
}


/*
グラフ作成
*/
function createGraphBySheetName(sheetName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  var outputArr = {}
  var outputKeyColumn = 4;
  var outputValueColumn = outputKeyColumn + 1;

  // 集計
  for (i = 2; i <= lastRow; i++) {
    var range = sheet.getRange(i, 1);
    var text = range.getValue();
    var splitArr = text.split(";");

    // スプリットして連想配列にいれる（存在すれば+1、なければ1をセット）
    for (j = 0; j < splitArr.length; j++) {
      var outputKey = splitArr[j];
      if (outputArr[outputKey] == null) {
       outputArr[outputKey] = 1;
      } else {
        outputArr[outputKey]++;
      }
    }  
  }  

  // アウトプットの表示
  var outputKeyRange = sheet.getRange(1, outputKeyColumn);
  var outputValueRange = sheet.getRange(1, outputValueColumn);
  outputKeyRange.setValue("項目");
  outputValueRange.setValue("count");
  var outputLabelRange = sheet.getRange(1, outputKeyColumn, 1, 2);
  outputLabelRange.setBackground("yellow");

  var i = 2;
  for (key in outputArr) {
    var keyRange = sheet.getRange(i, outputKeyColumn);
    var valueRange = sheet.getRange(i, outputValueColumn);
    keyRange.setValue(key);
    valueRange.setValue(outputArr[key]);
    i++;   
  }

  // ソートする
  var height = Object.keys(outputArr).length;
  var sortRange = sheet.getRange(2, outputKeyColumn, height + 1, 2);
  sortRange.sort({column: outputValueColumn, ascending: false});

  // グラフを埋め込む
  var chartRange = sheet.getRange(1, outputKeyColumn, height + 1, 2);
  var chartBuilder = sheet.newChart();
  chartBuilder.addRange(chartRange)
  .setChartType(Charts.ChartType.PIE)
  .setOption('title', 'My Chart!')
  .setPosition(1, outputValueColumn + 1, 20, 20); 
  sheet.insertChart(chartBuilder.build());
}

以上です。ありがとうございました。