最近、仕事で外部とやり取りする時も、ExcelではなくGoogleのスプレットシートでデータをやり取りする機会が増えてきました。
OSや端末を選ばず、GoogleChromeがインストールされていれば使えると言うのはとても便利です。
スプレットシートで作業をしていると同じような作業が発生する場合があり、 その都度同じ事を書くのは面倒です。
簡単な計算や参照程度であれば、関数が用意されていますが複雑な事をしようとするとその関数だけでは再現できません。
また、Excelにはない機能やExcelのスクリプト言語であるVBA
と仕様が違う所も見受けられます。
今回は簡単スクリプトを作成して、Google App Script
について理解を深めたいと思います。
実現したい事
例えば、各シートの内容を1つのシートにまとめた目次シートを作りたいとします。
目次以外のページには、タイトルと概要が入力されています。
同じフォーマットで作られており、タイトルと概要がそれぞれ同じ位置に配置されています。
その内容を一覧にしたいです。
実装方針
実装する上で次の2つの機能が必要です。
- シート番号からシート名を取得
- シート番号からシートを指定して、且つ、セルの値を取得
この2つを実装するためのスプレット用の関数はおそらく存在しません。
(もしかしたら私が把握できていないだけかもしれません。)
そのため、スクリプトで機能を拡張する必要があります。
Google Apps Scriptとは?
Googleが開発したApp Scriptは、G Suiteでサービスのカスタマイズ、拡張を行うもことを目的としたプログラム言語です。
通称GAS
と言われます。
このページでは以降、GAS
と略します。
Google Apps Script は JavaScript ベースのスクリプト言語で、ドキュメント、スプレッドシート、スライド、フォームなどの G Suite サービスをカスタマイズ、拡張できます。インストール作業は不要です。
ブラウザ内で動作するコードエディタが用意されており、スクリプトは Google のサーバーで実行されます。
Googleアカウントさえあれば無料で利用できます。
スクリプトの開発方法
スクリプトを書くにはスクリプトエディタを起動します。
トップメニューのツール
> スクリプトエディタ
を選択してエディタ画面を開きます。
実装方法
シート番号からシート名を取得
目次以外のページのタイトルセルをシート名にするために、今表示しているページ名を参照します。
下記のようなソースコードを作成します。
/// シート名を取得 /// @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
の引数は省略しても問題ありません。
これで1ページ分作成できましたので、以降のページも同じように関数を指定します。
シート番号からシートを指定して、且つ、セルの値を取得
次に前項で作成したシートからタイトルと概要を取得します。
前項で特定のシートを取得する処理はできましたので、その処理で取得したシートから特定のセルを指定する方法を実装します。
前項で書いた処理の次に下記のコードを追加します。
// 省略 /// 指定したシートのセル番号を取得 /// @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(); }
これで、getRangeValue()
からデータを取得できるようになります。
シートの番号はA列の番号を参照するとします。
目次の列名 | 目次以外のページで記載しているセル | セルに記載する構文 |
---|---|---|
タイトル | B1 | =getRangeValue("B1", A2) |
概要 | B2 | =getRangeValue("B2", A2) |
これで1ページ分の目次が出来上がりました。
あとは、他のページ分処理をコピーします。
先ほど追加したセルを選択した状態で右下の■
をコピーしたいセルまでドラッグします。
ドラッグすると、関数getRangeValue()
の第2引数がコピーしたセルに紐づいて変わって動的に実行されます。
これで目次ページの完成です。
以上です。
頻繁に出て来る作業ですが、関数だけでは解決できないケースは他にもあると思います。
Excelの要領で解決しようとして、違いに戸惑うかもしれません。
この記事が初めて実装される方の手引きになっていただければ幸いです。