【GASで時短】フォルダ内のスプレッドシートを結合する

指定したフォルダにあるスプレッドシートをすべて連結するGoogle Apps Scriptです。生徒・受講者の課題をチェックするときに1ファイルずつ開くのは面倒で、1つのスプレッドシート（ブック）にまとめたくなったので作りました。

こんな悩みを解決します

• Googleドライブフォルダに大量のスプレッドシートがあるが、すべて結合したくなった
• 生徒に個別のスプレッドシートで課題を提出してもらったが、1ファイルずつ見るのは大変なので1ブックにまとめたい

用意するもの

特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること

• 指定したGoogleドライブフォルダにあるすべてのスプレッドシートを、今開いているスプレッドシートに結合します。

使い方

1.データの準備

スプレッドシートを用意します。このスプレッドシートにフォルダ内の全てのシートが集まってきます。
現在はシート1しか存在しません。

2.スクリプトの実行

上のメニューにある「スクリプト実行」から「指定フォルダのスプレッドシートをすべて連結する」を実行します。

3.URLの入力

結合したいファイルが保存されているGoogleドライブのURLを入力します。

4.結果の確認

シートがどんどん追加されていきます。
「シート1のコピー」のように中身がわかりづらくなってしまいますが、課題の提出であれば学生の名前をシート名にしておいてもらうなど、事前にユニークになるように案内することで回避できそうです。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00085/index.html
 */

function onOpen() {
  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('指定フォルダのスプレッドシートをすべて連結する', 'concatenate_all_sheets_in_folder');

  // メニューを画面に追加する
  menu.addToUi();
}


function concatenate_all_sheets_in_folder() {

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // 開いている（アクティブな）スプレッドシートオブジェクトを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 結合したいファイルが入っているGoogleドライブのURLを入力してもらう
  const input = ui.prompt('GoogleドライブフォルダのURLを入力してください', ui.ButtonSet.OK_CANCEL);

  // キャンセルが押されたらスクリプトを終了する
  if (input.getSelectedButton() == ui.Button.CANCEL) {
    return;
  }

  // 入力されたURLを取り出す
  const url = input.getResponseText();

  // フォルダURLからID部分を取得する
  const folder_id = url.replace(/^https:.*folders\//, '');

  // フォルダ内のファイルを取得する
  const files = DriveApp.getFolderById(folder_id).getFiles();

  // ファイルが存在する限り、処理を繰り返す
  for (let i = 0; files.hasNext(); i++) {

    let file = files.next(); //ファイルを取得

    // ファイルがスプレッドシートかどうか判断するために、mimetypeを取得
    let mimetype = file.getMimeType();

    // スプレッドシートのmimetypeは「application/vnd.google-apps.spreadsheet」
    if (mimetype.match(/spreadsheet/)) {

      // コピー対象のスプレッドシートを取得
      let ss_copy = SpreadsheetApp.openById(file.getId());

      // スプレッドシート内の全シートを取得
      let sheets_copy = ss_copy.getSheets();

      // シートの数だけ、今開いているスプレッドシートにコピーする
      for (let j = 0; j < sheets_copy.length; j++) {

        sheets_copy[j].copyTo(ss);

      }
    }
  }
}

関連するアプリ