2022/04/17に更新

【GASで時短】全てのシートのデータを1シートにまとめる

スプレッドシート

スプレッドシートの全てのシートのデータを、1つのシートに集約するGoogle Apps Scriptです。各シートで列が揃っていないと使いにくいですが、思いつきで作ってみました。

こんな悩みを解決します


  • 一人1シートに書き込んでもらったが、1シートにまとめたくなった!
  • 過去データを別シートに退避していたが、集計のために1シートにまとめたい!

用意するもの


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

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


  • スプレッドシート内のすべてのシートの値を、1つのシートにまとめます。
  • まとめ先のシートは自動的に生成されます。

使い方


1.スクリプトの実行

サンプルとして、A1からE10にデータが入ったシートを6シート作りました。

上のメニューにある「スクリプト実行」から「全てのシートの値を1シートにまとめる」を実行します。

3.実行結果の確認

「集約シート」が生成され、データがどんどん追記されていきます。見ていてちょっと楽しいかも。
もともとどのシートに存在したデータなのかわかるように、A列にシート名が追加されます。

スクリプト


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

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

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

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

  // メニューに追加するボタン
  menu.addItem('全てのシートの値を1シートにまとめる', 'put_together_all_data');

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

function put_together_all_data() {

  // スプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // データを集約するための新しいシートを作る
  const new_sheet = ss.insertSheet();
  
  // 作ったシートの名前を変更する
  new_sheet.setName('集約シート');

  // 先頭(左端)にシートを移動するためにアクティブなシートにする
  new_sheet.activate();

  // シートを先頭に移動する
  ss.moveActiveSheet(1);


  // 既存のシートを全て取得する
  const sheets = ss.getSheets();

  // 1シートずつ処理していく。左端は集約用のシートなので飛ばす(1スタート)。
  for (let i = 1; i < sheets.length; i++) {

    // シート名を取得する(どのシートのデータがわかるようにするため、シート名をA列に追記する)
    let sheet_name = sheets[i].getName();

    // 既存のシートに存在するデータを全て取得する
    let values = sheets[i].getRange(1, 1, sheets[i].getLastRow(), sheets[i].getLastColumn()).getValues();

    // 取得した既存データのA列にシート名を追加する
    for (let j = 0; j < values.length; j++) {
      values[j].unshift(sheet_name);
    }

    // 集約シートに書き込むため、既存データに応じたrangeオブジェクトを取得する
    let range = new_sheet.getRange(new_sheet.getLastRow() + 1, 1, values.length, values[0].length);

    // 集約シートに書き込みを実行する
    range.setValues(values);

  }
}

関連するアプリ


【GASで便利に】キーワードを含むセル番地を一覧表示する

検索キーワードを全シートから探して、ヒットしたセル番地をまとめて表示するGoogle Apps Scriptです。標準の検索機能ではヒットしたセルが1個ずつ分かりますが、まとめて知りたくなったので作成しました。

【GASで時短】全シートに条件付き書式をセットする

すべてのシートに条件付き書式を適用してくれるGoogle Apps Scriptです。条件付き書式は便利ですが手動で設定するのは面倒なんですよね。

【GASで時短】全シートの条件付き書式を削除する

すべてのシートのすべての条件付き書式を削除するGoogle Apps Scriptです。条件付き書式はシート単位で操作する必要があり、面倒なので作成しました。

【GASで時短】すべてのシートを非表示にする

いま開いているシート以外のすべてのシートを非表示にするGoogle Apps Scriptです。あまり見られたくない作業用シートがたくさんあるときにどうぞ。

【GASで時短】全シートの行・列固定を解除する

全てのシートの行固定、列固定を解除するGoogle Apps Scriptです。ニーズはあんまりなさそうですが、思いつきで作ってみました。

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

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

【GASで時短】すべてのシートを保護する

スプレッドシートの全てのシートを「保護」するGoogle Apps Scriptです。一時的にすべてのシートの更新を止めたい時などにどうぞ。

タグ一覧

最新のアプリ

人気のアプリ

myfunc.jp