今日は以前書いたソースに仕様を追加してみた。
以前「GASを使って複数ファイル複数シートを1つにまとめる方法」というブログを書いた。
stsa.hatenablog.com
その際に書いたプログラムに追加の仕様をおこなった。
追加した仕様としては、複数ファイル複数シートをまとめる際に、A列にスプレッド名、B列にシート名を記載するという機能を追加した。
複数のファイルをまとめるときにどこから持ってきたデータなのか分からなくなるためだ。
実行時に、A列にスプレッド名、B列にシート名を記載するかどうかを確認するようにし、出力しないこともできる。
全てのソースはこちら。
function onOpen() { const ui = SpreadsheetApp.getUi() const menu = ui.createMenu('メニュー'); menu.addItem('複数シートを集計する', 'sumSheetTotalling'); menu.addItem('複数ブックの複数シートを集計する', 'sumBooksSumSheetTotalling'); menu.addToUi(); } function sumSheetTotalling() { let ui = SpreadsheetApp.getUi(); // シートIDの入力 let response = ui.prompt('シートIDを入力してください。', ui.ButtonSet.OK_CANCEL); // キャンセル時はスクリプトを終了する if (response.getSelectedButton() == ui.Button.CANCEL) { return; } // シートIDを取得する let sheetID = response.getResponseText(); //コピー先のスプレッドシート let toSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); //コピー先のシートを新規作成する。シート名は「集計_」+システム日付 let newSheet = toSpreadSheet.insertSheet(); let newSheetName = "集計_" + Utilities.formatDate(new Date(), "Asia/Tokyo", "yyyyMMddhhmmss"); newSheet.setName(newSheetName); //コピー元のスプレッドシート let fromSheet = SpreadsheetApp.openById(sheetID); //コピー元のスプレッドシート let fromSpreadSheet = fromSheet.getSheets(); //コピー元のシート分ループする for (let i = 0; i < fromSpreadSheet.length; i = i + 1) { //シートの内容を取得する let data = fromSpreadSheet[i].getDataRange().getValues(); //取得したデータの先頭行(ヘッダ)を削除する data.shift(); //取得したデータをコピー先シートの最終行以降に書き込む let lastlow = newSheet.getLastRow(); //空の場合を考慮しチェックする if (data.length > 0) { newSheet.getRange(lastlow + 1, 1, data.length, data[0].length).setValues(data); } } } function sumBooksSumSheetTotalling() { let ui = SpreadsheetApp.getUi(); // シートIDの入力 let response = ui.prompt('フォルダーIDを入力してください。', ui.ButtonSet.OK_CANCEL); // キャンセル時はスクリプトを終了する if (response.getSelectedButton() == ui.Button.CANCEL) { return; } // シートIDの入力 let response2 = ui.prompt('出力時A列にスプレッド名、B列にシート名を記載しますか? する:1 しない :1以外', ui.ButtonSet.OK_CANCEL); // キャンセル時はスクリプトを終了する if (response2.getSelectedButton() == ui.Button.CANCEL) { return; } // フォルダーIDを取得する let folderID = response.getResponseText(); //指定フォルダを取得 var folder = DriveApp.getFolderById(folderID); //コピー先のスプレッドシート let toSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); //コピー先のシートを新規作成する。シート名は「集計_」+システム日付 let newSheet = toSpreadSheet.insertSheet(); let newSheetName = "集計_" + Utilities.formatDate(new Date(), "Asia/Tokyo", "yyyyMMddhhmmss"); newSheet.setName(newSheetName); //フォルダ内すべてのファイルを取得 var files = folder.getFiles(); //各ファイルに対して繰り返し while (files.hasNext()) { //ファイルを取得 var file = files.next(); //ファイルIDを取得 var sheetID = file.getId(); //コピー元のスプレッドシート let fromSheet = SpreadsheetApp.openById(sheetID); //コピー元のスプレッドシート let fromSpreadSheet = fromSheet.getSheets(); //コピー元のシート分ループする for (let i = 0; i < fromSpreadSheet.length; i = i + 1) { //シートの内容を取得する let data = fromSpreadSheet[i].getDataRange().getValues(); //スプレッド名を取得する let spreadName = fromSheet.getName(); let spreadNameArray = []; //シート名を取得する let sheetName = fromSpreadSheet[i].getSheetName(); let sheetNameArray = []; for (let i = 0; i < data[0].length; i = i + 1) { //スプレッド名を設定 let tempSpreadNameArray = new Array(); tempSpreadNameArray.push(spreadName); spreadNameArray.push(tempSpreadNameArray); //シート名を設定 let tempSheetNameArray = new Array(); tempSheetNameArray.push(sheetName); sheetNameArray.push(tempSheetNameArray); } //取得したデータをコピー先シートの最終行以降に書き込む let lastlow = newSheet.getLastRow(); //空の場合を考慮しチェックする if (data.length > 0) { if (response2.getResponseText() == '1'){ newSheet.getRange(lastlow + 1, 1, spreadNameArray.length, spreadNameArray[0].length).setValues(spreadNameArray); newSheet.getRange(lastlow + 1, 2, sheetNameArray.length, sheetNameArray[0].length).setValues(sheetNameArray); newSheet.getRange(lastlow + 1, 3, data.length, data[0].length).setValues(data); }else{ newSheet.getRange(lastlow + 1, 1, data.length, data[0].length).setValues(data); } } } } }
GASを使って複数ファイル複数シートを1つにまとめる方法で追加の仕様を実装してみました。