Google スプレッドシートで、複数シートの情報を自由にレイアウトしたシートを作る

こんにちは、kadotaです。
複数の社員にそれぞれの入力シートを持たせ、そのデータをひとまとめに閲覧できるものが欲しい、という簡単な社内ツール的なものをこしらえることがあったのでそのお話です。
Googleスプレッドシートと Google Apps Script (GAS) を使います。

入力側を作る

これは単純で、テンプレートとなるスプレッドシートを用意し、それをコピーして対象社員に配ります。
編集権限を個別に与えて、それぞれの社員のみが各自のシートに入力できる形にしています。
（実際はGASで生成用シートを元に複製させてますが今回は細かい説明を割愛します）

閲覧側を作る

閲覧側では、複数の入力シートの情報をまとめて扱う必要があります。
トリガーを使って一定時間毎にGASでアクセスしてデータを集約することもできますが、今回はGoogleスプレッドシートの IMPORTRANGE 関数を使うことにしました。

IMPORTRANGE("シートURL", "シート名!範囲指定")

入力シートは100枚未満の数だったので IMPORTRANGE関数でも特に問題はありませんでした。
トリガーで一定時間で処理させると更新までのタイムラグが発生しますが、IMPORTRANGE ならほぼリアルタイムに反映できるのも利点です。

閲覧シートに入力シートを IMPORTRANGE で紐付ける

入力シートの数の分、IMPORTRANGE関数を仕込んだシートを閲覧シートにも用意する必要があります。
数が多いと手作業では面倒なので、次のような形でGASでシートを挿入しつつ左上のセルに IMPORTRANGE を仕込みます。 ※ここに出てくる sheets は、シート名に振るIDと、GoogleドライブのFileオブジェクトを格納した配列です。

function insertSheets(sheets) {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  sheets.sort((a, b) => a.id - b.id)
  sheets.forEach( v => {
    ss.insertSheet('入力シート' + v.id).getRange(1, 1).setValue('=IMPORTRANGE("'+ v.file.getUrl() +'", "入力シート!A2:K10")')
  })
}

入力シートの内容を1つのデータシートにまとめる

上記の状態ですでに入力シートの内容を閲覧シート側でアクセスできる状態にはなっていますが、入力シートをひとまとめにした中間シートを作っておくことにします。

入力シート(複数) → 中間シート(1つ) → 閲覧シート(1つ)

閲覧用の画面を作る時に

各シート名
シート内の入力セルの位置

をそれぞれ扱いながら埋めていくのが面倒そうなので、指定を楽に単純にしたかったためです。
（入力シートはいわゆる帳票的な作りで、データ列のみが綺麗に並ぶようなものではないです）

この中間シートで、先頭のID列に続いてそれ以降の列にデータが続く…という形にして、各入力シートの内容を1行に落とし込みます。
データ列では INDIRECT関数を使って先頭列のIDを参照しながら指定文字列を組み立てます。※シート名が「入力シート + ID」という前提

=INDIRECT("入力シート"&$A2&"!A2") 、=INDIRECT("入力シート"&$A2&"!F5") など。

こうしておくと中間シートに1行分を仕込んだあとは、以降はコピペで指定を増やせば良くなります。
同一シート内での通常のセル参照なら =$A2 などとしておけばコピペで便利なように絶対・相対指定を入れ込むことができますが、シートが絡む場合はちょっと工夫が必要でした。

中間シートを用意しておくと、閲覧シートでの仕込みが、手作業でやるにしても、GASで自動化するにしても便利になります。

閲覧シートを作成する

あとは閲覧シートを仕上げるだけです。
=中間シート!A2 、 =中間シート!B2 、 =中間シート!C2 … といった感じでセルに指定して、レイアウトを組みます。

ここでも手作業が面倒な場合はGASで自動化してしまうと良いです。下記はその一例です。

  const labels = [['A','B','C']]

  let id = 2
  let formulas = []
  labels.forEach(row => {
    formulas.push( row.map(v => '=中間シート!' + v + id) )
  })

  viewSheet.getRange(row, col, 1, 3).setValues(formulas)

このような処理をID毎に繰り返すようにします。

不要なシートを隠す

こうして閲覧シートが完成したわけですが、シートを他の人に共有する際に、実際に見てほしいシート以外は見せたくない場合があります。
（IMPORTRANGEを仕込んだシートや、中間シートなど）
そんな場合は hideSheet() でシートを隠してしまいます。下記はその一例です。

  ids.forEach((id) => {
    try {
      let sheet = ss.getSheetByName('入力シート' + id)
      sheet.hideSheet()
    }
    catch(e) {
      console.error(e)
    }
  })