はじめに
Google Spreadsheet でシート切り替えを行う場合、画面下のタブから切り替える必要があります。
７つや８つ程度であれば、そこから切り替えれば良いですが、２０や３０となると切り替えや並び替えが非常に面倒になります。
その点についてはシートを管理する目次用のシートを作れば解決できるでしょう。
ただ、今度はその目次シートの管理が面倒になります。
本記事はそういった問題を Google App Script で解決することを目的としています。
要件定義
具体的な機能としては
- 目次シートを作る
- 目次シートに目次シート以外のシートのリンクをまとめる。
※リンク化することでシートを別タブで開きやすくなるというメリットもあります。 - シートの追加・更新・削除があれば、それを再反映できるようにする。
- 再反映しても、すでにある目次の並び替えは維持する
です。これらの要件を Google App Script で実装します。
実装
1.目次を追加したい スプレッドシートを開く。
2.スプレッドシートの メニュー＞ツール＞スクリプトエディタ をクリックし、スクリプトエディタを開く。
3.スクリプトエディタに以下のソースコードを貼り付ける。
function replaceToc(){
// 任意の値を入れてください。
var edit_sheet_name = '目次'; // 目次シートの名前
var edit_start_row = 3; // 目次の記述の開始行
var edit_start_col = 3; // 目次の記述の開始列
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //スプレッドシートを取得
// 目次シートがない場合は作り、一番前に挿入する
var edit_sheet = spreadsheet.getSheetByName(edit_sheet_name); //目次シート取得
if (edit_sheet === null) { //ない場合
edit_sheet = spreadsheet.insertSheet(edit_sheet_name, 0); //スプレッドシートの0番目に目次シート生成
}
// 既存の目次が何行目まで入っているか算出する
var plus_start_row = edit_sheet.getLastRow() - edit_start_row + 1;
plus_start_row = plus_start_row <= 0 ? 0 : plus_start_row;
// シート一覧を取得しを目次シートにリンク書き込んでいく 追加は最終行に、更新は上書き、削除・非表示は取り消し線がつく
var sheet_list = spreadsheet.getSheets(); //シート一覧を取得（非表示も含む）
var cell_value_list_list = [];
if (plus_start_row > 0) { // 既存の目次がある場合
cell_value_list_list = edit_sheet.getRange(edit_start_row, edit_start_col, plus_start_row, 1).getFormulas(); // 既存の目次一覧を取得
edit_sheet.getRange(edit_start_row, edit_start_col, plus_start_row, 1).setFontLine('line-through'); // 取り消し線を入れる
}
for (var i in sheet_list) {
var sheet = sheet_list[i];
var sheet_id = sheet.getSheetId();
var sheet_name = sheet.getName();
// 目次一覧に記載するシートを選別する（目次シートと非表示シートは排除）
if (sheet_name !== edit_sheet_name && !sheet.isSheetHidden()) {
var function_str = '=HYPERLINK("#gid='+sheet_id+'","'+sheet_name+'")'; //リンク用関数を生成
var edit_row = plus_start_row + edit_start_row; //最終行を指定する
for (var j in cell_value_list_list) {
var cell_value = cell_value_list_list[j][0];
//既存の目次から正規表現でIDを取り出し、そのIDと一致するものがあれば書き込む行をその行に変更する
var tmp_match_list = cell_value.match(/\#gid=\d*/);
if (tmp_match_list !== null && '#gid='+sheet_id === tmp_match_list[0]) {
plus_start_row--;
edit_row = parseInt(j) + edit_start_row;
break;
}
}
edit_sheet.getRange(edit_row, edit_start_col).setFormula(function_str); //リンクの書き込み
edit_sheet.getRange(edit_row, edit_start_col).setFontLine('none'); // 取り消し線の解除
plus_start_row++;
}
}
}
4.スクリプトエディタの メニュー＞実行＞関数を実行＞replaceToc をクリックし、スクリプトを実行。
※ここで承認が求められる場合があります。
5.実行すると、スプレッドシート側に「目次」というシートができあがります。
目次シートの目次リンク以外の列は編集して大丈夫です。
また作成したあと、シートの追加・削除、シート名の変更を行っても、本スクリプトを実行することで、
- シートの追加：一番下の行に追加したシートのリンクを追加
- シート名の更新：該当リンクの行のリンク名を修正
- シートの削除・非表示：該当リンクの行のリンクに取り消し線
といった処理の反映がされます。
まとめ
基本的にはシートを増やしすぎないように、要件単位でスプレッドシートごとに分けることが理想です。
ただ、どうしても分けられない、多くなってしまう、といったことがあると思います。
そういった場合に本スクリプトを使っていただければと思います。