【GAS】Googleスプレッドシートの目次シートを作成する方法
Google Apps Script(GAS)を使用して、スプレッドシートの全シート名を記載した目次シートを作成します。目次シートのシート名をクリックすると対象シートに移動するハイパーリンクも設定します。
目次シートがあれば、スプレッドシートのシート一覧から目的のシートにすばやく移動できます。
目次シートの作成
GASを実行して目次シートを作成します。
GASエディタ画面を開く
スプレッドシートのメニューから「拡張機能」>「Apps Script」をクリックして、GASエディタ画面を開きます。
GASの実装
以下のコードを実装します。
function createIndexSheet() {
// 目次シート名
const SHEET_NAME = '目次'
// 目次書き込み開始セル
const START_CELL = 'B2'
// 目次シートを取得
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var index = spreadsheet.getSheetByName(SHEET_NAME)
if(index == null)
{
// 目次シートを新規作成して先頭に移動
index = spreadsheet.insertSheet(SHEET_NAME).activate();
spreadsheet.moveActiveSheet(0)
}
else
{
// 目次シートの内容をクリア
index.clearContents();
}
// 目次書き込み開始セルの行と列を取得
var row = index.getRange(START_CELL).getRow();
var col = index.getRange(START_CELL).getColumn();
// 目次シート以外を取得
var sheets = spreadsheet.getSheets().filter(x=>x.getName() != index.getName());
// スプレッドシートのurl取得
var url = spreadsheet.getUrl()
// 目次シートにシート名とハイパーリンクを設定
for(var i=0; i<sheets.length; i++)
{
// 目次書き込み開始セルから下にシート名を記載
var range = index.getRange(row + i, col);
// シート名とハイパーリンク設定
var sheeturl = `${url}#gid=${sheets[i].getSheetId()}`
var link = `=HYPERLINK("${sheeturl}","${sheets[i].getName()}")`
range.setValue(link);
}
}
スクリプトの先頭で、目次シートの名(SHEET_NAME)と目次書き込み開始セル(START_CELL)を定数定義しています。こちらは必要に応じて変更してください。
目次シートは存在しなければ新規作成します。既に存在していれば目次シートの内容をクリアします。目次書き込み開始セルの行番号と列番号はgetRow()とgetColumn()で特定します。
spreadsheet.getUrl()でスプレッドシートのurlを取得します。ハイパーリンクを設定するときにurlが必要になります。
シートの数だけforループを回し、シート名とハイパーリンクを設定します。
ハイパーリンクは以下の形式でsetValue()すると設定できます。
=HYPERLINK("シートのURL", "シート名")
目次シートは存在しなければ新規作成します。既に存在していれば目次シートの内容をクリアします。目次書き込み開始セルの行番号と列番号はgetRow()とgetColumn()で特定します。
spreadsheet.getUrl()でスプレッドシートのurlを取得します。ハイパーリンクを設定するときにurlが必要になります。
シートの数だけforループを回し、シート名とハイパーリンクを設定します。
ハイパーリンクは以下の形式でsetValue()すると設定できます。
=HYPERLINK("シートのURL", "シート名")
GASの実行例
GASエディタ画面の「実行」をクリックしてGASを実行します。
自動作成された目次シートにすべてのシート名が出力されます。シート名をクリックするとハイパーリンクで設定されている対象シートに移動します。