【Google App Script】スプレットシートで目次ページの作る方法とGoogle App Scriptの始め方

最近、仕事で外部とやり取りする時も、ExcelではなくGoogleのスプレットシートでデータをやり取りする機会が増えてきました。

gsuite.google.co.jp

OSや端末を選ばず、GoogleChromeがインストールされていれば使えると言うのはとても便利です。

スプレットシートで作業をしていると同じような作業が発生する場合があり、その都度同じ事を書くのは面倒です。

簡単な計算や参照程度であれば、関数が用意されていますが複雑な事をしようとするとその関数だけでは再現できません。
また、Excelにはない機能やExcelのスクリプト言語であるVBAと仕様が違う所も見受けられます。

今回は簡単スクリプトを作成して、Google App Scriptについて理解を深めたいと思います。

実現したい事

例えば、各シートの内容を１つのシートにまとめた目次シートを作りたいとします。

f:id:nakahashi_h:20200609031434p:plain

目次以外のページには、タイトルと概要が入力されています。
同じフォーマットで作られており、タイトルと概要がそれぞれ同じ位置に配置されています。

f:id:nakahashi_h:20200609031509p:plain

その内容を一覧にしたいです。

実装方針

実装する上で次の２つの機能が必要です。

シート番号からシート名を取得
シート番号からシートを指定して、且つ、セルの値を取得

この２つを実装するためのスプレット用の関数はおそらく存在しません。
（もしかしたら私が把握できていないだけかもしれません。）

support.google.com

そのため、スクリプトで機能を拡張する必要があります。

Google Apps Scriptとは？

Googleが開発したApp Scriptは、G Suiteでサービスのカスタマイズ、拡張を行うもことを目的としたプログラム言語です。
通称GASと言われます。
このページでは以降、GASと略します。

developers.google.com

Google Apps Script は JavaScript ベースのスクリプト言語で、ドキュメント、スプレッドシート、スライド、フォームなどの G Suite サービスをカスタマイズ、拡張できます。インストール作業は不要です。
ブラウザ内で動作するコードエディタが用意されており、スクリプトは Google のサーバーで実行されます。

Googleアカウントさえあれば無料で利用できます。

スクリプトの開発方法

スクリプトを書くにはスクリプトエディタを起動します。
トップメニューのツール > スクリプトエディタ を選択してエディタ画面を開きます。

f:id:nakahashi_h:20200609032112p:plain

実装方法

シート番号からシート名を取得

目次以外のページのタイトルセルをシート名にするために、今表示しているページ名を参照します。
下記のようなソースコードを作成します。

/// シート名を取得
/// @parm sheetNum （左から数えた）対象のシート番号
function getSheetName(sheetNum) {
  var sheet = getSheet(sheetNum)
  
  // シートが存在しないとエラーになるため、Nullを返す
  if(sheet == null) {
    return null;
  }
  
  // シートからシート名を取得
  return sheet.getName();
}

/// シート並び順から指定の番号のシートを取得  
/// @parm sheetNum （左から数えた）対象のシート番号
function getSheet(sheetNum) {
  var sheet;
  
  if(sheetNum != null) {
    // 引数を指定されていれば、（左から数えて）指定された番号のシートを変数に代入
    sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[sheetNum];
  } else {
    // 引数を指定されていなければ、現在選択されているシートを変数に代入
    sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  } 
  
  return sheet;
}

シートのセルからスクリプトの関数を呼ぶには、「=関数名(引数)」で呼び出せますので次のように指定します。
目次ページ以外に関しては、現在開いているシートのシート名を指定したいので、getSheetNameの引数は省略しても問題ありません。

f:id:nakahashi_h:20200609032130p:plain

これで１ページ分作成できましたので、以降のページも同じように関数を指定します。

シート番号からシートを指定して、且つ、セルの値を取得

次に前項で作成したシートからタイトルと概要を取得します。
前項で特定のシートを取得する処理はできましたので、その処理で取得したシートから特定のセルを指定する方法を実装します。

前項で書いた処理の次に下記のコードを追加します。

// 省略

/// 指定したシートのセル番号を取得
/// @parm rangeKey     参照したいセルを指定（例：A1）
/// @parm sheetNum （左から数えた）対象のシート番号
function getRangeValue(rangeKey, sheetNum) {
  // シートを取得
  var sheet = getSheet(sheetNum);
  
  // シートが存在しないとエラーになるため、Nullを返す
  if(sheet == null) {
    return null;
  }
  
  // シートから指定のセルを参照して、その値を返す
  var range = sheet.getRange(rangeKey);
  return range.getValue();
}