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

この記事は[Ateam LifeDesign Advent Calendar 2023](https://qiita.com/advent-calendar/2023/ateam-life-design)の21日目の記事です。

# はじめに

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

# サクッと動かす

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

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

![無題のスプレッドシート-Google-スプレッドシート.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/445600/9aae98ed-bef6-905d-c055-a30500d676bb.png)


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

![無題のプロジェクト-プロジェクト編集者-Apps-Script.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/445600/77d54426-db14-f92d-6900-ce56ee3fd46f.png)

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

```javascript
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. 動かしてみる

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

押すと

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


![無題のスプレッドシート-Google-スプレッドシート (1).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/445600/6462cce4-85e6-ce52-2b21-1c8f5d205705.png)


![スクリーンショット 2023-12-18 21.31.04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/445600/1b107378-30c5-7812-3828-efaf587fcb1b.png)

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

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

- startRow： 行番号
- startColumn：列番号

になります。

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

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

```diff: 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);
  }
```

![無題のスプレッドシート-Google-スプレッドシート (2).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/445600/6a43e038-f614-7750-4b33-eeb187f08fcd.png)

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

![スクリーンショット 2023-12-18 21.40.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/445600/c6241d80-a0c4-c0dd-f37b-c53442f00c17.png)

# コード解説

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

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

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


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

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

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

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

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

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

しています。

```javascript
  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`で書き込みます。

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

# 最後に

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