![見出し画像](https://assets.st-note.com/production/uploads/images/89869144/rectangle_large_type_2_d9eb7a1ad83901195b090644fe76cb71.png?width=1200)
【GAS】Googleスプレッドシート自作関数で シート情報を取得する -1
これは本編のシリーズネタとは別で、旬の話題や 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関数で ファイル名、シート名が取得できる
![](https://assets.st-note.com/img/1666417755308-Jcb84QhYkf.jpg?width=1200)
Excel だと、CELL関数の 検査の種類 の中に filename があって、これを使うことで シート名入りのファイルのフルパスが取得できます。
ここから REPLACE とか FINDを使って ファイル名 や シート名だけ抜き出せば良いんですが、古典的な関数の処理なのでここは省略。
ちなみに ブック内の全シート一覧も
![](https://assets.st-note.com/img/1666419167059-Oo42jXuEu3.jpg?width=1200)
名前の定義で =GET.WORKBOOK(1) を範囲として定義し、INDEXで 1行目、2行目と取得することで、[ブック 名]シート名 が取得できます。
この処理はマクロを利用しており、このまま保存しようとすると xlsm(マクロ有効ブック)で保存しろ!と警告がでます。
実質的にはマクロを使ってるわけですが、それでも簡単ですね。
ただし、これ Web版の Excel オンラインでは出来ません。
Excelオンラインだと、CELL関数 は使えるんですが、第1引数で "filename" を使うと エラーになります。公式にも対象外と記載されてます。
![](https://assets.st-note.com/img/1666420186490-rbB9jpcybe.jpg?width=1200)
また、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 となります。
ちなみに スプレッドシートを新規作成した際の最初のシート「シート1」は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での自作関数の作り方(基本)
![](https://assets.st-note.com/img/1666422305357-Ol0DCoetwv.jpg?width=1200)
基本的には普通に エディタで GASのファンクションを作成するのと一緒です。作成した function がそのまま 自作関数として使えます。
自作関数(GAS関数)の 書き方、および特徴です。
■GAS 自作関数の特徴
return で返したものが、セルに結果として返される。
単一の値に限らず、縦横にスピらせた配列を結果として返すことができる。
(setValuesと違って、出力先のサイズ セル数を気にしなくてよい)
他のセルを書き換えたりといった「処理」は出来ない。
他のスプレッドシートを読み取ることも出来ない。
出来ることが制限されている。
GASを使ってはいるが、初回のスクリプト承認はない
引数で A1 とセルを指定した場合は、A1 セルの値 が引数に入る。
シート上では 関数名の大文字、小文字は区別されない。
※エディタ上では区別されるので重要
通常関数と違って 処理が重い。多用は禁物。
基本的には 引数に変化がない限りは再計算されない。
そのスプレッドシート内の情報を取得して、計算処理し結果を返すものだと思いましょう。詳しくは公式を参照。
自作関数とかGAS関数とか 色々な呼び方がありますが、公式に Custom Functions って書いてあるんで、「カスタム関数」って呼ぶのが正しいのかもしれません。
それでは エディタを開き コードを書いていきましょう。
アクティブな シート名 / シートID を取得する自作関数
シート名を返す関数、シートIDを返す関数、ほぼ記述は同じなんで 2つ一気に作ってみましょう。
Q1. アクティブなシートの シート名(シートID)を返す関数をGASで作りたい
とりあえず最初は、一番簡単な式を入れているシート(アクティブなシート)のシート名を返す関数、シートIDを返す関数を 作ってみましょう。
自作関数の場合は、
その式が入っているシート = アクティブシート
その式が入ってるセル = アクティブセル
という扱いになります。
関数の名前はなんでもいいんですが、わかりやすいものが良いですね。
当然ですが、すでに存在するシート関数名との重複はNGです。
サクッと作れそうでしょうか?
これは簡単すぎですかね?
↓ 一応、ここから回答
A1. アクティブなシート名(シートID)を返す関数
![](https://assets.st-note.com/img/1666423495490-JcHLbhrF8u.jpg?width=1200)
コードは 以下のような感じ。
アクティブなシート名を返す自作関数 activeSheetName
//このシート名を返す
function activeSheetName(){
return SpreadsheetApp.getActiveSheet().getName();
}
アクティブなシートIDを返す自作関数 activeSheetId
//このシートIDを返す
function activeSheetId(){
return SpreadsheetApp.getActiveSheet().getSheetId();
}
とくに解説も不要なレベルですね。
今回のようなケースは 引数無しでそのまま使えます。
アクティブなスプレッドシートを取得する必要もなく、直接アクティブなシート取得で OK。変数宣言もなくていいですね。
ぶっちゃけ開いている(自分自身の)「シート名」はあまり使いどころはありません。
取得したシートIDの方の活用例を紹介しましょう。
活用例:シートIDを使って リンク先へ飛ぶ
![](https://assets.st-note.com/production/uploads/images/89528701/picture_pc_9d449ed7a1e5da6cd56b2c20dd454567.gif?width=1200)
=HYPERLINK("#gid="&activeSheetId()&"&range="&A1,A1&"セルへのリンク")
このように、自作関数を 標準のシート関数 HYPERLINK と組み合わせることで、A1セルに入力したアドレスのセルへジャンプすることが出来ます。
もう少し応用すると
![](https://assets.st-note.com/production/uploads/images/89529494/picture_pc_f1996604147d9d88904d58388eab754c.gif?width=1200)
=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)を返す関数
![](https://assets.st-note.com/img/1666445242662-VYbIfplzLs.jpg?width=1200)
コードは ↓ こんな感じ。
宣言なしで1行で書くことも出来ますが長くて見づらいんで分けました。
全シート名を返す自作関数 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 でシート番号(何番目か)を指定して シート名を取得
![](https://assets.st-note.com/img/1666446687877-EWB8xYZUKw.jpg?width=1200)
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)
このようにすればOK。でも、ちょっと手軽とは言えませんね。
この辺りは後半で改善していきましょう。
活用例:リンク付き シート INDEX(目次)を作る
![](https://assets.st-note.com/production/uploads/images/89592446/picture_pc_c185556f250096793e1884ce8697d90f.gif?width=1200)
全シート名 / 全シートID を取得する自作関数 を使って なにが出来るか?
真っ先に思いつくのが、
そのスプレッドシートの目次(リンク付きシートインデックス)作成です。
=Arrayformula(HYPERLINK("#gid="&allsheetids(),allsheetnames()))
allsheetids と allsheetnames を HYPERLINK関数と組み合わせ、さらに配列に対する処理なので Arrayformulaでくくる。
これだけで画像のような リンク付き 目次が生成できます。
これは便利!
自作関数 の弱点 (シート名が変わっても自動更新されない)
便利な自作関数ですが、 コードを書く手間がある、動きが重い というデメリットに加え「更新されない」という弱点があります。
正しくは
自作関数は 「引数 に変化」があるか「再度実行」させないと 更新されない
ただし、一度閉じてから 一定時間 経過後に開くと再計算される
という特徴(弱点)があるのです。
つまり今回の自作関数だと、シート名を変更したり シートを追加しても、しばらくは 変更した結果が反映されません。
![](https://assets.st-note.com/production/uploads/images/89593909/picture_pc_218d64822614642bec424e875506e811.gif?width=1200)
これ、一度閉じて再度開く(ページの再読み込み)をしたら更新されるんでは?と期待してもダメです。それで更新されるなら苦労しません。
※数時間経してから開けば、だいたい再計算されます。
すぐに再計算させる方法としては、一度 式が入ったセルを Deleteボタンで 消してから、「元に戻す」ボタン(または Ctrll + z)で 式を復活させるワザがあります。この方法だと、直後に再計算され最新情報が反映されます。
ただ、これはちょっとユーザーに使わせるにはリスクがありますね。
行や列、またはセルの削除や追加で 式の入っているセル位置が変化した時も再計算されますが、これもシートが崩れるリスクがあるので 避けたいです。
では、どうするか?
手っ取り早いのが チェックボックスで更新ボタンを作ってしまう方法です。
GAS 自作関数を手動更新させる方法
![](https://assets.st-note.com/production/uploads/images/89594890/picture_pc_d011a41711fa04696370056ee16e0267.gif?width=1200)
たとえば 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側で制限しているようで エラーになります。
これ許可しちゃうと、頻繁な再計算で 負荷が上がりすぎちゃうってことでしょうね。
![](https://assets.st-note.com/img/1666509752853-BJlpDRXqvy.jpg?width=1200)
そもそも シート名の変更はトリガーで検知(判別)できないので、自作関数の自動更新はなかなかハードルが高いのです。
比較的簡単な方法としては、 onOpen のトリガーで スプレッドシートを開いた時に毎回、強制的に式を入れ直す動きをさせて再計算って技があります。
でも、複数の自作関数を使ってる場合は 一気に再計算が走るんで結構重くなりますね。
個人的には チェックボックス再計算スイッチで、必要な時に手動で再計算させれば 十分だと思います。
今回は GAS による 自作関数で、
1. 式を入れたシート(アクティブなシート)のシート名
2. 式を入れたシート(アクティブなシート)のシートID
3. そのスプレッドシートの全シート名、全シートID
4. オプション(引数を追加)で取得する内容を 制御してみる
こちらの 「3. 全シート名、全シートIDを取得する自作関数」の作成と活用例までを紹介しました。
次回は、より実用的な シート名関数 を目指して、
・式が入ってる(自分のシート)を含めるかどうか
・非表示のシートを含めるかどうか
・〇番目から〇番目まで とシート範囲を指定する
といった 自作関数の 引数で 出力条件が制御 できるように、コードを加筆していきたいと思います。
合わせてタイトル画像のような、あると便利な 自作関数の入力補助(オートコンプリート)表示も説明していきましょう。
■このシリーズの次の記事
この記事が気に入ったらサポートをしてみませんか?