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

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

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

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

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

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

今回は 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) として切り替えるようにしてみましょう。

コードはこんな感じ

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イメージとしては、

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

※引数は 条件を適用する時を 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 開始です。

これを保存してシート上で試してみると、第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 や end に数値が入っていれば その数値がそのまま使われ、  start や end が ''(空文字)、null、underfind の時は false扱いとなり、右側が start, end に代入されるという処理をしています。

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

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

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

最後の 配列の切り取りは 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で  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関数 などフル活用で 色々検証してみましょう。

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