GASでシート名の目次を作る

この記事はAteam LifeDesign Advent Calendar 2023の21日目の記事です。

はじめに

今回はスプレッドシートを使ってると作りたくなる目次シートをGASを使って自動で作ろうと思います。
サクッと動かすパートと、コードを解説する２パートでお届けします。

サクッと動かす

1. スプレッドシートを作成→GASを開く

拡張機能のApp Scriptをクリックしてエディタを開きます。

2. エディタにコードを貼り付ける

以下のコードをコピペして貼り付けてください。

function onOpen() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.addMenu('スクリプト', [
    { name: '目次生成', functionName: 'main' }
  ]);
}

function main() {
  const sheets = SpreadsheetApp.getActive().getSheets();
  const spreadsheetId = SpreadsheetApp.getActive().getId();
  let contentsSheet;
  const contentsSheetName = '目次'
  if (!isExistSheet(contentsSheetName)) { // 目次シートがなければ新規作成
    const sheet = SpreadsheetApp.getActiveSpreadsheet();
    contentsSheet = sheet.insertSheet();
    contentsSheet.setName(contentsSheetName);
  } else {
    contentsSheet = SpreadsheetApp.getActive().getSheetByName(contentsSheetName);
  }

  let links = [];

  for (const sheet of sheets) {
    const sheetId = sheet.getSheetId();
    const sheetName = sheet.getSheetName();
    const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/edit#gid=${sheetId}`;
    const link = [`=HYPERLINK("${url}","${sheetName}")`];
    links.push(link);
  }
  if (links.length > 0) {
    const startRow = 1;
    const startColumn = 1;
    const range = contentsSheet.getRange(startRow, startColumn, links.length, 1);
    range.setValues(links);
  }
}

function isExistSheet(sheetName) {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  return sheets.some(sheet => {
    if (sheet.getSheetName() === sheetName) {
      return true;
    }
  });
}

3. 動かしてみる

コードを貼り付けて保存すると、スクリプトというメニューが出てきます。
目次生成を押しましょう。

押すと

• 目次シートがなければ作成
• 目次シートに全シート名へのリンク付きの目次を生成

＃# 4. 目次の出力位置を変える

利用しているシートによって目次を表示するセルを変更したいと思います。
そんな時は、GASの以下の部分のstartRowとstartColumnを変更します。

• startRow： 行番号
• startColumn：列番号

になります。

if (links.length > 0) {
    const startRow = 1;
    const startColumn = 1;
    const range = contentsSheet.getRange(startRow, startColumn, links.length, 1);
    range.setValues(links);
  }

例えば、
画像のような場所だと以下のように書き換えます。

javascript

if (links.length > 0) {
-    const startRow = 1;
+    const startRow = 3;
-    const startColumn = 1;
+    const startColumn = 2;
    const range = contentsSheet.getRange(startRow, startColumn, links.length, 1);
    range.setValues(links);
  }

目的の場所に目次を出すことができました。

コード解説

1. スプレッドシートに関数実行ボタンを設置する

以下の記述で、スプレッドシートを読み込んだ際に
スクリプトというメニューを作成し、その中に目次生成というmain関数を実行するボタンを設置しています。

function onOpen() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.addMenu('スクリプト', [
    { name: '目次生成', functionName: 'main' }
  ]);
}

2. 特定のシートの存在チェック

isExistSheet（'目次'）を実行すると
目次シートが見つかった場合に、true 見つからなかった時に falseを返します。

function isExistSheet(sheetName) {
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  return sheets.some(sheet => {
    if (sheet.getSheetName() === sheetName) {
      return true;
    }
  });
}

3. シートのリンクを作成

• getSheets()でシートを全部取得
• シートの数だけループを実行しHYPERLINKを作成
• 作成したHYPERLINKを linksに挿入

しています。

  const sheets = SpreadsheetApp.getActive().getSheets();
  const spreadsheetId = SpreadsheetApp.getActive().getId();
  ・
  ・
  ・
  let links = [];

  for (const sheet of sheets) {
    const sheetId = sheet.getSheetId();
    const sheetName = sheet.getSheetName();
    const url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/edit#gid=${sheetId}`;
    const link = [`=HYPERLINK("${url}","${sheetName}")`];
    links.push(link);
  }

4. リンクを目次シートに書き込み

startRowで指定した行位置、startColumnで指定した列位置に
作成した目次リンクをsetValuesで書き込みます。

if (links.length > 0) {
    const startRow = 1;
    const startColumn = 1;
    const range = contentsSheet.getRange(startRow, startColumn, links.length, 1);
    range.setValues(links);
  }

最後に

GASはスプレッドシートを活用した業務をサクッと効率化できるのでいいですね！
皆さんは他にどんなちょっとした業務効率化をしていますか？
コメント等にて是非教えてください。