【GAS】Googleスプレッドシート自作関数でシート情報を取得する -1

mir

2022年11月5日 14:53

これは本編のシリーズネタとは別で、旬の話題や Googleスプレッドシート、GoogleWorkspace関連でランダムに気になったことを書いていく 雑談記事です。土日に新しい記事を出していこうかなと思います。

前回の記事
【LAMBDA / XLOOKUP】Googleスプレッドシート新関数検証 -6

2022年9月から使えるようになった新関数と新機能。
検証記事の 1～3で LAMBDA・ヘルパー関数を、そして4～6 では XLOOKUPを検証・解説してきました。

新関数は歓迎なんですが、いずれも Excel で人気の関数の輸入ってことで、Googleスプレッドシートのオリジナル新関数が無かったのは残念。

今回は 「これができる新関数ほしかったよ～」という関数を GASを使って自作してみようと思います。

ずっと関数ネタが続いたんで、久しぶりに GAS回です。

シート関数で出来そうで出来ないこと

新関数の LAMBDA ・ヘルパー関数が強力すぎて、いままでGASに頼るしかなかった処理や、作業列や作業シートを使わないと実現できなかった計算の多くがシート関数の組み合わせによる 1行数式で実現できるようになりました。

でも現状のシート関数では、どうやっても処理できないものもあります。
その中でも特に需要が高そうなのが、

シート情報の取得・処理
色に関連する情報の取得・処理

この 2つです。

色に関しては、指定した色のセルを数えたり、指定した色のセルのみを抽出したりですかね。

個人的にはセルの色に頼る運用は見直した方がよいと思うんで、色関連の新関数は特に必要ないかと思います。（欲しいって人がいるのもわかりますが）

でも シート情報を引っ張れる関数は欲しかったんですよね。

シート情報が取得できさえすれば、LAMBDAとかの新関数と組み合わせて一気に関数で出来ることが広がるのにー。

というわけで、mir 的に 追加して欲しい新関数第1位、シート情報を取得する関数 を自作してきます。（色々盛り込んだら1回じゃ終わらないな・・・）

Googleスプレッドシート関数はシート情報が取得出来ない

「関数で出来ないならGASを使えばいいじゃない。」

マリーアントワネットじゃないですが、確かにたいしたコードじゃないので GASで自作関数を作るのは別にいいんですが、やっぱり誰でも使えて動きも軽いシート関数で出来た方がいいわけで・・・。

逆にこれくらいのことは標準のシート関数で実現して欲しいもんだ、って思います。

EXCELでは CELL関数でファイル名、シート名が取得できる

Excel だと、CELL関数の検査の種類の中に filename があって、これを使うことでシート名入りのファイルのフルパスが取得できます。

ここから REPLACE とか FINDを使ってファイル名やシート名だけ抜き出せば良いんですが、古典的な関数の処理なのでここは省略。

ちなみに ブック内の全シート一覧も

名前の定義で =GET.WORKBOOK(1) を範囲として定義し、INDEXで 1行目、2行目と取得することで、[ブック名]シート名 が取得できます。

この処理はマクロを利用しており、このまま保存しようとすると xlsm(マクロ有効ブック）で保存しろ！と警告がでます。

実質的にはマクロを使ってるわけですが、それでも簡単ですね。

ただし、これ Web版の Excel オンラインでは出来ません。

Excelオンラインだと、CELL関数は使えるんですが、第1引数で "filename" を使うとエラーになります。公式にも対象外と記載されてます。

また、Googleスプレッドシートにも CELL関数はあるんですが、残念ながら検査の種類に filename がありません。

まぁファイルではないので当然なんですが、それならせめて URLくらい取得できたら 良かったのに・・・。

GoogleスプレッドシートではシートIDも重要

じゃあ、シート名を取得する自作関数をGASで作ればOKか？

いや、ここはシート名だけでなく シート IDも取得できるようにしましょう。

Googleスプレッドシート上でシート関連の処理をする際に、シートIDも使えると便利です。

シートID とは何か？

https://docs.google.com/spreadsheets/d/【この部分がスプレッドシートID】/edit#gid=【この部分がシートID】

紛らわしいですが、ここで出てくる「スプレッドシート」というのが、 EXCELでいうところのブックと同じで、要は複数シートを持つことが出来るスプレッドシートファイル（のようなもの）と理解してください。

1つ1つのスプレッドシートが ユニークな IDを持っており、上記のように URL上では /d/ の後ろに続く部分となります。これは 「スプレッドシートID」と呼ばれるものです。

スプレッドシートIDも GASでは結構使うんですが、自作関数で使うケースはほぼないかな。

スプレッドシートIDは importrange と組み合わせて他のスプレッドシート内のデータ取得に使いますが、実質 自作関数では使えません。

例えば親フォルダ内の全スプレッドシート IDを取得する自作関数を作ろうと思っても、

自作関数は 他のスプレッドシートの情報は取得できない
importrange関数は初回にアクセス承認が必要で。これは手動操作が必要

このように制限があるんで無理なのです。

今回取得したいのはシートIDの方で、スプレドシート内の各シート（画面下のタブ）を切り替えた時に URLの末尾 gid= の後ろが変わります、

このURL の一番後ろ、 gid= に続く部分が シートID となります。

ちなみにスプレッドシートを新規作成した際の最初のシート「シート１」はgid=0 となっていて、それ以降に追加した（コピーした）シートのIDは、ランダムな 9桁の数字になっています。

このシートIDが何のために必要か？というと、HYPELINK関数と組み合わせて指定したセル・シートにジャンプする際に必要となります。

ハイパーリンク関数の URL部分は、通常は https://～とURLを入れてリンクにしますが、同じスプレッドシート内の別シートへ飛ぶリンクは #gid=**** 部分だけで使えるリンクになります。

さらにシートに加えてセル指定リンクとする場合は、#gid=****&range=** とすれば OK。

ただし 同じシート内で別のセルへジャンプするリンクを作成する際も、#gid=**** の記述が必要になります。

#gid=【シートID】&range=【セルアドレス】

例 F15 セルへのリンク（シートIDを 0123456789とした場合）：　
=HYPERLINK("#gid=0123456789&range=F15","セルへのリンク")

こんな感じで使います。 HYPERLINK関数の解説は ↓

シートIDが取得できたら、スプレッドシート内の指定の場所に自由に飛べるリンクを生成できそうですね！

シート名の方は、INDIRECT関数との組み合わせれば、自由に指定のシートの特定のセルの値を取得することが出来そうです！

というわけで、

式を入れたシート（アクティブなシート）のシート名
式を入れたシート（アクティブなシート）のシートID
そのスプレッドシートの全シート名、全シートID
オプション（引数を追加）で取得する内容を制御してみる

この流れで、シート情報を取得する自作関数を作ってみましょう。

GASで自作関数を作る前に

エディタが使える＆ GASを触ったことがある前提で話を進めますが、自作関数の最低限の基本だけは書いておきます。

GASでの自作関数の作り方（基本）

基本的には普通にエディタで GASのファンクションを作成するのと一緒です。作成した function がそのまま自作関数として使えます。

自作関数（GAS関数）の書き方、および特徴です。

■GAS 自作関数の特徴
return で返したものが、セルに結果として返される。
単一の値に限らず、縦横にスピらせた配列を結果として返すことができる。
（setValuesと違って、出力先のサイズセル数を気にしなくてよい）

他のセルを書き換えたりといった「処理」は出来ない。
他のスプレッドシートを読み取ることも出来ない。
出来ることが制限されている。
GASを使ってはいるが、初回のスクリプト承認はない

引数で A1 とセルを指定した場合は、A1 セルの値 が引数に入る。
シート上では関数名の大文字、小文字は区別されない。
※エディタ上では区別されるので重要

通常関数と違って 処理が重い。多用は禁物。
基本的には 引数に変化がない限りは再計算されない。

そのスプレッドシート内の情報を取得して、計算処理し結果を返すものだと思いましょう。詳しくは公式を参照。

自作関数とかGAS関数とか色々な呼び方がありますが、公式に Custom Functions って書いてあるんで、「カスタム関数」って呼ぶのが正しいのかもしれません。

それではエディタを開きコードを書いていきましょう。

アクティブなシート名 / シートID を取得する自作関数

シート名を返す関数、シートIDを返す関数、ほぼ記述は同じなんで２つ一気に作ってみましょう。

Q1. アクティブなシートのシート名（シートID）を返す関数をGASで作りたい

とりあえず最初は、一番簡単な式を入れているシート（アクティブなシート）のシート名を返す関数、シートIDを返す関数を作ってみましょう。

自作関数の場合は、

その式が入っているシート　＝　アクティブシート
その式が入ってるセル　=　アクティブセル

という扱いになります。

関数の名前はなんでもいいんですが、わかりやすいものが良いですね。
当然ですが、すでに存在するシート関数名との重複はNGです。

サクッと作れそうでしょうか？
これは簡単すぎですかね？

↓ 一応、ここから回答

A1. アクティブなシート名（シートID）を返す関数

コードは以下のような感じ。

アクティブなシート名を返す自作関数　activeSheetName

//このシート名を返す
function activeSheetName(){
  return SpreadsheetApp.getActiveSheet().getName();
}

アクティブなシートIDを返す自作関数　activeSheetId

//このシートIDを返す
function activeSheetId(){
  return SpreadsheetApp.getActiveSheet().getSheetId();
}

とくに解説も不要なレベルですね。

今回のようなケースは引数無しでそのまま使えます。

アクティブなスプレッドシートを取得する必要もなく、直接アクティブなシート取得で OK。変数宣言もなくていいですね。

ぶっちゃけ開いている（自分自身の）「シート名」はあまり使いどころはありません。

取得したシートIDの方の活用例を紹介しましょう。

活用例：シートIDを使ってリンク先へ飛ぶ

=HYPERLINK("#gid="&activeSheetId()&"&range="&A1,A1&"セルへのリンク")

このように、自作関数を標準のシート関数 HYPERLINK と組み合わせることで、A1セルに入力したアドレスのセルへジャンプすることが出来ます。

もう少し応用すると

=HYPERLINK("#gid="&activeSheetId()&"&range=A"&XMATCH(A2,A5:A)+4,TO_TEXT(A2)&"へGo")

このように指定した日付へ飛べるリンクなんかも作れます。TODAY関数を使えば、常に本日の日付に飛べるリンクになりますね。

ちなみに列を指定せず シート全体を検索して見つかったセルに飛ぶリンクもシートIDと既存関数の組み合わせで作成可能です。

もちろんこれらは直接シートを開いてURLからシートIDをコピーしたものを使う方法（ID 手入力）でも実現できます。

ただ、その場合はシートをコピーしたり、他のシートで使おうと数式をコピーした際に、シートIDが固定されてしまっているので 毎回シートIDを手作業で差し替える必要があります。

自作関数でシートIDを取得すれば、シートをコピーしても数式コピーでも、そのままどのシートでも使えるというのが大きなメリットです。

全シート名 / 全シートID を取得する自作関数

この流れで次に開いているスプレッドシート内の 全シート名、全シートIDを取得する関数を作成してみましょう。

Q2.スプレッドシート内の全シート名（シートID）を返す関数をGASで作りたい

少し記述することは増えますが、今回もシート名、シートID 、どちらでも記述はあまり変わらないので、同じように合わせて作ってみましょう。

色々な書き方がありますが、なるべくシンプルな記述がいいですね。

どうでしょう？これも自力で作成できそうでしょうか？

↓ 一応、ここから回答

Q2.全シート名（シートID）を返す関数

コードは ↓ こんな感じ。
宣言なしで１行で書くことも出来ますが長くて見づらいんで分けました。

全シート名を返す自作関数　allSheetNames

//スプレッドシート内のシート名一覧を返す
function allSheetNames(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets().map(sheet => sheet.getName());
  return sheets;
}

全シートIDを返す自作関数　allSheetIds

//スプレッドシート内のシートID一覧を返す
function allSheetIds(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets().map(sheet => sheet.getSheetId());
  return sheets;
}

少し解説しておきましょう。

ヘルパー関数でも登場した map を使ってます。

こっちは JavaScriptの Arrayメソッドの方ですが。基本動作は結構近いものがあります。

アクティブな（今回の場合は開いている）スプレッドシート ss から getSheets でスプレッドシート内の全シートのシートオブジェクトを配列として取得します。

その配列を map メソッドで、各シートオブジェクトをシート名（シートID）に変換する処理をしています。

for や forEach のループ処理でも同様のことができますが、mapを使った方がモダンですね。 map内の記述　=> はアロー関数です。

初心者向けの GAS解説だと、「基礎を学ばせる為に」あえて面倒な記述で紹介しているサイトも多いんですが、理解は後でいいんで カッコいコードをいっぱい書いて慣れた方が上達するっていうのが mir的な考えです。
（そのせいで基礎がおろそかだったりするんですが・・・）

それぞれの自作関数で、全シート名、全シートIDが取得できました。

活用例：INDEX や FILTER でシート番号（何番目か）を指定してシート名を取得

Googleスプレッドシートの関数使いだったら、シート名一覧さえ取得できればこっちのものです。

FILTER関数を使って〇番目から〇番目のシート名を出力したり、
自由に指定したシート番号のシート名（シートID）を取り出し利用できます。

INDEXを使えば、〇番目のシート名を出力できます。
たとえば、9番目のシート名のみ欲しい場合は

=INDEX(allSheetNames(),9)

〇番目から〇番目のシート名を配列で取得したい場合は、FILTER関数です。
たとえば、2番目から5番目のシート名が欲しい場合は、

=LAMBDA(sheets,start,end,
FILTER(sheets,SEQUENCE(ROWS(sheets))>=start,
SEQUENCE(ROWS(sheets))<=end))(allSheetNames(),2,5)

LAMBDA使わなくてもOK

このようにすればOK。でも、ちょっと手軽とは言えませんね。
この辺りは後半で改善していきましょう。

活用例：リンク付きシート INDEX（目次）を作る

全シート名 / 全シートID を取得する自作関数を使って なにが出来るか？
真っ先に思いつくのが、

そのスプレッドシートの目次（リンク付きシートインデックス）作成です。

=Arrayformula(HYPERLINK("#gid="&allsheetids(),allsheetnames()))

allsheetids と allsheetnames を HYPERLINK関数と組み合わせ、さらに配列に対する処理なので Arrayformulaでくくる。

これだけで画像のようなリンク付き目次が生成できます。
これは便利！

自作関数の弱点（シート名が変わっても自動更新されない）

便利な自作関数ですが、コードを書く手間がある、動きが重いというデメリットに加え「更新されない」という弱点があります。

正しくは

自作関数は「引数に変化」があるか「再度実行」させないと更新されない
ただし、一度閉じてから一定時間経過後に開くと再計算される

という特徴（弱点）があるのです。

つまり今回の自作関数だと、シート名を変更したりシートを追加しても、しばらくは変更した結果が反映されません。

これ、一度閉じて再度開く（ページの再読み込み）をしたら更新されるんでは？と期待してもダメです。それで更新されるなら苦労しません。
※数時間経してから開けば、だいたい再計算されます。

すぐに再計算させる方法としては、一度式が入ったセルを Deleteボタンで消してから、「元に戻す」ボタン（または Ctrll + z）で式を復活させるワザがあります。この方法だと、直後に再計算され最新情報が反映されます。

ただ、これはちょっとユーザーに使わせるにはリスクがありますね。

行や列、またはセルの削除や追加で 式の入っているセル位置が変化した時も再計算されますが、これもシートが崩れるリスクがあるので避けたいです。

では、どうするか？

手っ取り早いのがチェックボックスで更新ボタンを作ってしまう方法です。

GAS 自作関数を手動更新させる方法

たとえば B1セルをチェックボックス（更新ボタン）として、今回のケースなら

=IF(B1,Arrayformula(HYPERLINK("#gid="&allsheetids(),allsheetnames())),)

IF文を使って丸ごと再計算させる式にすれば OK。

一度 B1のチェックを外し FALSEにして結果を空にしてから、再度チェックを入れてTRUEにすることで 再計算が実行されシート情報が最新のものに更新されます。

B1（チェックボックス）をそのまま引数にしちゃってもOKです。

=Arrayformula(HYPERLINK("#gid="&allsheetids(B1),allsheetnames(B1)))

これだとチェックがONでもOFFでも更新・再表示となります。

単体で allsheetnames を使っている場合なら、

=allsheetnames(B1)

これでOKです。

このB1は結果にはまったく影響を与えませんが、チェックボックスを ON/OFF切り替えるだけで、引数(B1) に変化があったことになるので再計算されます。

じゃあ、頻繁に更新される NOWや RAND を引数にしておけば「自動更新」が出来る！？って考えになりますが、残念ながらこの方法は Google側で制限しているようで エラーになります。

これ許可しちゃうと、頻繁な再計算で負荷が上がりすぎちゃうってことでしょうね。

そもそも シート名の変更はトリガーで検知（判別）できないので、自作関数の自動更新はなかなかハードルが高いのです。

比較的簡単な方法としては、 onOpen のトリガーでスプレッドシートを開いた時に毎回、強制的に式を入れ直す動きをさせて再計算って技があります。

でも、複数の自作関数を使ってる場合は一気に再計算が走るんで結構重くなりますね。

個人的にはチェックボックス再計算スイッチで、必要な時に手動で再計算させれば十分だと思います。

今回は GAS による自作関数で、

1. 式を入れたシート（アクティブなシート）のシート名
2. 式を入れたシート（アクティブなシート）のシートID
3. そのスプレッドシートの全シート名、全シートID
4. オプション（引数を追加）で取得する内容を制御してみる

こちらの「3. 全シート名、全シートIDを取得する自作関数」の作成と活用例までを紹介しました。

次回は、より実用的なシート名関数を目指して、

・式が入ってる（自分のシート）を含めるかどうか
・非表示のシートを含めるかどうか
・〇番目から〇番目までとシート範囲を指定する

といった自作関数の引数で出力条件が制御できるように、コードを加筆していきたいと思います。

合わせてタイトル画像のような、あると便利な自作関数の入力補助（オートコンプリート）表示も説明していきましょう。

■このシリーズの次の記事

この記事が気に入ったらサポートをしてみませんか？

【GAS】Googleスプレッドシート自作関数で シート情報を取得する -1

シート関数で出来そうで出来ないこと

Googleスプレッドシート関数はシート情報が取得出来ない

EXCELでは CELL関数で ファイル名、シート名が取得できる

Googleスプレッドシートでは シートIDも重要