コピー＆ペーストの繰り返しから脱却！ GASでバラバラのデータを1箇所に集約：働く価値を上げる“VBA／GAS”術（8）（3/3 ページ）

[高橋宣成，プランノーツ]

経費データを収集するスクリプト

　スクリプト「colectData()」の処理について解説します。処理の流れは以下のようになっています。

1. アクティブシート「経費収集」の取得
2. フォルダ「経費精算書」内のファイルをコレクションとして取得
3. コレクション内の全てのファイルについて繰り返し処理
   　┣対象となっているファイルを開き、その1番目のシートを取得
   　┣データ「対象月」「部署」「氏名」を変数に格納
   　┗シート上の全ての経費データについて繰り返し処理
   　　┗経費データを「経費収集」シートに追加する

　スクリプトのポイントとなる部分について、以下で解説します。

Googleドライブ内の指定フォルダの取得

　今回の最大のポイントは、Googleドライブ内の指定フォルダの内部にある全てのファイル（スプレッドシート）について、繰り返し処理を行う点です。

　GASではGoogleドライブの操作が「Driveサービス」として提供されていて、グローバルオブジェクト「DriveApp」からさまざまなドライブ操作の機能を利用できます。

　まず、Googleドライブの特定フォルダを取得する必要があります。それには、「フォルダID」を使って取得する「getFolderById()」メソッドがシンプルかつ確実です。以下の記述で、指定したフォルダをFolderオブジェクトとして取得できます。

DriveApp.getFolderById(フォルダID)

　なおフォルダIDは、Googleドライブの全フォルダに振られているIDです。ブラウザでそのフォルダを開いているときのURLの下記部分から取得できます。

https://drive.google.com/drive/u/0/folders/{フォルダID}

図3　GoogleドライブフォルダのURL

ファイルコレクション取得とループ処理

　フォルダに含まれるファイルをコレクションとして取得するには「getFiles()」メソッドを使います。

Folderオブジェクト.getFiles()

　これでファイルのコレクションを取得できます。ここで取得するコレクションは「FileIterator」オブジェクトです。

　このFileIteratorオブジェクトは、ファイルのコレクションの反復処理を実現する機能として、反復処理でまだ取り出していないファイルを取り出す「next()」メソッドと、まだ取り出していないファイルが存在するか判定をする「hasNext()」メソッドが提供されています。

FileIteratorオブジェクト.next()
FileIteratorオブジェクト.hasNext()

　ここで、next()メソッドの戻り値はFileオブジェクト、hasNext()メソッドの戻り値はブール値となります。

　つまり、以下のようにwhile文と組み合わせることで、フォルダ内のファイル全てを繰り返し処理できるようになります。

while (FileIteratorオブジェクト.hasNext()) {
　var file = FileIteratorオブジェクト.next();
　//fileに関する何らかの処理
}

Fileオブジェクトからスプレッドシートを開く

　Fileオブジェクトがスプレッドシートならば、「open()」メソッドで開けます。

SpreadsheetApp.open(Fileオブジェクト)

　また、開いたスプレッドシートからシートを取得する必要あります。今回はスプレッドシートのシート配列として取得する「getSheets()」メソッドを使います。

Spreadsheetオブジェクト.getSheets()

　これで、取得できるものはSheetオブジェクトの配列です。「0」を付与することでインデックス0のシートを取得するようにしています。シートのインデックスは、スプレッドシートの最も左に配置してあるものが0、以降順番に数字が付与されます。

　従って、一番左に別のシートを追加すると、このスクリプトは正しく動作しないため、運用上注意する必要があります。

シートのデータのある最終行の行番号を取得する

　続いて、経費精算書の経費データの各行についてfor文による繰り返し処理を構成します。開始行はどの経費精算書でも「8」で固定ですが、終了行は経費データの数によって異なります。

　そこで、シートのデータがある最終行の行番号を取得する「getLastRow()」メソッドを使います。

Sheetオブジェクト.getLastRow()

　なお、データがある最終列の列番号を取得する「getLastColumn()」メソッドもありますので、覚えておくといいでしょう。

シートにデータ行を追加する

　GASではシートの最終行にデータ行を追加する「appendRow()メソッド」という便利なメソッドがあります。

Sheetオブジェクト.appendRow(配列)

　引数として配列を指定しますが、配列のインデックス順にA列からデータを格納します。つまり、カンマ区切りで並べた順に最終行にデータを追加できます。

プロシージャ全体を確認

　スクリプト「collectData()」の解説は以上です。再度スクリプトの内容を確認してみましょう。

function collectData() { 
　var sheetMain = SpreadsheetApp.getActiveSheet();
　var files = DriveApp.getFolderById('-----フォルダID-----').getFiles();
　
　while (files.hasNext()) {
　　var file = files.next();
　　var sheet = SpreadsheetApp.open(file).getSheets()[0];
　　
　　var month = sheet.getRange("G1").getValue(); //対象月
　　var department = sheet.getRange("G3").getValue(); //部署
　　var fullname = sheet.getRange("G4").getValue(); //氏名
　　
　　for (var i = 8; i <= sheet.getLastRow(); i++) {
　　　sheetMain.appendRow([
　　　　month, //対象月
　　　　sheet.getRange(i, 1).getValue(), //日付
　　　　department, //部署
　　　　fullname, //氏名
　　　　sheet.getRange(i, 2).getValue(), //科目
　　　　sheet.getRange(i, 5).getValue(), //適用
　　　　sheet.getRange(i, 6).getValue(), //金額
　　　　sheet.getRange(i, 7).getValue() //備考
　　　]);
　　}
　}
}

　GASでは収集する対象のスプレッドシートの数やデータの数によっては、Googleが制限をしているスクリプト1回当たりの実行時間の制限（6分以内）に抵触し、そこでスクリプトが停止してしまう可能性があります。そのため、実行時間に注意を払って使いましょう。

著者プロフィール

高橋宣成

プランノーツ 代表取締役

「ITを活用して日本の『働く』の価値を高める」をテーマに、ExcelやVBA、G Suite、Google Apps Script、クラウドなどによる企業または個人事業主向けのシステムおよびツールの開発やコンサルティング、セミナー講師などを務める。「IT×働き方」をテーマに運営するブログ「いつも隣にITのお仕事」は月間60万PV達成。

書籍紹介

ExcelVBAを実務で使い倒す技術

高橋宣成著 秀和システム 1800円（税別）

動くコードが書けたその先、つまり「ExcelVBAを実務で使う」という目的に特化した実践書。ExcelVBAを楽に効果的に使いこなし続けるための知恵と知識、そしてそのためのビジョンと踏み出す勇気を提供する1冊。

詳解！ Google Apps Script完全入門 〜Google Apps & G Suiteの最新プログラミングガイド〜

高橋宣成著 秀和システム 2600円（税別）

Google Apps Scriptの完全入門書として、JavaScriptの基本から自作ライブラリまでを徹底解説。これ一冊だけで基礎から実践まで体系的にマスターできます。