メインコンテンツへスキップ
見出し画像

【Googleスプレッドシート最新関数 + GASカスタム関数】SHEET関数、SHEETS関数 こう使え！（完全版シートインデックス関数の作り方）

    mir

    2026年2月、Googleスプレッドシートに2つの新しい関数

    SHEET関数
    SHEETS関数

    が追加されました。

    👇この新関数を紹介・検証した noteがコチラ

    👆こちらのnoteを書いた段階では、SHEET関数、SHEETS関数のコレ！といった活用方法が思いつかなかったんですが、その後の検証でイメージしていた便利な活用方法が形になりました。

    今回のnoteは 「Googleスプレッドシートの SHEET関数、SHEETS関数は こう使え！」と題してお届けします。（キャッチーなタイトルにしているだけで、実際は使い方の一例だと思ってください）

    前回のnoteは、【QuickTips 16】削除も移動もできない？Googleドライブからダウンロードした「謎ファイル」を削除する方法 を紹介しました。

    シンプルで便利なGoogleWorkaspaceの Quick Tips をマガジンにまとめています。


    Googleスプレッドシートの目次をリアルタイムで更新する 完全版 シートインデックス関数

    SHEET関数、SHEETS関数のおススメ活用方法、それが全シートの目次生成関数 SHEET_INDEX関数 と組み合わせる使い方です！

    このSHEET_INDEX関数は、組み込みで用意されている関数ではなく GASで自作するカスタム関数です。

    適当なセルに入れるだけで 全シートのシート名がシートの並び順通りに縦1列で出力され、

    画像

    さらにシート名がハイパーリンクになっていて、クリックするだけでそのシートに飛べるという関数です。

    画像

    「完全版」名付けたのは、今回は目次が

    • シートの追加

    • シートの並び替え

    • シート名変更

    • シートの削除

    これらのシート操作を検知して、リアルタイムで最新の目次に更新されるようになったからです！

    画像
    シートの追加
    画像
    シートの並び替え
    画像
    シート名変更
    画像
    シートの削除

    これまではシート操作を検知することが難しく、リアルタイムに更新されるシート目次関数が作れませんでした。

    これが新関数 SHEET関数、SHEETS関数を組み合わせることで、実現可能となりました！！

    今回は 完全版シートインデックス関数（シート目次関数）の作り方を紹介します。


    GAS カスタム関数で作る シートインデックス関数

    2026年3月時点、Googleスプレッドシートの標準関数では 全シートのシート名を取得したり、シートID（gid= の後の数値）を取得することは出来ません。

    これらを取得する為には Google Apps Script （GAS）で、カスタム関数（自作関数）を用意する必要があります。

    以前一度紹介していますが、まずは（完全版ではない）基本のシートインデックス関数の作り方をおさらいしましょう。

    GASを実行するのではなく、カスタム関数を使うメリットは、GASでお馴染みのユーザー毎の初回の承認が不要という点です。


    シートIDとシート名の両方を取得するカスタム関数

    GASのカスタム関数とは、スクリプトエディタ上で記述した関数をスプレッドシートのセル内で 組み込みの関数と同じように（SUMとかIFと同じように）

    =作成した関数名()

    と入力して利用できる機能です。

    👇こちらの noteで詳しく解説しています。

    シートインデックス関数を作成するのに必要な情報は、シート名とシートID（gid）なので、

    画像

    メニューからスクリプトエディタを開き

    画像
    function sheetsInfo(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets().map(sheet => [sheet.getName(),sheet.getSheetId()]);
  return sheets; 
}

    こんな感じで sheetsInfo関数を定義し 保存ます。

    スプレッドシートの適当なセルに

    =sheetsInfo() と入れれば

    画像
    カスタム関数は 大文字、小文字を気にせず記述してOK

    このようにブック内（スプレッドシートファイル内）の 全 シート名とシートIDが出力されます。

    GASのカスタム関数名を sheetsIndex関数としていないのは、この段階では単なる シート名とIDの羅列で完成形ではないからです。

    また、カスタム関数はドキュメンテーションコメント（JSDoc）を入れた方がよいのですが

    今回はこの後他の関数と組み合わせて、最終的に名前付き関数としてパッケージするので、この時点では特にコメントを記述しなくても問題ありません。


    カスタム関数ではハイパーリンクを生成できない

    本当はカスタム関数で ハイパーリンクが返せればよいのですが、残念ながらカスタム関数ではハイパーリンクを出力することが出来ません。

    クリックしたら、そのシートにジャンプできるハイパーリンクとする為には、このsheetsInfo() 関数で 取得した シート名とシートIDを HYPERLINK関数と組み合わせる必要があります。

    画像
    =LET(
  si,sheetsInfo(),
  ARRAYFORMULA(
    HYPERLINK("#gid="&INDEX(si,,2),INDEX(si,,1))
  )
)

    式を少し解説すると LET関数で sheetsInfo関数の結果を si とした上で、


    INDEX関数で 1列目のシート名、2列目のシートIDをそれぞれ取得。

    画像

    これを HYPERLINK関数の引数として利用します。

    同じスプレッドシート内の 特定シートへジャンプするハイパーリンクの記述方法は

    #gid=【シートID】

    とすればよいので、"#gid=" とINDEX(si,,2) を &連結します。

    ここは配列処理となる為 ARRAYFORMULAを付ける必要があります。

    =LET(si,sheetsInfo(),ARRAYFORMULA(HYPERLINK("#gid="&INDEX(si,,2),INDEX(si,,1))))

    これで完成。

    他にも BYROW関数 を使った数式でもOKです。こちらの方が式は短くなります。

    画像
    =BYROW(sheetsinfo(),LAMBDA(r,HYPERLINK("#gid="&INDEX(r,,2),INDEX(r,,1))))

    BYROW関数は、行毎にLAMBDA内に記述した数式の処理が出来る関数です。


    名前付き関数でパッケージ

    最後にこの数式を名前付き関数として使いやすくします。

    メニューから データ ＞ 名前付き関数 と進み 「新しい関数を追加」をクリック。

    画像

    関数名を SHEET_INDEX として、説明を入れておきましょう。

    この関数は引数はないので、引数のプレースホルダは空欄のままで。

    画像

    上で作った式を 数式の定義にそのままコピペ。LAMBDAを使う必要はありません。

    画像

    次へを押して最後に「作成」で完成です。


    画像

    GASのカスタム関数を使った数式を、さらに 名前付き関数と出来るのが面白いですね。

    名前付き関数は セルに途中まで入力すれば関数の候補として表示されますし

    画像

    このように自分で入れた説明も表示されます。


    シートインデックス関数の弱点

    基本のシートインデックス関数は非常に便利なんですが、リアルタイムでシートの変更が反映されないという不満がありました。

    画像

    シートの移動やシート名変更、シートの削除、追加をしても、再計算はされず SHEET_INDEX関数で出力される目次に変化がありません。

    GASのカスタム関数は揮発性関数なので、しばらく放置していれば 再計算されて最新目次に更新されるんですが、やはりシートに動きがあったその時にリアルタイムで再計算して欲しいところ。

    ちなみに更新頻度をアップしようと NOW関数や乱数系関数を組み合わせると

    画像

    このようにエラーとなります。

    頻繁に再計算され高い負荷がかかることを防止する為、GASのカスタム関数は NOWやRAND系と関数と組み合わせることが出来ない仕様となっています。


    新関数 SHEET関数、SHEETS関数を組み合わせた 完全版シートインデックス関数

    シートの並び替え、シート名変更、追加、削除を検知して、シートインデックス関数を再計算させるカギとなるのが、SHEET関数、SHEETS関数です！

    新関数 SHEET関数、SHEETS関数の特殊な仕様

    2026年2月に Googleスプレッドシートに追加された新関数 SHEET関数、SHEETS関数は 

    SHEET関数
    指定したシートのシート番号（何枚目のシートか？）を返す

    SHEETS関数
    ブック（スプレッドシート）のシート枚数を返す

    と単体としては、なんの役に立つかイマイチわからない関数なんですが・・・


    注目すべきは

    シートの追加、シートの削除　▶　SHEETS関数の結果が変化

    画像

    シートの移動、シートの名前変更　▶　SHEET関数の結果が変化

    画像

    このようにシート操作を検知して結果が変化する、他の関数には無い特殊な仕様である点です。

    これをGASのカスタム関数 sheetsInfo関数と組み合わせて、シート操作を検知させれば、リアルタイムで目次が更新されるはずです！！


    完全版シートインデックス関数 作り方

    画像

    お待たせしました。

    SHEET関数、SHEETS関数を引数として組み合わせて シート操作を検知、リアルタイムで再計算する 完全版シートインデックス関数の作り方です！


    1. GASカスタム関数で sheetInfo関数を 2つ引数がとれるように設定

    画像
    function sheetsInfo(x,y){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets().map(sheet => [sheet.getName(),sheet.getSheetId()]);
  return sheets; 
}

    元のコードからほぼ変更はありません。1行目の関数の定義部分で

    function sheetsInfo(x,y){

    このように 2つの引数をとるように変えただけです。


    2. sheetsInfo関数の2つの引数にSHEETS関数、SHEET関数の数式を設定

    そして、このsheetsInfo関数の 引数 x, y には

    画像
    =sheetsInfo(
  SHEETS(),
  MAP(INDEX(sheetsInfo(),,1),LAMBDA(s,IFERROR(SHEET(s),0)))
)

    このようにそれぞれ数式を入れます。

    第1引数が シートの削除、追加を検知する SHEETS関数

    第2引数が シートの移動、シート名変更を検知する 
    　MAP(INDEX(sheetsInfo(),,1),LAMBDA(s,IFERROR(SHEET(s),0)))

    SHEET関数の方は、まずシート名ありきなので、sheetsInfo関数の1列目をINDEXで取得したものを MAP関数で値ごと（シート名毎）に処理しています。

    また、シート名に変更があった場合は #RRF!エラーとなるので、 IFERRORを組み合わせてエラーの場合は 0を返すようにしています。

    sheetsInfo関数の引数に再度sheetsInfo関数を使うのは違和感がありますが、あまり難しく考える必要はありません。

    sheetsInfo関数の引数として利用する方のMAP内で使う sheetsInfo関数には 引数を入れないのがポイントです。

    画像

    最初は mirも再帰式を書いたり 色々と考えすぎてハマりましたが、シンプルにSHEETS関数、全シートのSHEET関数、これをそのまま引数とすればよいだけという答えにたどり着きました。

    シートの並び替え、名前変更、削除、追加 があると 引数の値が変化するので、それをトリガーに sheetsInfo関数が再計算され シート名、シートIDが並び順を含め最新情報に更新されます。

    引数なしの時と同じように見えますが

    画像

    シートの移動、名前変更、追加、削除 を検知し リアルタイムに更新されているのがわかりますね。


    3. LET関数、ARRAYFORMULA関数、HYPERLINK関数と組み合わせてハイパーリンク化

    これを LET関数、ARRAYFORMULA関数、HYPERLINK関数と組み合わせた数式を作ります。

    =LET(
  si,sheetsInfo(
    SHEETS(),
    MAP(INDEX(sheetsInfo(),,1),
      LAMBDA(s,IFERROR(SHEET(s),0))
    )
  ),
  ARRAYFORMULA(
    HYPERLINK("#gid="&INDEX(si,,2),INDEX(si,,1))
  )
)

    これで該当シートにジャンプするハイパーリンクとなりました。



    4. 名前付き関数 SHEET_INDEX関数として定義

    最後にこれを名前付き関数として新規登録（先ほどSHEET_INDEX関数を作成している場合は編集から数式の定義を差し替え）とします。

    画像

    これでリアルタイムでシート情報が更新される完全版シートインデックス関数（シート目次関数）の完成です。

    適当なセルに =SHEET_INDEX()　を入れれば、

    画像

    クリックで該当シートにジャンプできる目次が生成されます。


    従来版と完全版のシートインデックス関数比較

    画像

    最初に紹介した基本のシートインデックス関数と、完全版シートインデックス関数を比較してみましょう。

    式を入れただけでは同じ結果が返るので違いはわかりませんが、

    シートの並び替えやシート削除

    画像

    シート名変更やシート追加

    画像

    これらのシート操作を検知して目次が更新されるのは完全版だけです。

    従来版のシートインデックス関数は、シートを更新したのに一切動きがありません。

    これが今までは出来なかったんですよね。


    目次があればどこでも飛べる

    今回は2026年2月  Googleスプレッドシートに追加された新関数 SHEET関数、SHEETS関数を GASカスタム関数と組み合わせて完全版シートインデックス関数を作る方法を紹介しました。

    シートの移動は、QuickTipsで紹介した ショートカットによる簡単な移動方法もありますが

    枚数が多いシートだと、目次から一発でお目当てのシートに飛べると便利ですね！

    ただこのシートインデックス関数、GASでカスタム関数を定義して、それを標準のシート関数と組み合わせてちょっと複雑な数式を組んで、さらにそれを名前付き関数として定義って・・・最初の準備が面倒（手数が多い）なんですよね。

    やはり公式の組み込み関数として、シート名やシートIDを取得できる関数を実装して欲しいものです。

    次回は Quick Tips で小ネタ紹介を予定。


     
     

    mir

     
     
    元Excel職人・VBA使いから、Googleスプレッドシート職人・GAS使いにジョブチェンジ。謎解き感覚で お題（課題）を解決していくような記事を書こうかなと。その他、AIやらGeminiやらGoogleWorkspaceネタ全般

    あなたへのおすすめ