GASで複数ファイルを簡単にまとめたい！

Contents

はじめに
1.sampleデータ
- スプレッドシートのファイル名
- スプレッドシート内のsampleデータ
2.GASコード

はじめに

令和第一号記事がだいぶ遅くなってしまいましたが、令和初の投稿です！まず、GoogleDriveに日次でシステムからスプレッドシートレポートが出力されている場合、そのデータを1つのファイルにまとめたい！というシーンで使えるコードを紹介します。Google Apps Script (GAS) を使って実装して、それをSlackやChatworkにレポートとして配信するのもいいですね。

それでは早速始めていきます！

1.sampleデータ

POINT

サービスはGoogleDrive、スプレッドシート、GASを使って実装していきます。
前提条件として以下が必要です。
– GoogleDriveには日時でデータが上がっていること。
– スプレッドシートのファイル名は日付を入れること。
– 同一フォルダ内にファイルが存在していること。

スプレッドシートのファイル名

上記条件を満たすデータサンプルは以下のようなデータを指します。
ファイル名にそれぞれ出力した日付を入れてます。

スプレッドシート内のsampleデータ

スプレッドシートのデータは以下のようなデータを指します。IDと顧客名等をサンプルで入力しているデータです。

2.GASコード

POINT

今回のコードは一部愚直なコードもありますが、別フォルダにあるスプレッドシートを一つずつ呼び出してそれらを一つのシートにまとめることが簡単にできます。

コードは大きく2つの関数で出来ています。コードを確認してもらえばわかりますが、コメント多めに書いてます。

DriveからスプレッドシートIDを取得

まず、var Folder = DriveApp.getFolderById(‘**************’); はGoogleDriveのフォルダパスをセットします。以下枠線の箇所に表示されているパスを指定します。

検索するファイル

Driveにあるデータを全て参照したのち、一致するデータをここで指定して一つのスプレッドシートにまとめます。この時、コード上は月初から今日までのファイルを探す。という実装をしているのでfrom.setDate(1); で月初（当月）を取得するが、期間を指定したい場合はsetDateの引数を0や-1に変更すれば指定した期間の該当ファイル全てを取得することが出来ます。

GASコード

//シート名
var dailySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Daily_Report');

//処理するファイルの日付を取得
var from = new Date(); //開始日を取得する（比較する為、取得した表記はこのまま残す）
var end = new Date(); //終了日を取得する（比較する為、取得した表記はこのまま残す）
from.setDate(1); //月初（当月）を取得するが、期間を指定したい場合はここを変更する

//-------------------------------------------------------------------------------------------

function getFolderIdList() {
 //
 var folder = DriveApp.getFolderById('***********************'); //フォルダパスを指定
 var serchId = folder.getId();
 var files = DriveApp.searchFiles(
 "mimeType = 'application/vnd.google-apps.spreadsheet' and '"+serchId+"' in parents and trashed = false"
 );
 
 var filelist = new Array();
 for(var i = 0; files.hasNext(); i++){
   var fileArray = files.next(); //ファイル名を取得（fileArray）
   var tmp = new Array();
   tmp.push(fileArray.getId());
   tmp.push(fileArray.getName());
   filelist.push(tmp);　//ファイルIDを取得（tmp）
   Logger.log(fileArray); //取得したファイル名をlogで表示させたい場合使う
 }

 var cnt = 1; //データを渡した時の処理を分岐させるため
 while(from <= end){ //fromが1日でend（今日のファイル）を取得するまで繰り返す
   
  //月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう）
   fromS = (Utilities.formatDate( from, 'Asia/Tokyo', 'yyyyMMdd')); //開始日の表示形式をファイル名のため整える
   endE = (Utilities.formatDate( end, 'Asia/Tokyo', 'yyyyMMdd')); //終了日の表示形式をファイル名のため整える
   
   var dailyS = fromS + '-発注管理'; //検索したいファイル名を取得してこの後探す
   var dailyE = endE + '-発注管理'; //検索したいファイル名を取得してこの後探す
   var last = filelist.length; //array(ファイル)の要素数を取得
   
   //yyyymmdd-tcpay-usageの本日のファイルを探す
   for (var i = 0; i < last; i++) {           
     if (dailyS === filelist[i][1]) {
      var dailyId = filelist[i][0];
     }
   }
    
   //別Driveにあるスプレッドシートデータを開いて、取得した結果をreturnさせる
   if (dailyId !== undefined) {
     if(dailyId !== ''){
       var result = getData(dailyId,cnt);
       cnt = cnt + 1; //データを渡した時の処理を分岐させるため
       var rows = result.length;
       var reqCnt = dailySheet.getLastRow();
       if (reqCnt === 0) {
         reqCnt = 1;
       }
       dailySheet.getRange(1 + reqCnt,1,rows,4).setValues(result);
     }
   }
    
   from.setDate(from.getDate()+1);
   var dailyId = '';//取得、保持しているシートIDを初期化
 }
}

//シート名

var dailySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Daily_Report');

//処理するファイルの日付を取得

var from = new Date(); //開始日を取得する（比較する為、取得した表記はこのまま残す）

var end = new Date(); //終了日を取得する（比較する為、取得した表記はこのまま残す）

from.setDate(1); //月初（当月）を取得するが、期間を指定したい場合はここを変更する

//-------------------------------------------------------------------------------------------

function getFolderIdList() {

var folder = DriveApp.getFolderById('***********************'); //フォルダパスを指定

var serchId = folder.getId();

var files = DriveApp.searchFiles(

"mimeType = 'application/vnd.google-apps.spreadsheet' and '"+serchId+"' in parents and trashed = false"

);

var filelist = new Array();

for(var i = 0; files.hasNext(); i++){

var fileArray = files.next(); //ファイル名を取得（fileArray）

var tmp = new Array();

tmp.push(fileArray.getId());

tmp.push(fileArray.getName());

filelist.push(tmp);　//ファイルIDを取得（tmp）

Logger.log(fileArray); //取得したファイル名をlogで表示させたい場合使う

}

var cnt = 1; //データを渡した時の処理を分岐させるため

while(from <= end){ //fromが1日でend（今日のファイル）を取得するまで繰り返す

//月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう）

fromS = (Utilities.formatDate( from, 'Asia/Tokyo', 'yyyyMMdd')); //開始日の表示形式をファイル名のため整える

endE = (Utilities.formatDate( end, 'Asia/Tokyo', 'yyyyMMdd')); //終了日の表示形式をファイル名のため整える

var dailyS = fromS + '-発注管理'; //検索したいファイル名を取得してこの後探す

var dailyE = endE + '-発注管理'; //検索したいファイル名を取得してこの後探す

var last = filelist.length; //array(ファイル)の要素数を取得

//yyyymmdd-tcpay-usageの本日のファイルを探す

for (var i = 0; i < last; i++) {

if (dailyS === filelist[i][1]) {

var dailyId = filelist[i][0];

}

//別Driveにあるスプレッドシートデータを開いて、取得した結果をreturnさせる

if (dailyId !== undefined) {

if(dailyId !== ''){

var result = getData(dailyId,cnt);

cnt = cnt + 1; //データを渡した時の処理を分岐させるため

var rows = result.length;

var reqCnt = dailySheet.getLastRow();

if (reqCnt === 0) {

reqCnt = 1;

}

dailySheet.getRange(1 + reqCnt,1,rows,4).setValues(result);

}

from.setDate(from.getDate()+1);

var dailyId = '';//取得、保持しているシートIDを初期化

}

スプレッドシートIDからデータを取得

受け取った引数のデータ（スプレッドシートID）を使ってスプレッドシート上のデータを取得してreturnでもう一度呼び出し元関数に戻します。

GASコード

function getData(daily,count) {
 var spreadsheet = SpreadsheetApp.openById(daily);
 var sheet = spreadsheet.getActiveSheet();
 var requestCnt = sheet.getLastRow(); //最終行を取得
  
 if (count === 1){
   var requestVal = sheet.getRange('A1:D' + requestCnt).getValues(); //1回目はヘッダまで取得
 }else{
   var requestVal = sheet.getRange('A2:D' + requestCnt).getValues(); //2回目はデータのみ取得    
 }
 return requestVal;
}

function getData(daily,count) {

var spreadsheet = SpreadsheetApp.openById(daily);

var sheet = spreadsheet.getActiveSheet();

var requestCnt = sheet.getLastRow(); //最終行を取得

if (count === 1){

var requestVal = sheet.getRange('A1:D' + requestCnt).getValues(); //1回目はヘッダまで取得

}else{

var requestVal = sheet.getRange('A2:D' + requestCnt).getValues(); //2回目はデータのみ取得

}

return requestVal;

}

全コード

「DriveからスプレッドシートIDを取得」と「スプレッドシートIDからデータを取得」の両コードを合わせたものです。

GASコード

//シート名
var dailySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Daily_Report');

//処理するファイルの日付を取得
var from = new Date(); //開始日を取得する（比較する為、取得した表記はこのまま残す）
var end = new Date(); //終了日を取得する（比較する為、取得した表記はこのまま残す）
from.setDate(1); //月初（当月）を取得するが、期間を指定したい場合はここを変更する

//-------------------------------------------------------------------------------------------

function getFolderIdList() {
 //
 var folder = DriveApp.getFolderById('***********************'); //フォルダパスを指定
 var serchId = folder.getId();
 var files = DriveApp.searchFiles(
 "mimeType = 'application/vnd.google-apps.spreadsheet' and '"+serchId+"' in parents and trashed = false"
 );
 
 var filelist = new Array();
 for(var i = 0; files.hasNext(); i++){
   var fileArray = files.next(); //ファイル名を取得（fileArray）
   var tmp = new Array();
   tmp.push(fileArray.getId());
   tmp.push(fileArray.getName());
   filelist.push(tmp);　//ファイルIDを取得（tmp）
   Logger.log(fileArray); //取得したファイル名をlogで表示させたい場合使う
 }

 var cnt = 1; //データを渡した時の処理を分岐させるため
 while(from <= end){ //fromが1日でend（今日のファイル）を取得するまで繰り返す
   
  //月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう）
   fromS = (Utilities.formatDate( from, 'Asia/Tokyo', 'yyyyMMdd')); //開始日の表示形式をファイル名のため整える
   endE = (Utilities.formatDate( end, 'Asia/Tokyo', 'yyyyMMdd')); //終了日の表示形式をファイル名のため整える
   
   var dailyS = fromS + '-発注管理'; //検索したいファイル名を取得してこの後探す
   var dailyE = endE + '-発注管理'; //検索したいファイル名を取得してこの後探す
   var last = filelist.length; //array(ファイル)の要素数を取得
   
   //yyyymmdd-tcpay-usageの本日のファイルを探す
   for (var i = 0; i < last; i++) {           
     if (dailyS === filelist[i][1]) {
      var dailyId = filelist[i][0];
     }
   }
    
   //別Driveにあるスプレッドシートデータを開いて、取得した結果をreturnさせる
   if (dailyId !== undefined) {
     if(dailyId !== ''){
       var result = getData(dailyId,cnt);
       cnt = cnt + 1; //データを渡した時の処理を分岐させるため
       var rows = result.length;
       var reqCnt = dailySheet.getLastRow();
       if (reqCnt === 0) {
         reqCnt = 1;
       }
       dailySheet.getRange(1 + reqCnt,1,rows,4).setValues(result);
     }
   }
    
   from.setDate(from.getDate()+1);
   var dailyId = '';//取得、保持しているシートIDを初期化
 }
}

function getData(daily,count) {
 var spreadsheet = SpreadsheetApp.openById(daily);
 var sheet = spreadsheet.getActiveSheet();
 var requestCnt = sheet.getLastRow(); //最終行を取得
  
 if (count === 1){
   var requestVal = sheet.getRange('A1:D' + requestCnt).getValues(); //1回目はヘッダまで取得
 }else{
   var requestVal = sheet.getRange('A2:D' + requestCnt).getValues(); //2回目はデータのみ取得    
 }
 return requestVal;
}

//シート名

var dailySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Daily_Report');

//処理するファイルの日付を取得

var from = new Date(); //開始日を取得する（比較する為、取得した表記はこのまま残す）

var end = new Date(); //終了日を取得する（比較する為、取得した表記はこのまま残す）

from.setDate(1); //月初（当月）を取得するが、期間を指定したい場合はここを変更する

//-------------------------------------------------------------------------------------------

function getFolderIdList() {

var folder = DriveApp.getFolderById('***********************'); //フォルダパスを指定

var serchId = folder.getId();

var files = DriveApp.searchFiles(

"mimeType = 'application/vnd.google-apps.spreadsheet' and '"+serchId+"' in parents and trashed = false"

);

var filelist = new Array();

for(var i = 0; files.hasNext(); i++){

var fileArray = files.next(); //ファイル名を取得（fileArray）

var tmp = new Array();

tmp.push(fileArray.getId());

tmp.push(fileArray.getName());

filelist.push(tmp);　//ファイルIDを取得（tmp）

Logger.log(fileArray); //取得したファイル名をlogで表示させたい場合使う

}

var cnt = 1; //データを渡した時の処理を分岐させるため

while(from <= end){ //fromが1日でend（今日のファイル）を取得するまで繰り返す

//月を指定するときは必ず大文字のMMを指定します（半角mmだとminutesを指してしまう）

fromS = (Utilities.formatDate( from, 'Asia/Tokyo', 'yyyyMMdd')); //開始日の表示形式をファイル名のため整える

endE = (Utilities.formatDate( end, 'Asia/Tokyo', 'yyyyMMdd')); //終了日の表示形式をファイル名のため整える

var dailyS = fromS + '-発注管理'; //検索したいファイル名を取得してこの後探す

var dailyE = endE + '-発注管理'; //検索したいファイル名を取得してこの後探す

var last = filelist.length; //array(ファイル)の要素数を取得

//yyyymmdd-tcpay-usageの本日のファイルを探す

for (var i = 0; i < last; i++) {

if (dailyS === filelist[i][1]) {

var dailyId = filelist[i][0];

}

//別Driveにあるスプレッドシートデータを開いて、取得した結果をreturnさせる

if (dailyId !== undefined) {

if(dailyId !== ''){

var result = getData(dailyId,cnt);

cnt = cnt + 1; //データを渡した時の処理を分岐させるため

var rows = result.length;

var reqCnt = dailySheet.getLastRow();

if (reqCnt === 0) {

reqCnt = 1;

}

dailySheet.getRange(1 + reqCnt,1,rows,4).setValues(result);

}

from.setDate(from.getDate()+1);

var dailyId = '';//取得、保持しているシートIDを初期化

}

function getData(daily,count) {

var spreadsheet = SpreadsheetApp.openById(daily);

var sheet = spreadsheet.getActiveSheet();

var requestCnt = sheet.getLastRow(); //最終行を取得

if (count === 1){

var requestVal = sheet.getRange('A1:D' + requestCnt).getValues(); //1回目はヘッダまで取得

}else{

var requestVal = sheet.getRange('A2:D' + requestCnt).getValues(); //2回目はデータのみ取得

}

return requestVal;

}

var requestVal = sheet.getRange(‘A1:D’ + requestCnt).getValues(); の「D」や、dailySheet.getRange(1 + reqCnt,1,rows,4).setValues(result);の「4」などで記載している列の指定コードが一部イケてないコードです。データサイズを鑑みて変更はしてください。可変でできるようにする記事は今後の課題として直したらアップデートかけます！

GASログ

[19-05-16 02:00:38:446 JST] 20190514-発注管理
[19-05-16 02:00:38:448 JST] 20190513-発注管理
[19-05-16 02:00:38:449 JST] 20190512-発注管理
[19-05-16 02:00:38:450 JST] 20190511-発注管理
[19-05-16 02:00:38:451 JST] 20190510-発注管理