😗

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

2021/03/25に公開
1

この記事でやること

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

こんな具合です

intro

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

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

下準備

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

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

2. コピペする際に邪魔なので、コード.gsの記述をすべて消す(Ctrl + ABackSpace


一度まっさらにします

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;
    }
}

大まかな処理の流れ

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

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

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

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

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


コードを貼り付けます

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

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

3. 実行の左のボタンを押して、プロジェクトを保存


忘れずに必ず保存しましょう

4. スプレッドシートをリロード、再読み込み

→数秒するとカスタムメニューが出るはずです。

ヘルプの横に"カスタム"メニューが出現

これで準備は完了です。

目次を生成してみよう

ではさっそく、目次を生成してみましょう。

  1. カスタムメニューから、目次を作成するをクリック
  2. 承認が必要と言われたら、続行をクリック
  3. アカウントを選択
  4. なんか警告が出るので詳細をクリックし
    無題のプロジェクト(安全ではないページ)に移動をクリック
  5. アカウントへのアクセスリクエストを許可

    これで承認が通ります
  6. 再びカスタムメニューから、目次を作成するをクリック

    今度は行けるはず
  7. ダイアログが出るのでOK
  8. 目次が生成されました!

    やったぜ

実装に関してのあれこれ

TARGETSHEETをgetActiveSpreadsheetで取らなかったのは何故か

これは途中で直せば良かったことに気づいたのですが
スプレッドシートIDの概念を皆さんに知って欲しかった ということにしましょう。

ssInitで初期化している理由

onOpen()の処理を走らせる時に.openById()に権限が無くコケるからです。

setFomula()使わないの?

コード書き終わったあとに気づきました。面倒なので直しませんでした...

getXXX なのか generateXXX なのか命名規則をハッキリしろ

リファクタリング前は、値を引数に取らずグローバル関数を書き換えていたため
"generate"という命名をしていました。
今回リファクタリングにあたって、関数名を書き換えるという発想に至らずそのままになっています。

その他

今回は、このブログのために
入社したての頃に書いたコードをリファクタリングしていました。
結構気にしたのはfor文の処理ですね。
for(var i=0,i<list.length,i++)みたいなのを書きたくなくて、できるだけモダンなコードを目指したつもりです。

outro

GASのバージョンが上がって、letが使えるようになるなど色んな変化が見られました。
filterforEachなど、コールバック関数の概念を理解するのに一番時間を要したかな。

普段はインフラエンジニアなので
コードを書くのはかなり久々ですが、なかなか良いものが出来た気がしています。

やっぱり書きっぱなしのコードは最高ですね。
レビューで突き返されたり、数百のテストケース消化するの嫌ですもん笑

ではまた。

脚注
  1. Google Developers -Apps Script- ↩︎

Discussion

じゅんじゅん

有用な記事ありがとうございます。早速使わせていただきました。
一つだけ…目次シートを事前に作っておかなければいけないということに気がつくまで3分位かかりました笑
追記しておくともしかしたら親切かもしれません…
(私だけかもしれませんが…汗)