別シートへのリンクテキストをシート名に更新するGAS

はじめに

最近、業務でスプレッドシートを使って資料や仕様書を作成する機会が増え、便利なスクリプトをいくつも書くようになりました。しかし、新たに気づいた不便な点がありました。それは、目次を作成した後にシート名が変更された際に、目次のシート名が自動で更新されないという問題です。

たとえば、最初に目次を作り、シート名を設定しても、仕様変更などでシート名が変わると、目次に反映されません。この問題を解決するために、今回はシート名の変更を目次に反映させるスクリプトを作成しました。

コード

function myFunction() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // シート一覧
  const sheets = activeSpreadsheet.getSheets();

  function getSheetByGid(gid) {
    var sheet = null
    for (var i = 0; i < sheets.length; i++) {
      if (sheets[i].getSheetId() == gid) {
        sheet = sheets[i]; // 一致するシートを返す
        break
      }
    }
    return sheet; // 一致するシートがない場合はnullを返す
  }

  // 選択しているセルを取得
  const activeSheet = activeSpreadsheet.getActiveSheet();
  const selection = activeSheet.getActiveRange(); // 選択しているセルの範囲を取得
  const numRows = selection.getNumRows();
  const numCols = selection.getNumColumns();

  for (let row = 1; row <= numRows; row++) {
    for (let col = 1; col <= numCols; col++) {
      
      const cell = selection.getCell(row, col);
      
      
      var link = cell.getRichTextValue()?.getLinkUrl();
      var link_match = link?.match(/#gid=([0-9]+)/) 
      if(link && link_match){
        // リンクがあり、そのリンクと同じシートが存在するか
        var gid = link_match[1]
        var targetSheet = getSheetByGid(gid)
        if(targetSheet){
          var richTextBuilder = cell.getRichTextValue().copy()
          var richTextLink = cell.getRichTextValue().getLinkUrl()
          richTextBuilder.setText(targetSheet.getName()).setLinkUrl(richTextLink)
          cell.setRichTextValue(richTextBuilder.build())
        }
      } else {
        // リンクは一致しないが、同じ名前のシートが存在する時
        var cellText = cell.getRichTextValue() ? cell.getRichTextValue().getText() : cell.getValue()
        var targetSheet = activeSpreadsheet.getSheetByName(cellText)
        if(targetSheet){
          var richTextBuilder = cell.getRichTextValue().copy()
          richTextBuilder.setText(targetSheet.getName()).setLinkUrl("#gid="+ targetSheet.getSheetId())
          cell.setRichTextValue(richTextBuilder.build())
        } else {
          // 処理されなかったシートの一覧は名前が被らないシートを先頭に作成してリストで出力する予定(未実装)
        }
      }
    }
  }

}

使い方

下記画像のように目次シート内で範囲選択をした状態で上記のコードを実行します。

処理内容

行っている処理は下記の2つです。

• すでにリンクが設定されており、リンク先のシートが存在するセルはテキストをシート名に変更(最新のシート名を適用)
• セルに入力されているテキストのシート名が存在している場合はリンクを設定(リンクの設定)

おわりに

扱うシート数が少しなら困りませんが数十以上のシートを扱うようになった場合管理がとても難しくなります。今回のGASを使うことで目次の管理がかなり楽になると思います。
使用用途は限定的ですがおすすめのスクリプトです。

今回のようなスクリプトに需要がありそうならシート一覧の目次を自動生成するスクリプトなども紹介しようかなと思います。

今回作ったスクリプトは少し説明が難しかったので分かりづらかったらすみません。