Google Spreadsheetに「目次」っぽいシートを付ける処理をGoogle Apps Scriptで書く

Google Spreadsheet にタブをたくさん作ると、[すべてのシート]のポップアップリストは使いづらい

Google Spreadsheetや一般的な表計算ソフトでは、シート切り替えは画面下のタブから行わねばならず、シートをたくさん含む場合は切り替えが微妙にメンドクサイと思います。そういう場合に Google Spreadsheet では、画面左下のバーガーメニュー的なアイコンで[すべてのシート]をリスト表示でき、そこで選択したシートに移動できます。

しかしシートの数が多いと、リスト表示でスクロールが必要になるため、これはこれで微妙に使いづらいです。

また、リスト表示から選んで、ブラウザの別タブでそのシートを開く、ということもできないようです。Google Spreadsheet では同じスプレッドシートに含まれる複数のシートを、それぞれ別のブラウザウィンドウで表示することができるので、単にシートに移動するのではなく、別々のシートの内容を見比べたりできるほうが便利だと思うんです。

Google Spreadsheetに「目次」のシートをつけて、シートの名前とURLを列挙していれば、もっと便利に使えるはず

シート名と切り替え用のURLを一覧を目次として最初のシートに持たせておけば、切り替えがとてもラクになります。そうすると目次のシートのリンクから別々のウィンドウで開いたりできるわけです。

でも、そういう目次のシートを手作業で作るのは、それはそれでメンドクサイ話。だから Google Apps Script で生成させてみることにします。

以下の例では、BeaconFence（屋内に固定設置したiBeaconを用いての測位や地図表示を可能にする、Delphi/C++Builder向けIoTコンポーネント）の測位データを扱っているのですが、生データのシートと簡易ヒートマップのシートを切り替えてみました。最初はタブをクリックして切り替えていますが、途中で Google Apps Script による目次生成を行って、その目次からシートの切り替えを行っています。

実装

Google Apps Script でこんな風に実装してみました。

function generatePageList(){
  var spreadshsset = SpreadsheetApp.getActiveSpreadsheet();
  var numSheets    = spreadshsset.getNumSheets();
  var indexSheet   = spreadshsset.getSheets()[0];

  spreadshsset.moveActiveSheet(1);

  // アクティブなシート名が 'index' の場合だけ処理を続ける
  if ( spreadshsset.getSheetName() != "index" ) {
    return;
  }

  // 編集範囲を取得
  var range = indexSheet.getRange(1,1,numSheets,2);  

  range.getCell(1,1).setValue("シート名");
  range.getCell(1,2).setValue("URL");

  // スプレッドシートのシート名とURLを取得して index に出力する
  // 対象範囲は全部のシート（ただし一番左側のシートを除く）とするので
  // i = 1 から開始する
  for(var i = 1; i < numSheets; i++){
    var sheet     = spreadshsset.getSheets()[i];
    var sheetName = sheet.getSheetName();
    var sheetURL  = spreadshsset.getUrl() + '#gid='+String(sheet.getSheetId());

//    Logger.log(sheetName);
//    Logger.log(sheetURL);
    // データは2行目以降に書く
    range.getCell(1+i,1).setValue(sheetName);
    range.getCell(1+i,2).setValue(sheetURL);
  }
}

やっていることはシンプルで、以下の通りです。

• アクティブなシートの名前が index の場合だけ処理を行う（意図しない書き換えを防止するため）
• 全てのシートの名前とURLを取得して、index のシートに書き込む

ただし、シート名とURLを抽出する処理は少々手を抜いておりまして、index のシートは一番左（１枚目）のシートであるとみなしています。もうすこし真面目に実装しても良いのですが自分の使い方の範囲では実用上の問題がないので、こういう実装にしています。

あとは、シートを増やしたり、シート名を変えるたびに、このスクリプトを実行すればOKです。それすらも面倒だと思う場合は、１時間に１回自動で実行させてもよいんじゃないかと思います。（が、タイマー駆動の動作検証はしていません）