Google スプレッドシート x GAS による自動集計 その1：データをコピーする

はじめに

オイシックス・ラ・大地株式会社アドベントカレンダー9日目の記事になります。

[Oisix ra daichi Inc. Advent Calendar 2018][1]
[1]:https://adventar.org/calendars/3112

エンジニアだけでなくデザイナー・人事などのメンバーも書いていて、
幅広い方に楽しんでいけるようなコンテンツになっています。

本記事では、今までプログラミングしたことがない方でもやりやすく、
実務で実行されている方も多いであろう集計作業を自動化する方法の一例をご紹介します。

こんなことやっていませんか？

・複数のExcelファイル・シートから参照orコピペしてデータを集計
・元データが変わったのでExcelのpivotを更新して再集計

→ ExcelをGoogleスプレッドシート(以下、スプレッドシート)に変更、
　GoogleAppsScript(以下、GAS)を設定することで自動で集計できます。

なぜGASを使うか？

・環境構築が簡単(サーバーなど用意する必要がない)
・スケジュール実行などが容易に実施できる(cronなど、専門的な知識も要らない)

実務で実施していること

代理店で運用実施している広告(管理画面共有不可)の配信結果を
日次でスプレッドシートに記入してもらっています。

下記のようなスプレッドシートのデータを
一つのスプレッドシートに集計するためにGASを活用しています。

• スプレッドシートA(代理店A)

  • 媒体A_20XX年01月
  • 媒体A_20XX年02月
  • 媒体A_20XX年03月
  • 媒体B_20XX年01月
  • 媒体B_20XX年02月
  • 媒体B_20XX年03月

• スプレッドシートB(代理店B)

  • 媒体C_20XX年01月
  • 媒体C_20XX年02月
  • 媒体C_20XX年03月
  • 媒体D_20XX年01月
  • 媒体D_20XX年02月
  • 媒体D_20XX年03月

集計の手順

1. 各スプレッドシートからシートをコピーしてくる　← この記事で紹介するのはここまで
2. コピーしてきたデータを集計し、代理店別、全体のサマリーを出力する
3. 集計したデータをもとに自動でグラフを生成する

実装方法(各スプレッドシートからデータをコピーする)

1. 集計用のスプレッドシートを作成し、シート名「CopyFrom」でシートを作成

2. A列に代理店名、B列にスプレッドシートのキーを入力する
   
   キーは、URL https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxx/edit の xxxxxxxxxxxxxxxx の部分です。

3. 「ツール」→「スクリプトエディタ」を開く
   

4. GoogleAppsScriptの画面に遷移するので、下記をコピぺして保存する

data_copy.gas

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ss_list = ss.getSheetByName('CopyFrom');
  var values = ss_list.getSheetValues(1, 1, ss_list.getLastRow(), ss_list.getLastColumn());
  for(var i=0; i < values.length; i++){
    copyValues(values[i][1]);
  }
  sort_sheets(ss);
}

function copyValues(ss_id) {
  var ss_From = SpreadsheetApp.openById(ss_id);
  var ss_To = SpreadsheetApp.getActiveSpreadsheet();
  var numSheets_From = ss_From.getNumSheets();
  var numSheets_To = ss_To.getNumSheets();
  var name_exists = 0;
  for(var i = 1; i < numSheets_From; i++){
    var sheet_From = ss_From.getSheets()[i];
    var name = sheet_From.getSheetName();
    name_exists = 0;
    for(var j = 1; j < numSheets_To;j++){
      if (ss_To.getSheets()[j].getSheetName() == name ){
        name_exists++;
      }
    }
    if (name_exists == 0 ){
      ss_To.insertSheet(name);
    }
    var sheet_To = ss_To.getSheetByName(name);
    var copyValue = sheet_From.getSheetValues(1, 1, sheet_From.getLastRow(), sheet_From.getLastColumn());   
    for(var k = 0; k < copyValue.length; k++){
      for(var l = 0; l < copyValue[k].length; l++){
        sheet_To.getRange(k+1,l+1).setValue(copyValue[k][l]);
      }
    }
  }
}

function sort_sheets(ss){
  var numSheets = ss.getNumSheets();
  var Sheets = ss.getSheets();
  var order = 1;
  for(i=0;i < numSheets; i++){
    var Sheet_name = Sheets[i].getSheetName();
    order = 1;
    for(j=0;j < numSheets; j++){
      if(Sheets[j].getSheetName() < Sheet_name){
        order++;
      }  
    }
    Sheets[i].activate();
    ss.moveActiveSheet(order);
  }
}

これでスプレッドシートを開く(起動する)たびに、最新のデータがコピーされている状態になります。
※毎日決まった時間に実行することも可能です。

コードの解説

追記します