【GAS】Google Apps Script 活用事例　特定のシート1枚だけをExcelに変換する超〜便利スクリプト

G suiteの立ち位置は、WindowsとMacのOSシェアの分布に似ているような気がします。ExcelやWord、Power Point、いわゆるOfficeが中心で、spreadsheetはメインストリームではない（憶測ですが）.....そんな気がします。

官公庁向けの許認可が関係してくるビジネスでは、先方の言うことが絶対であり、「提出はExcelで。」と言われたら、かしこまりました。と言う以外にありません。

「Excelだと、GAS使えないじゃん....。」

今まで、GASで自動化してやんよ！！と威勢の良かった僕も、「....Excel....シュン」となる事が多かったのですが、今回、自動化に成功しました。下記のような手順です。

1.　Spreadsheet側で提出段階まで作成する
2.　特定のシート1枚だけをExcelに変換
3.　シートタブのリネーム、シート1を削除
4.　変換し終わったら、spreadsheetを削除する

完成形のスクリプト

function convertToExcel(){
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 var sheet       = spreadsheet.getSheetByName('Excel');
 
 Logger.log(sheet.getName());
 
 //1ヶ月前の日付を生成
 var date      = new Date();
 var month     = date.getMonth()+1;
 date.setMonth(month-2);
 var lastMonth = Utilities.formatDate(date, 'JST', 'YYYY_MM_');
 var fiileName = lastMonth + 'ファイル名';
 
 /*GASからDrive APIを利用するためには下記2点の設定が必要です。
 プロジェクトを開いて、上部の　「リソース」 - 「Googleの拡張サービス」　 - 「Drive API」をONにする。
 Googleデベロッパーコンソールのライブラリにて　「Google Drive API」　を検索して有効にする。*/
 
 var newSpreadsheet = Drive.Files.insert({
   "title":   fiileName,
   "mimeType": "application/vnd.google-apps.spreadsheet",
   "parents":  [{"id": '******************'}]
 });
 var ssId  = SpreadsheetApp.openById(newSpreadsheet.id);
 var getId = ssId.getId();
 Logger.log(ssId.getName());
 
 /*現在のSpreadsheetから新規に作成したシートに特定のシートをコピー*/
 sheet.copyTo(ssId);
 
 /*名前の変更と余計なシートの削除*/
 var targetSheet  = ssId.getSheetByName('Excel のコピー');
 targetSheet.setName('Excel');
 var deleteSheet  = ssId.getSheets();
 ssId.deleteSheet(deleteSheet[0]);
 
 /*excelに変換後の用済みファイルをDriveから消す*/
 var fldId     = '*************'; // ブランクの場合はマイドライブ直下に
 var fetchUrl  = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' + getId + '&exportFormat=xlsx';
 var fetchOpt  = {'headers' : { Authorization: 'Bearer ' + ScriptApp.getOAuthToken()},
                  'muteHttpExceptions' : true
                 };
                 
 var file      = UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName(fiileName);
 DriveApp.getFolderById(fldId).createFile(file);
 DriveApp.getFolderById(fldId).removeFile(DriveApp.getFileById(getId));
}

Google Apps Scriptで指定したフォルダへ直接スプレッドシートを作成する - Qiita

今回、作成する際に参考になったのが、上記のブログです。

var newSpreadsheet = Drive.Files.insert({
   "title":   fiileName,
   "mimeType": "application/vnd.google-apps.spreadsheet",
   "parents":  [{"id": '******************'}]
 });

この部分は、モロパクリです。詳しい事は上記ブログに書いてあるのですが、DriveAppで指定のフォルダに新しいspreadsheetを作る事が、なぜか出来ないため、高度なDrive APIを使います。その際は設定が必要。

シートを特定のspreadsheet（ファイル内）にコピーする

/*現在のSpreadsheetから新規に作成したシートに特定のシートをコピー*/
sheet.copyTo(ssId);

このたった一行だけで、新しく作成したspreadsheetに特定のシートをコピーする事が出来てしまいます。

一番左のシートを削除する

/*名前の変更と余計なシートの削除*/
var targetSheet  = ssId.getSheetByName('Excel のコピー');
targetSheet.setName('Excel');
var deleteSheet  = ssId.getSheets();
ssId.deleteSheet(deleteSheet[0]);

getSheets()メソッドは、全てのシートを配列で取得する事が出来ます。

ちなみに一番右のシートを取得するには？

var anotherSpreadsheet = SpreadsheetApp.openById('*********');
var getSheetsLength    = anotherSpreadsheet.getSheets().length;
Logger.log(getSheetsLength);
 
/*シートの作成場所の指定　一番右に配置、シート40などの名前でシートを取得*/
var newSheet     = anotherSpreadsheet.insertSheet(getSheetsLength);
var newSheetName = newSheet.getName();
Logger.log(newSheetName);

getSheets().length　で、シートの枚数が返ってきます。例えば、5枚シートがあるとすると、5と返ってきます。anotherSpreadsheet.insertSheet(getSheetsLength)　これでシートの右側に新規シートを作成する事が出来ます。getSheets()[配列番号]でする場合は、配列番号が、0から始まるので、-1する事が必要かと思います。

こちらも月次集計処理で使用しているので、後日、記事にしようかなと思っています。

GASで操作するスプレッドシートのワークシートを指定する３つの方法

ここまでの処理で、特定のシート1枚だけになったspreadsheetをExcelに変換します。変換し終えたspreadsheetは、要らないので削除します。

var file      = UrlFetchApp.fetch(fetchUrl, fetchOpt).getBlob().setName(fiileName);
DriveApp.getFolderById(fldId).createFile(file);
DriveApp.getFolderById(fldId).removeFile(DriveApp.getFileById(getId));