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

mir

2022年11月12日 17:31

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

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

LAMBDAやXLOOKUPはうれしいけど、Excelからの輸入じゃなくてオリジナル新関数でこれ欲しかったよ～ と願うシート名やシートIDを取得する関数を GAS で自作しています。

前回は、1つのスプレッドシート内にある 全シート名、全シートIDを取得する関数の作成まで出来ました。

シート名/シートIDを取得する関数をブラッシュアップしよう

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

今回は 4. オプション（引数を追加）で取得する内容を制御してみる にチャレンジして、より便利な自作関数にブラッシュアップしたいと思います。

1つの式でシート名、シートIDを返す自作関数

シート名の取得
シートIDの取得

前回は別々の関数として作成していましたが、まずはこれを1つにまとめてみましょう。

今回はもうQA方式でなく、順番にコード検証・機能追加をしていきます。

シート名、シートＩＤ両方返す自作関数

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

関数名はなんでもいいんですが、とりあえず sheetNames2 としておきましょう。

コード内の const ではなく let sheets と、let を使ってる箇所は理由はわかりますか？その日の気分じゃないですよ。

次の行で再代入するからなんですが、この辺りの基礎的な補足などプログラミング系の記事はどこまで（どこから）説明したらよいか悩ましいところです。

なるべくわかりやすく、でも基本ばかりにもならないように、よそ様のページも参考にしつつ書いていきましょう。

前回も登場した map + アロー関数 で、sheets（全シートのシートオブジェクト配列）内の個々の sheet を

[sheet.getName(), sheet.getSheetId()]

とすることで、シートオブジェクトをシート名、シートIDという（スプレッドシート的には横並びの）配列に変換しています。これが各シート毎に繰り返されるイメージ。

GASで配列をスプレッドシートに書き込む（setValues する）時は、二次元配列にしたり、書き込み先の範囲を getRange で配列と同じサイズで取得する必要があったりと、結構気にかけるポイントが多いんですが、自作関数だとその辺りをあまり意識せず return で書き出せちゃうんで楽ですね。

1つの式でシート名、シートIDが返せましたが、ケースによってはどちらかのみ使いたいという時もあります。

まずは、この制御を引数でコントロールしてみましょう。

swtich文で出力モードを制御してみる

出力の切り替えですが、sheetNames2() に引数 mode を渡して sheetNames2(mode) として切り替えるようにしてみましょう。

■mode で以下のように切り替える
1. シート名のみ
2. シートIDのみ
0（もしくは空欄）. シート名、シートIDの両方（デフォルト値）

コードはこんな感じ

function sheetNames2(mode=0){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheets = ss.getSheets();

  //モードを確認し出力を制御 
  switch (mode){
    case 1:
    sheets =sheets.map(sheet => [sheet.getName()]);//シート名のみ
    break;

    case 2:
    sheets =sheets.map(sheet => [sheet.getSheetId()]);//シートIDのみ
    break;

    default:
    sheets =sheets.map(sheet =>  [sheet.getName(),sheet.getSheetId()]);//両方
  };

  return sheets;
}

switch文による分岐で caseごとに map処理を記述してみましたが、もっといい方法ないんかな？（処理を別関数にしてもいいんですが）

記述的にすっきりさせる方法はあるけど、使わないのに両方取得したり、map 内でswitchすると無駄が多いし・・・。

switch使う時は、ケースごとの break 入れ忘れに注意です。

VBAと違って型の指定がないのが、良くも悪くも javascriptって感じです。

一応引数のデフォルト値を０ってことで、sheetNames2(mode=0)　としてるけど、今回の場合は switch の最後の default: 処理で1，2以外は全部拾っちゃいますねｗ

まぁここは別に問題ないでしょう。

とりあえずこの式をベースにして、他の条件の引数を追加していきましょう。

対象とするシートの条件を引数で制御してみる

どんな条件（引数）が必要かを考える

出力対象とするシートを絞り込む際に、どんな条件で絞り込めたら便利かを考えてみました。

・アクティブシート（式を入れたシート）を含めるか否か
・非表示のシートを含めるか否か
・シート〇番目から〇番目といった範囲設定ができる

この辺りの機能があると良さそうですね。

式が入っているシート（アクティブシート）の除外は、他のシートの値を合計するサマリーシートで必要になりそう。

非表示のシート除外は前回のような全シートINDEXを生成する際に、非表示のシートを含めたくない場合もあるでしょう。

あと、シート〇番目から〇番目といった範囲指定があると、いわゆる EXCELでいうところのシート範囲を指定した 串刺し計算 に使えるかなと。

自作関数の Goalイメージとしては、

=sheetNames3(mode, self, hidden, start, end)
mode 出力モード　0 両方、1 シート名、 2 シートID (省略時は 0）
self このシートを除外するか？ trueで除外 (省略時は false）
hidden 非表示シートを除外するか？ trueで除外 (省略時は false）
start 開始シート番号 (省略時は1)
end 終了シート番号（省略時は末尾）

hidden true（非表示シート除外）時は、表示されているシート内での番号
self は trueとした場合もシート番号には影響しない

こんな式を作ってみましょう。

※引数は条件を適用する時を true として、省略時を false扱いとした方が楽です。

引き続きQA方式ではなく、ステップを踏んで解説していきます。

self このシートを除外するか？

mode は既に sheetNames2 の式で実装済みなので、self 条件から組み込んでいきましょう。

const ss = SpreadsheetApp.getActiveSpreadsheet();
const activeSheet = ss.getActiveSheet();

式の入っているシートの取得は、前回もやりましたね。

既にアクティブなスプレッドシートを取得し ss としているので、 ss.getActiveSheet() でアクティブなシート（この式が入っているシート）が取得できます。

let sheets = ss.getSheets();

全シート（sheets）から、アクティブシートを除外すればよいのですが、どちらも シートオブジェクトなのでこのままでは比較ができません。

シートオブジェクトって概念は、なかなかわかりにくいかもしれませんが、そのシートを操作したり、そのシートの情報を取得したりする為のシートそのもの（塊）という理解でよいかなと思います。

「いつも隣にITのお仕事」さんでも少しオブジェクトについて触れているので、そちらもぜひご確認を。

今回のケースでは最終的にシート名やシートIDを返すので、「シート名」もしくは「シートID」というユニークな文字列に変換してから比較することで、アクティブシートを除外すれば良いわけです。

というわけで、まずはこんな感じにしてみました。

function sheetNames3(mode=0,self){
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  //全シートを取得
  let sheets = ss.getSheets()

  //モードを確認し出力を制御
  switch (mode){
    case 1:
    sheets =sheets.map(sheet => [sheet.getName()]);//シート名のみ
    break;

    case 2:
    sheets =sheets.map(sheet => [sheet.getSheetId()]);//シートIDのみ
    break;

    default:
    sheets =sheets.map(sheet =>  [sheet.getName(),sheet.getSheetId()]);//両方
  }; 

  //selfフラグが true の場合は 式が入っているシートを除外
  const activeSheet = mode == 2? ss.getActiveSheet().getSheetId() : ss.getActiveSheet().getName();
  sheets = self? sheets.filter(sheet => sheet[0] != activeSheet) : sheets ;


  return sheets;
}

アクティブシート除外の部分の記述をシンプルにするために、switch文での mapによる変換を 全てのケースで [ ] をつけて配列で返す形にしました。

後半のself フラグの条件処理 2行は、どちらも 三項演算子で記述しています。

const activeSheet = mode == 2? ss.getActiveSheet().getSheetId() : ss.getActiveSheet().getName();

まず、この部分で activeSheet という変数に mode が２の時だけアクティブシートのシートIDを、それ以外の時はアクティブシートの シート名をセットしています。

  sheets = self? sheets.filter(sheet => sheet[0] != activeSheet) : sheets ;

その上で、この部分 self? つまり self がture なら、 filterで sheets(配列）をアクティブシートと一致しない（ != で一致しない）ものだけに絞りこみ、 selfがtureでないならそのまま sheets を返す、としています。

filterメソッドはアロー関数を組み合わせた場合は、後ろの式で true となる要素だけに絞り込まれます。感覚的にはシート関数のFILTERと同じようなイメージ。

mode によって以下のような中身・比較になっていると思ってください。
※配列のindex番号（添え字）は 0 開始です。

mode が1だったら
sheets ・・・[[シート名1],[シート名2],[シート名3]…] という二次元配列
sheet ・・・ sheetsの要素を1つずつ取り出したもの（1つ目なら [シート名1]）※この時点ではまだ配列
sheet[0] ・・・　シート名1　※配列 [シート名1] の1つ目の要素（中身）
activeSheet　・・・　アクティブシート名
sheet[0] != activeSheet　で各シートのシート名とアクティブシート名を比較して != （一致しない）ものに絞り込む

以下、重複する説明は省略

mode が2だったら
sheets ・・・[[シートID1],[シートID2],[シートID3]…]
sheet ・・・　上記同様（1つ目なら [シートID1] ）
sheet[0] ・・・　シートID1
activeSheet　・・・　アクティブシートID
sheet[0] != activeSheet　で各シートのシートIDとアクティブシートIDを比較して != （一致しない）ものに絞り込む

mode0 デフォルトなら
sheets ・・・[[シート名1,シートID1],[シート名2,シートID2],[シート名3,シートID3]…]
sheet ・・・　上記同様（1つ目なら [シート名1,シートID1] ）
sheet[0] ・・・　シート名1 ※配列 [シート名1,シートID1] の1つ目の要素（つまり先頭の要素シート名1の方）
activeSheet　・・・　アクティブシート名
sheet[0] != activeSheet　で各シートのシート名とアクティブシート名を比較して != （一致しない）ものに絞り込む

これを保存してシート上で試してみると、第2引数の self に trueを入れたときにアクティブなシート（この場合は1枚目の「おシート」）が除外されているのがわかります。

アクティブシートの抽出制御ができました。

hidden 非表示シートを除外するか？

次に非表示シートの除外部分をコードに加えていきましょう。

シートが非表示かどうかを判別するには、isSheetHidden というメソッドがあるので、これを使えば良さそう。

シートオブジェクトに対して使うメソッドなので、シート名やシートIDに変換する前に記述した方がよいですね。

いっそのこと sheets に格納するタイミングで filter処理しちゃいましょう。

function sheetNames3(mode=0,self,hidden){
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  //全シートを取得（非表示 trueの時は絞り込み）
  let sheets = hidden? ss.getSheets().filter(sheet => !sheet.isSheetHidden()) : ss.getSheets();

  //モードを確認し出力を制御
  switch (mode){
    case 1:
    sheets =sheets.map(sheet => [sheet.getName()]);//シート名のみ
    break;

    case 2:
    sheets =sheets.map(sheet => [sheet.getSheetId()]);//シートIDのみ
    break;

    default:
    sheets =sheets.map(sheet =>  [sheet.getName(),sheet.getSheetId()]);//両方
  }; 

  //selfフラグが true の場合は 式が入っているシートを除外
  const activeSheet = mode == 2? ss.getActiveSheet().getSheetId() : ss.getActiveSheet().getName();
  sheets = self? sheets.filter(sheet => sheet[0] != activeSheet) : sheets ;


  return sheets;
}

変更したのは

//全シートを取得（非表示 trueの時は絞り込み）
  let sheets = hidden? ss.getSheets().filter(sheet => !sheet.isSheetHidden()) : ss.getSheets();

ここだけですね。同じく三項演算子による処理です。

hidden? で引数 hidden がtrueなら、

ss.getSheets().filter(sheet => !sheet.isSheetHidden())

filter でシートを表示されているシートだけに絞り込む（非表示シートを除外する）。という処理をしています。

isSheetHidden は、シートが非表示だと true を返すので、 !をつけて反転させています。 !は論理演算子ってやつでシート関数だと NOT みたいなものですね。

true ⇔ false を切り替えるのに使ってます。

実際に第3引数 hiddenを true にすると 非表示シートが除外されているのがわかります。

非表示シートも制御ができました。

start /end で抽出範囲を指定する

最後に開始シート番号、終了シート番号の引数で、抽出するシート範囲を制御できるようにしましょう。

function sheetNames3(mode=0,self,hidden,start,end){
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  //全シートを取得（非表示 trueの時は絞り込み）
  let sheets = hidden? ss.getSheets().filter(sheet => !sheet.isSheetHidden()) : ss.getSheets();

  //モードを確認し出力を制御
  switch (mode){
    case 1:
    sheets =sheets.map(sheet => [sheet.getName()]);//シート名のみ
    break;

    case 2:
    sheets =sheets.map(sheet => [sheet.getSheetId()]);//シートIDのみ
    break;

    default:
    sheets =sheets.map(sheet =>  [sheet.getName(),sheet.getSheetId()]);//両方
  };

  //開始値、終了値を定義し sliceで切り取り
  start = start || 1;
  end = end || sheets.length ;
  sheets = sheets.slice(start-1,end); 

  //selfフラグが true の場合は 式が入っているシートを除外
  const activeSheet = mode == 2? ss.getActiveSheet().getSheetId() : ss.getActiveSheet().getName();
  sheets = self? sheets.filter(sheet => sheet[0] != activeSheet) : sheets ;

  return sheets;
}

シート番号は

「非表示シートを除外」とした場合は、非表示シートを含めずカウント
「このシートを除外」としてもカウントにはアクティブシートを含める

こんなルールにしました。

非表示シートは 番号に含めない方がわかりやすいですが、アクティブシートは番号から除外すると逆にわかりにくくなるからです。

というわけで、範囲部分に切り取る処理は、self フラグ処理の手前、switchを抜けたあとに入れています。

  //開始値、終了値を定義し sliceで切り取り
  start = start || 1;
  end = end || sheets.length ;
  sheets = sheets.slice(start-1,end);

この部分ですね。

ここはせっかくなんで記述で 論理和 (||) の短絡評価を使った記述にしています。

自作関数として利用する際、引数の start , end を省略した場合は、書き方によって以下の違いがあります。

start = start || 1;
end = end || sheets.length ;

未記入 =sheetNames3(1,true,true, , )
※カンマで区切られた枠はあるけど中身がない状態
→ GAS上ではstart,end は 空文字扱い
未設定 =sheetNames3()
※そもそもカンマで区切られた枠がない状態
→ GASではstart,end は underfind

|| を使った記述部分で、左側 start や end に数値が入っていればその数値がそのまま使われ、 start や end が ''(空文字)、null、underfind の時は false扱いとなり、右側が start, end に代入されるという処理をしています。

ショートハンド（コード記述の簡略）として使われる書き方です。

start,end の省略時（空白、underfind) は、

start = start || 1;
end = end || sheets.length ;

start 開始シート・・・1 （先頭のシート）
end 終了シート・・・ sheets.length シート配列の要素の数 = シートの枚数（最後のシートのシート番号）

としています。
※シート枚数は hidden がtrueの場合は表示されいているシート枚数

sheets = sheets.slice(start-1,end);

最後の配列の切り取りは slice を使用。ここで配列上の 1番目は index 0 なので、staart -1 としています。

一方 endは -1する必要はありません。日本人的感覚だと start が1、endが5なら、シートの1枚目から5枚目までという意味合いで end を最後の要素として含めたくなりますが、 slice の end はその手前までが切り取り範囲で、end 自体は含まれないのです。

よって、endが 5 だった場合は手前の index 4までが対象となりますが、0スタート換算なのでちょうど 1~5枚目が対象となるわけです。

どちらも未入力の場合は、start が 1、endがシート枚数(12) になるので、

sheets.slice(0,12) は シートINDEX 0～11まで、つまり全てのシートを返すことになり、範囲選択なしと同じになります。

全ての引数による制御をコーディングできました。
とりあえず完成です。

自作関数の入力時に説明を表示させる

自作関数は完成しましたが、引数が多いし制御ルールも複雑なので、式を入力する際の説明が欲しいところ。

実はこれ GAS上の記述で出来るんです。

リファレンスの Custom Functions の Autocomplete(オートコンプリート）ってところに記載があります。

こちらの例にならって、コードに追記してみましょう。

オートコンプリート部分を追記する

説明を追記した、今回のシート名/シートID 自作関数の最終形となります。

/**
 * シート名,シートIDを返す GAS関数。 ※非表示シート除外の際はシート番号に含めない
 * @param {2} mode 出力モード 0 両方、1 シート名のみ、 2 シートIDのみ (省略時は 0）
 * @param {true} self このシートを除外するか？ (trueで除外、省略時は false）
 * @param {true} hidden 非表示シートを除外するか？ (trueで除外、省略時は false）
 * @param {1} start 開始シート番号 (省略時は1)
 * @param {2} end 終了シート番号（省略時は末尾）
 * @return {シート名} シート名/IDの並びの配列
 * @customfunction
 */
function sheetNames3(mode=0,self,hidden,start,end){
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  //全シートを取得（非表示 trueの時は絞り込み）
  let sheets = hidden? ss.getSheets().filter(sheet => !sheet.isSheetHidden()) : ss.getSheets();

  //モードを確認し出力を制御
  switch (mode){
    case 1:
    sheets =sheets.map(sheet => [sheet.getName()]);//シート名のみ
    break;

    case 2:
    sheets =sheets.map(sheet => [sheet.getSheetId()]);//シートIDのみ
    break;

    default:
    sheets =sheets.map(sheet =>  [sheet.getName(),sheet.getSheetId()]);//両方
  };

  //開始値、終了値を定義し sliceで切り取り
  start = start || 1;
  end = end || sheets.length ;
  sheets = sheets.slice(start-1,end); 

  //selfフラグが true の場合は 式が入っているシートを除外
  const activeSheet = mode == 2? ss.getActiveSheet().getSheetId() : ss.getActiveSheet().getName();
  sheets = self? sheets.filter(sheet => sheet[0] != activeSheet) : sheets ;

  return sheets;
}

シート側で自作関数入力時に表示される説明とコード側で記述するドキュメンテーションの対比は以下のようになっています。

このコードで保存し、シート側で自作関数 sheetName3 を入力してみると

入力の途中で関数候補に登場し、さらにヘルプに設定した説明が表示されました。

これは便利ですね。

細かい設定部分や意味合いについては例によって 「いつも隣にITのお仕事」さんが参考になります。

自作関数における @return の部分は、どこにも表示されないんで意味があるかわかりませんが・・・。（mirが理解してないだけ？）

完成した自作関数の動作チェック

実際に動かしてみましょう。わかりやすいように、全て引数はセル参照にしています。

mode: 出力モード切替

self: 自分のシートを含める/含めない

hidden: 非表示シートを含める/含めない

start / end: シート範囲設定

イメージ通りの動きになりました。
完成です！

リンク付きシート INDEX（目次）にどう使うか？

前回作成した自作関数と HYPERLINK関数を組み合わせた シートINDEX（全シート目次）は、今回ブラッシュアップした関数だとどう作ればよいでしょうか？

前回だとシートINDEXはシート名を返す関数、シートIDを返す関数が分かれていたので2つの関数を使って作成しましたが・・・。せっかく1つに集約したのでこの sheetNames3 を 1回使うだけで 実現したいですね！

ここで登場するのがLAMBDAです。

新リンク付きシート INDEX（目次）式

=LAMBDA(sheets,ARRAYFORMULA(HYPERLINK("#gid="&INDEX(sheets,,2),INDEX(sheets,,1))))(sheetNames3(0,true))

LAMBDAで sheetNames3(0,true) から返されるシート名、シートIDの 2列になっている配列を sheetsと置いてしまうのが良いでしょう。

自シート（目次シート）を含めないように第2引数の self を trueとしています。非表示シートも除外したい場合は第3引数にも true を入れておきましょう。

シート名、シートID はそれぞれ sheets の1列目、2列目になるので、ここは INDEXで取得すれば、あとは前回と同じ HYPERLINK関数と ARRAYFORMULAで OKです。

自作関数は負荷が大きいので、なるべく利用数は絞っておきたいですね。

スマホ（アプリ版）での動作を確認する

おまけです。スマホアプリ版でも稼働することを確認しておきましょう。

上記の通り残念ながらアプリ版だと最後に記述した 説明（オートコンプリート）部分は表示されませんが、正しく引数を入れれば機能していることがわかります。

処理が少し重い気がしますが、GAS自作関数がスマホでも使えるのは便利ですね。

前回の自作関数をアップデートして、引数で出力を制御できる自作関数が完成しました。これでシート情報（シート名、シートID）の取得はOKですね。

次回は自作関数シリーズの最後、完成した sheetNames3 を既存のシート関数と組み合わせて、具体的に何ができるか？ を検証していきます。

シート指定の串刺し計算や、全シート検索、さらに 複数シートの範囲結合など、GASが続いたので久々にシート関数 LAMBDA関数（REDUCE）や Arrayformula、QUERY関数などフル活用で色々検証してみましょう。

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

シート名/シートIDを取得する関数をブラッシュアップしよう

1つの式でシート名、シートIDを返す自作関数

シート名、シートＩＤ両方返す自作関数

swtich文で出力モードを制御してみる

対象とするシートの条件を引数で制御してみる

どんな条件（引数）が必要かを考える

self このシートを除外するか？

hidden 非表示シートを除外するか？

start /end で抽出範囲を指定する

自作関数の入力時に説明を表示させる

オートコンプリート部分を追記する

完成した自作関数の動作チェック

mode: 出力モード切替

self: 自分のシートを含める/含めない

hidden: 非表示シートを含める/含めない

start / end: シート範囲設定

リンク付きシート INDEX（目次）にどう使うか？

新リンク付きシート INDEX（目次）式

スマホ（アプリ版）での動作を確認する

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

いいなと思ったら応援しよう！

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

シート名/シートIDを取得する関数をブラッシュアップしよう

1つの式で シート名、シートIDを返す自作関数

シート名、シートＩＤ 両方返す自作関数

swtich文で 出力モードを制御してみる

対象とするシートの条件を 引数で制御してみる

どんな条件（引数）が必要かを考える

self このシートを除外するか？

hidden 非表示シートを除外するか？

start /end で抽出範囲を指定する

自作関数の入力時に説明を表示させる

オートコンプリート部分を追記する

完成した 自作関数の動作チェック

mode: 出力モード切替

self: 自分のシートを 含める/含めない

hidden: 非表示シートを 含める/含めない

start / end: シート範囲設定

リンク付き シート INDEX（目次） にどう使うか？

新 リンク付き シート INDEX（目次） 式

スマホ（アプリ版）での動作を確認する

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

いいなと思ったら応援しよう！

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

1つの式でシート名、シートIDを返す自作関数

シート名、シートＩＤ両方返す自作関数

swtich文で出力モードを制御してみる

対象とするシートの条件を引数で制御してみる

完成した自作関数の動作チェック

self: 自分のシートを含める/含めない

hidden: 非表示シートを含める/含めない

リンク付きシート INDEX（目次）にどう使うか？

新リンク付きシート INDEX（目次）式