【GAS】GASを使って複数ファイル複数シートを1つにまとめる方法

Googleスプレッドシートって便利で、たくさんのブック(ファイル)を作りさらにその中でたくさんシートを作ってしまいますよね。
で、時々シートを1つにまとめて集計したいって思うことありませんか？
そういう時のために今日はGASを使って複数ファイル複数シートを1つにまとめる方法について書いてみたいと思います。

とは言っても以前書いたこちらをもとに複数ファイルを扱えるようにしただけです。
stsa.hatenablog.com

仕様について説明します。

1つにまとめたいスプレッドシートのフォルダのIDを指定し、実行しているスプレッドシートに新規のシートを作成しそこに集約します。
新規で作られるシート名は集計_システム日付になります。

コードを登録して、スプレッドを開くとメニューというのが一番右にでます。

「複数ブックの複数シートを集計する」をクリックすると処理が始まります。

フォルダーIDを入力します。（最近この形式での入力がマイブーム。）

1つにまとめられ、こんな感じで新しいシートができます。

ソースコードはこんな感じです。
前回の記事の内容も含んだ状態で記載しておきます。

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 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();
            //取得したデータの先頭行（ヘッダ）を削除する
            data.shift();
            //取得したデータをコピー先シートの最終行以降に書き込む
            let lastlow = newSheet.getLastRow();
            //空の場合を考慮しチェックする
            if (data.length > 0) {
                newSheet.getRange(lastlow + 1, 1, data.length, data[0].length).setValues(data);
            }
        }
    }
}

今日はGASを使って複数ファイル複数シートを1つにまとめる方法について書いてみました。

関連記事
stsa.hatenablog.com