複数の Google スプレッドシートのデータを集約する #GoogleAppsScript

Google スプレッドシート、便利ですよね :-)
で、いろいろと使うようになると、下記のようなケースもあるのではないでしょうか？

営業所ごとに作成されたスプレッドシートのデータをまとめたい
経理ソフトにインポートするため、各社員が作成した経費の申請書から、データを抽出してまとめたい

こういうユースケース、Google Apps Script を使えると、わりと簡単に対応できるんですが、意外と見当たらなかったのでご紹介。

（2016.10.03 追記）コメント欄で、harhogefooさんが一部動作しなくなったコードを修正してくれたうえに、コードとしてまとめてくださいました。ありがとうございます！

仕様

カンタンのため、ざっくり、下記のような前提で…

特定のフォルダの直下に対象となるスプレッドシートがすべて入っている
各スプレッドシートのうち、集計したい対象は最初のシートに限定

また、実際にこういうのを使うのは、総務とか経理とか、それほど ITに詳しくない人かもしれないので、下記のような方針で...

Google Developer Console にログインしないといけないようなワザは使わない。
Google Apps Script を編集しなくても、対象となるスプレッドシートのフォーマット変更に対応できる。

ということで、読み込み対象フォルダやコピーしたい領域、申請者や支店名などの識別子が書かれている場所などを下記のようにスプレッドシートで設定し、Sidebar にカンタンな UI領域を作ることにしました。

下準備

まずは、読み込み対象データを作る下準備から…
「経費精算書」というフォルダを作った上で、下記みたいな経費精算書を２つ作成しておきました。

Sidebar の作り方

カスタムの Sidebar を利用する場合、Google Apps Script の HTML Service を利用します。（下記リンク先では、UI Serviceも使えるように書いてありますが、今はもう利用出来ません。）

https://developers.google.com/apps-script/guides/dialogs?hl=JA#custom_sidebars

まずは、スプレッドシートを新規作成した上で、＜ツール＞＜スクリプトエディタ＞を開きます。デフォルトの名前だとなんとなく寂しいので、名前を付けておくことにします。

*「無題のプロジェクト」をダブルクリックして、プロジェクト名を変更（例：集計テスト）
*「コード.gs」の右の▼をクリックして、ファイル名を変更（例：aggregateData.gs）

続いて、コード内の myFunction を showSidebar と変えて、下記のように書き換えます。

aggregateData.gs

function showSidebar() {
  var ui = HtmlService.createTemplateFromFile('Sidebar')
      .evaluate()
      .setTitle("シート集計");
  SpreadsheetApp.getUi().showSidebar(ui);
}

上記コードでは、Sidebar.html というファイルをテンプレートに HTML Service を生成しているので、Sidebar.html というファイルを追加しましょう。
＜ファイル＞＜新規作成＞＜HTMLファイル＞から、Sidebar.html を作ります。

デフォルトで入っている中身を書き換えて、下記のようにしてみます。
最初にスタイルシートを読み込んで、最後に JavaScript を読み込むようにしてあります。

Sidebar.html

<?!= HtmlService.createHtmlOutputFromFile('Stylesheet').getContent(); ?>

<div class="sidebar branding-below">
  <div class="block" id="sidebar-button-bar">
    <button id="aggregate-data">読み込み開始</button>
  </div>
  <div id="sidebar-info"></div>
  <div id="sidebar-status"></div>
</div>

<?!= HtmlService.createHtmlOutputFromFile('SidebarJS').getContent(); ?>

続いてスタイルシート用のHTMLファイル、＜ファイル＞＜新規作成＞＜HTMLファイル＞から、Stylesheet.html を作ります。Google さんが add-on用に CSSを用意してくれているので、それをベースに使ってみましょう。

https://developers.google.com/apps-script/add-ons/css

Stylesheet.html

<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">

続いて JavaScript用のHTMLファイル、＜ファイル＞＜新規作成＞＜HTMLファイル＞から、SidebarJS.html を作ります。HTMLサービスを利用して生成した HTMLファイルの中に JavaScriptを書いているため、＜スクリプト＞ではなく＜HTMLファイル＞を選ぶことに注意してください。とりあえずは、あとで使う jquery のロードだけ入れておきます。

SidebarJS.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

＜ファイル＞から＜すべてを保存＞を押して、これまでに作成したファイルを保存した上で、showSidebarを実行します。

すると、もとのスプレッドシート上にサイドバーが出現するはずです。

さらに、aggregateData.gs に下記を加えます。この onOpenは、スプレッドシートがオープンされたときに呼ばれる関数で、これを加えておく事により、ファイルがオープンされると自動でサイドバーが表示されるようになります。

aggregateData.gs

function onOpen() {
  showSidebar();
}

「読み込み開始」ボタンから Google Apps Scriptを呼び出す

まずは、「読み込み開始」ボタンを押されたときの処理を追加します。そのためには、まず、Sidebar.html で定義されたボタンに反応するための JavaScript を追加します。

SidebarJS.html を下記のように修正しましょう。

SidebarJS.html

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>

<script>
  $(function() {
    $('#aggregate-data').click(onAggregateDataClick);
  });

  function onAggregateDataClick() {
    this.disabled = true;

    google.script.run
        .withSuccessHandler(
          function(msg, element) {
            showStatus(msg);
            element.disabled = false;
          })
        .withFailureHandler(
          function(msg, element) {
            showStatus(msg, 'error');
            element.disabled = false;
          })
        .withUserObject(this)
        .aggregateData();
  }

  function showStatus(msg, classId) {
    $('#sidebar-status').removeClass().html(msg);
    if (classId) {
      $('#sidebar-status').addClass(classId);
    }
  }

</script>

google.script.run といったあたりがキモですね。
成功時、失敗時のハンドラを登録しつつ、Google Apps Script の aggregateData() という関数を呼び出します。

withUserObject(this) というのをつけることにより、ハンドラを呼び出す際に this （この場合は buttonエレメント）が付加されるので、ハンドラ側でボタンの状態を disabled から戻すことが出来ます。

では、aggregateData.gs に下記を加えてみましょう。

aggregateData.gs

function aggregateData() {
  return("Message from Google Apps Script!");
}

すべてを保存した上で、showSidebar を実行し、スプレッドシートのサイドバーを更新します。　その後、「読み込み開始」ボタンを押すと、サイドバーに Google Apps Script からのメッセージが表示されるはずです。

シート上の設定情報を読み込む

さて、次は、「読み込み開始」ボタンを押されたときに、シート上の設定情報を読み込んでみましょう。

下準備で用意しておいたようなシートを読み込むので、スプレッドシート上に、下記のような項目を入れたうえで、シート名を「集計設定」と変更しておきます。

続いて、スクリプトエディタに戻って、上記の設定情報を読み込みます。下記の getConfig() を加えると同時に、aggregateData() を少し修正しましょう。

aggregateData.gs

function getConfig() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cfgSheet = ss.getSheetByName("集計設定");
  var cfgValues = cfgSheet.getDataRange().getValues();
  var cfg = {};

  for (var i=0; i<cfgValues.length; i++) {
    cfg[cfgValues[i][0]] = String(cfgValues[i][1]);
  }
  return cfg;
}

function aggregateData() {
  var cfg = getConfig();

  return JSON.stringify(cfg);
}

再び、showSidebar を実行した後に、「読み取り開始」ボタンを押すと、シートから読み込んだ設定情報がサイドバーに表示されるはずです。ただし、スプレッドシートにアクセスする関数を利用するため、「承認が必要です」というプロンプトが表示され、シートへのアクセスを許可する必要があります。

ここでは、単純に、「集計設定」というシートを読み込み、Key-Value のようなカタチでそのまま変数に格納しています。

フォルダ内のファイル一覧を取得する

続いて、フォルダ内のファイル一覧を取得し、ファイル名と識別子（申請者）のリストを作ってみましょう。aggregateData.gs を下記みたいに修正します。
今度は、Google Drive にアクセスするので、再び承認を求められます。先ほどと同じように許可してください。

aggregateData.gs

function aggregateData() {
  var cfg = getConfig();

  var targetFolder = DriveApp.getFoldersByName(cfg['集計対象フォルダ']).next();
  var files = targetFolder.getFiles();
  var fileList = [];

  while(files.hasNext()){
    var file = files.next();
    var spreadsheet = SpreadsheetApp.open(file);
    var sheet = spreadsheet.getSheets()[0];
    var id = sheet.getRange(cfg['識別子']).getValue();
    fileList.push( { "fileName": file.getName(), "id": id } );    
  }

  return fileList;
}

フォルダ名から、ターゲットフォルダを特定し、getFilesしています。各ファイルを SpreadSheetとしてオープンし、最初のシートを取得、設定用シートの「識別子」として定義されたセルに書かれたデータ（申請者など）を取得し、fileList に、ファイル名と識別子をセットで保存しています。

これまでは、aggregateDataの返り値を文字列にしていましたが、今回は配列を返しているので、受け取り側も変更する必要があります。

SidebarJS.html

    google.script.run
        .withSuccessHandler(
          function(list, element) {
            showList(list);
            element.disabled = false;
          })

withSuccessHandler の中の showStatus を showList に書き換えます。あわせて showList を追加。

SidebarJS.html

  function showList(list) {  
    if (Array.isArray(list)) {
      var output = "<table>";
      for (var i=0; i<list.length; i++) {
        output += "<tr>";
        for (var j in list[i]) {
          output += "<td>" + list[i][j] + "</td>";
        }
        output += "</tr>";
      }
      output += "</table>";
    } else {
      var output = "Not Array!¥n" + JSON.stringify(list);
    }
    $('#sidebar-info').html(output);
  }

各ファイルのデータ領域を集約する

ここまでくればもう一息！
各スプレッドシートから、識別子（申請者）だけでなく、データも抽出して、出力対象のシートにコピーしましょう。

aggregateData.gs

function aggregateData() {
  var cfg = getConfig();

  var targetFolder = DriveApp.getFoldersByName(cfg['集計対象フォルダ']).next();
  var files = targetFolder.getFiles();

  var outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(cfg['書き込み先シート']);  
  if (! outputSheet) {
    var outputSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(cfg['書き込み先シート']);
  }
  outputSheet.clear();

  var curRow = 1;  
  var fileList = [];
  while(files.hasNext()){
    var file = files.next();

    var spreadsheet = SpreadsheetApp.open(file);
    var sheet = spreadsheet.getSheets()[0];
    var id = sheet.getRange(cfg['識別子']).getValue();
    fileList.push( { "fileName": file.getName(), "id": id } );

    var srcRange = sheet.getRange(cfg['読み込み領域']);
    outputSheet.getRange(curRow, 2, srcRange.getHeight(), srcRange.getWidth())
               .setValues(srcRange.getValues());

    var lastRow = outputSheet.getLastRow()+1;
    outputSheet.getRange(curRow, 1, lastRow-curRow, 1)
               .setValue(id);      // 識別子の書き込み
    curRow = lastRow;
  }

  return fileList;
}

追加したのは、書き込み先シートをオープンして、各ファイルのコピーしたい領域を読み込み、書き込み先シートにセットしている部分だけです。

こんな感じで、実際に実行すると、下記のようなシートを作成してくれます。

最後に

実際にコード書くよりも、このメモを書く方が長くなってしまって、雑な解説になってしまいましたが、多少なりともお役に立てれば幸いです。あと、実際に使う際には、ちょこちょこ、エラー処理を入れておいたほうが良いと思います。