😗

GASで一発　シートの目次づくり

2021/03/25に公開

1件

この記事でやること

※コードに関して、よりスマートな書き方・実装あれば教えて下さい🙏

こんな具合です

intro

最近は、スプレッドシートに議事録や手順書をまとめている企業も多いかと思います。
スプレッドシート内にシートが増えることで、何が面倒かというと
『目的のシートまでスクロールして辿り着く』という作業ではないでしょうか？

このスクロールがとても面倒
というわけで、GoogleAppsScript^[1]を使用して目次を作ってみましょう。

下準備

0. 目次を作成したいスプレッドシートを開く

1. 上部メニュー`ツール`から`スクリプトエディタ`を選択

2. コピペする際に邪魔なので、`コード.gs`の記述をすべて消す（`Ctrl + A`→`BackSpace`）

一度まっさらにします

3. スプレッドシートの`スプレッドシートID`をメモしておく

※スプレッドシートIDはURLから分かります。

https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=シートID

後で必要になりますので、スプレッドシートIDは必ずメモしておきましょう。

目次を一発生成するコード

コード.gs

////*ここの値を変えてください*////
// シートID
const MYSHEETID  = 'xxxxxxxxxxxxxxxxxx'
// 目次シートの名前
const MOKUJINAME = '目次'
// 開始列
const STARTCOL   = 'A'
// 開始行
const STARTROW   =  1
//////////////////////////////

/* シート情報のグローバル変数。ssInitで読み込み */
let TARGETSHEET
let INDEXSHEET
function ssInit(){
  TARGETSHEET = SpreadsheetApp.openById(MYSHEETID)
  INDEXSHEET = TARGETSHEET.getSheetByName(MOKUJINAME)
}

/* メイン処理 */
function main(){
  if(confirmDialog("目次を作成しますか？")){
    generateIndex()
  }
}

/* 表紙ページにリンクを作成する */
function generateIndex(){
  ssInit()
  const sheets = TARGETSHEET.getSheets()
  const infomaps = getNonHiddenSheetInfoMaps(sheets)
  const linktexts = generateLinkTexts(infomaps)
  //開始セルからリンクを挿入
  linktexts.forEach( function( value, index ) {
    var range = STARTCOL + ( STARTROW  + index )
    INDEXSHEET.getRange(range).setValue(value);
  });
}

/* 非表示ではない、かつ目次でもないシートの"シート名"・"シートID"を返す */
function getNonHiddenSheetInfoMaps(sheets){
  let sheet_info = []
  for (sheet of sheets.filter(sht => !sht.isSheetHidden() && sht.getName() !== MOKUJINAME )){
    sheet_info.push({Name:sheet.getName(),Gid:String(sheet.getSheetId())})
  }
  return sheet_info
}

/* リンク文字列のリストを生成 */
function generateLinkTexts(infomaps){
  let textvalues = [];
  for (infomap of infomaps){
    var linkstyle_gid = '#gid=' + infomap.Gid;
    var linktext = adjustHyperLinkStyle(infomap.Name,linkstyle_gid);
    textvalues.push(linktext)
  }
  return textvalues;
}

/* ハイパーリンクの形に整形 */
function adjustHyperLinkStyle(str,url){
  return '=HYPERLINK("' + url + '","' + str + '")';
}

/* カスタムメニュー追加 */
function onOpen() {
  console.log('onOpen was called !!')
  SpreadsheetApp.getActiveSpreadsheet().addMenu('カスタム',[{name: '目次を作成する', functionName: 'main'}])
}

/* 確認ダイアログを表示する */
function confirmDialog(question_text){
  const ui = SpreadsheetApp.getUi();
  const response = ui.alert(question_text, ui.ButtonSet.OK_CANCEL);
    if(response == ui.Button.OK){
      return true;
    }else{
      ui.alert('キャンセルしました', ui.ButtonSet.OK);
      return false;
    }
}

大まかな処理の流れ

上で貼り付けたコードの処理内容はざっくりと下のような感じです。

非表示でないシート名とgid取得
ハイパーリンクの形に整形
目次シートのセルに出力

では本作業に入りましょう。

本作業(コードのコピペ)

1. 上に貼り付けたコードを`コード.gs`の欄に貼り付ける

コードを貼り付けます

2. `ここの値を変えてください`の欄を適切な値を入れる

Head	Head
MYSHEETID	事前にメモした`スプレッドシートID`
MOKUJINAME	目次を生成するシートの名前
STARTCOL	目次を生成し始める行(A1開始であれば 'A' )
STARTROW	目次を生成し始める行(A1開始であれば 1 )