【GAS】スプレッドシートで目次のようにシートの一覧ページを作ろう。

この記事は GAS道場 Advent Calendar 2019 の11日目の記事です。 Google Apps Script(GAS)をこれから使おうという方向けのアドベントカレンダーになります。

今回はこれまでのを活用してスプレッドシートに目次のページを作ってみたいと思います。

完成イメージ

では、まずはこんなのが作りたいってのを共有したいと思います。

f:id:ponkotsu0605:20191210225806p:plain

完成イメージはこのような感じになります。

f:id:ponkotsu0605:20191210225920p:plain

フッターにあるシートの一覧はこのような感じです。

f:id:ponkotsu0605:20191210230042p:plain

各シートのA1のセルに値段を書いています。もしかしたらここのA1のセルにシートのタイトルが書かれるかもしれないですね。

f:id:ponkotsu0605:20191210230312p:plain

目次の各シート名のところには、シートへのリンクも書かれています。

ということで、目次シートには、各シート名とA1セルの文字を貼り付け、しかも、各シートへのリンクが貼られているという状態を作成したいと思います。

ソースコード

実際のソースコードはこちらになります。

function updateTableOfContents() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var activeSheet = SpreadsheetApp.getActiveSheet()
  var activeSheetId = activeSheet.getSheetId();

  var data = [];
  data.push(['商品ID','値段','商品名'])
  var idNum = 1;
  for(var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetId() == activeSheetId) continue; // 目次のシートはスキップする
    var sheetName = sheets[i].getName()
    var name = sheets[i].getRange(1,1).getValue()
    var cellData = '=HYPERLINK("#gid=' + sheets[i].getSheetId() + '","' + sheetName + '")'
    
    data.push([idNum++,name,cellData]);
  }
  
  // いったんキレイにする
  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
  activeSheet.getRange(2,1,rows, cols).setValues(data).setBorder(true, true, true, true, true, true)

  // ヘッダーの背景色をつける
  activeSheet.getRange(2,1,1, cols).setBackground("#3399ff")
}