ポンコツエンジニアのごじゃっぺ開発日記。

いろいろポンコツだけど、気にするな。エンジニアの日々の開発などの記録を残していきます。 自動で収入を得られるサービスやシステムを作ることが目標!!

【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")
}

現在のアクティブなシート、これを目次ページとして、それ以外のシートを取得して、一覧を作成するというスクリプトになります。

作成手順

この記事がGAS初めてという方向けの項目になります。

ソースコードの入力方法

上のソースコードの入力の仕方については、以下の記事を参考にしていただければわかるかと思います。

www.pnkts.net

ここにかかれている手順のように、スクリプトエディタを開き、上のスクリプトを貼り付けます。

実行方法

目次の更新タイミングはおまかせしますが、例えば、以下の方法があるかもしれません。

定期実行

毎日、または1時間毎などをトリガーとして、目次の更新をする。

www.pnkts.net

スプレッドシート上に更新ボタンを配置

ちょうど昨日書いた記事になりますが、スプレッドシート上の目次のページなどにボタンを配置して、そのボタンを押すことで更新を行えます。

www.pnkts.net

このとき、ボタンに割り当てるスクリプトは、上で紹介したスクリプトの場合、 updateTableOfContents になります。

さいごに

このように、簡単にスプレッドシート上に目次を作成することができました。これを応用すれば、いろんな自動化が実現できるんじゃないかなと思います。