スプレッドシートの目次シートを自動でつくる

背景

スプレッドシート内にシートが大量にあるとき、目次シートが欲しくなる。
目次シートを勝手に作って更新してほしいし、
ついでに目次更新はメニューからできるようにしたい。

※あらかじめ「目次」シートを作っておいてください。(ここは手抜きしました、ごめんなさい)
※全シートのレイアウトが同じなスプシを想定しています。テーブル定義書とか台帳とかですね。

GAS

/**
 * カスタムメニュー追加
 */
function onOpen() {
  SpreadsheetApp
    .getActiveSpreadsheet()
    .addMenu('カスタム', [
      {name: '目次更新', functionName: 'updateTableOfContents'},
    ]);
}

/**
 * 存在するシートをもとに目次シートを作る
 */
function updateTableOfContents() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("目次");
  activeSheet.activate();
  var activeSheetId = activeSheet.getSheetId();

  var data = [];
  // 目次シートに出したい項目の見出しをここに列挙
  data.push(['No','タイトル','hoge列','fuga列']); 
  var idNum = 1;
  for(var i = 0; i < sheets.length; i++) {
    // 目次のシートはスキップする
    if (sheets[i].getSheetId() == activeSheetId) continue; 

    var sheetName = sheets[i].getName();
    // 目次シートに出したい項目の位置を地道に書く
    var title = sheets[i].getRange(1,2).getValue();
    var hoge = sheets[i].getRange(2,2).getValue();
    var fuga = sheets[i].getRange(3,2).getValue();
    var titleLink = '=HYPERLINK("#gid=' + sheets[i].getSheetId() + '","' + title + '")'
    
    data.push([idNum++,titleLink,hoge,fuga]);
  }
  
  activeSheet.clear()
  
  activeSheet.getRange(1,1,1,3).setFontSize(20)
  activeSheet.getRange(1,1,1,1).setValue(['目次(このシートの内容は自動生成されます)'])
  
  var rows = data.length
  var cols = data[0].length
  var range = activeSheet.getRange(3,1,rows, cols)
  
  // 罫線
  range.setValues(data).setBorder(true, true, true, true, true, true)
  // 折返し
  range.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)
  // 縦位置
  range.setVerticalAlignment("middle");
  // フォント（お好みで）
  range.setFontFamily("Sawarabi Gothic")

}

実行する

スクリプトエディタにGASを保存したらスプシを開き直す。
スプレッドシートのメニューに「目次更新」が出てくるので、こちらから。