Spreadsheetデータの選択、挿入、削除、コピー、移動、ソート&ちょっとした高速化のコツ:Excel VBAプログラマーのためのGoogle Apps Script入門(7)(1/3 ページ)
Googleが提供するGoogle Apps Script(GAS)のプログラミングで、Google Apps(主にスプレッドシート)を操作する方法を解説していく連載。今回は、Spreadsheetデータの選択、挿入、削除、コピー、移動、ソートに使うメソッドの使い方などを解説する。
本連載「Excel VBAプログラマーのためのGoogle Apps Script入門」では、Googleが提供する「Google Apps Script」(GAS)のプログラミングで、「Google Apps」を操作する方法を「Google Sheets」(スプレッドシート)を中心に解説していく。
今回はGASのデータ処理をメインに解説していく。データを挿入したり、削除したり、ソートしたり、データを別なセルにコピーしたり、移動したりする処理を解説する。これらの処理は、日常の業務においては、常に必要とされ、現場で行われている作業なので、すぐに役立つだろう。
また、大量のデータを扱うとなると、コードの実行が遅くなってしまいがちだ。これを解消するちょっとしたコツも紹介する。
今回は、図1のようにデータを入力したシートを使う。
また、「ツール」→「スクリプトエディタ」と選択して、スクリプトエディタを開いておこう。「コード.gs」ファイルの内容が表示されている。この「コード.gs」内に、以降のコードを書いて実行していく。
セル範囲を選択するsetActiveRangeメソッド
まずは、セル範囲を選択する方法について見ていこう。リスト1のコードを使う。
function mySetActiveRangeFunction() { var sheet=SpreadsheetApp.getActiveSheet(); var selectRange=sheet.getRange("A3:D10"); SpreadsheetApp.setActiveRange(selectRange); }
getRangeメソッドで、選択する範囲を指定して、変数selectRangeに格納する(3行目)。選択した範囲を、「setActiveRange」メソッドでアクティブにする(4行目)。
これで、指定した範囲が選択できる。実行すると図2のようになる。
Excel VBAでは、さまざまな方法で範囲を指定してSelectメソッドを使うことで「選択」ができる。
データの挿入
次は、データの挿入についてだ。下記のメソッドのいずれかを使い、空の行か列を増やした後、増やした所に値を入力する。
空の行か列を増やすメソッドの書式
- 下方向に1行を追加する
{シートオブジェクト}.insertRowAfter({行番号}) - 上方向に1行を追加する
{シートオブジェクト}.insertRowBefore({行番号}) - 下方向に複数行を追加する
{シートオブジェクト}.insertRowsAfter({行番号},{増やす行数}) - 上方向に複数行を追加する
{シートオブジェクト}.insertRowsBefore({行番号},{増やす行数}) - 上方向に複数行を追加する
{シートオブジェクト}.insertRows({行番号},{増やす行数}) - 右方向に1行を追加する
{シートオブジェクト}.insertColumnAfter({行番号}) - 左方向に1行を追加する
{シートオブジェクト}.insertColumnBefore({行番号}) - 右方向に複数列を追加する
{シートオブジェクト}.insertColumnsAfter({列番号},{増やす列数}) - 左方向に複数列を追加する
{シートオブジェクト}.insertColumnsBefore({列番号},{増やす列数}) - 左方向に複数列を追加する
{シートオブジェクト}.insertColumns({列番号},{増やす列数})
なお、「insertRows」「insertColumns」で{増やす行数}や{増やす列数}を省略すると、1つだけ増えることになる。
ここでは、insertRowAfterをリスト2のコードのように使うことにする。
function myInsertRowFunction() { var sheet=SpreadsheetApp.getActiveSheet(); sheet.insertRowAfter(5); sheet.getRange(6,1).setValue("霧隠才蔵"); sheet.getRange(6,2).setValue("愛媛県松山市伊予郡砥部町"); sheet.getRange(6,3).setValue("089-956-1234"); sheet.getRange(6,4).setValue("男性"); }
insertRowAfterメソッドで5行目の後ろ、つまり6行目にデータを挿入することを宣言する(3行目)。getRangeで、値を入力する「行」と「列」を指定して、setValueメソッドで6行目の1〜4列目にそれぞれ「氏名」「住所」「電話番号」「性別」を入力する(4〜7行目)。
図1に対して、リスト2を実行した結果は図3のようになる。
データを配列にして、setValuesメソッドで一気に入力することで高速化
なお、リスト3のようにvaluesという配列に値をセットしておき、getRangeで6行目の1〜4列目を一気に指定して、setValuesメソッドを使い配列でデータを入力することも可能だ。
このように、呼び出すメソッドの数を減らすことで、コードの行数を減らし、実行速度を上げることができる。基本的にローカルPCで使うVBAに比べて、GASでは特に通信量や速度、回数が処理速度に影響するので、実行するメソッドの数を減らすことが重要だ。
function myFunction() { var sheet=SpreadsheetApp.getActiveSheet(); var values = [ [ "霧隠才蔵","愛媛県松山市伊予郡砥部町","089-956-1234","男性" ] ]; sheet.insertRowAfter(5); sheet.getRange(6,1,1,4).setValues(values); }
Excel VBAとの違い
データの挿入は、Excel VBAにおいてはInsertメソッドを使う。
挿入する方向は、VBAでは引数で指定するが、GASだとメソッド名で指定するという違いがある。
またVBAでは、Range/Rows/Columnsオブジェクトで複数行/列の範囲を指定することで、挿入する位置と行/列数が決まるが、GASではメソッドの引数で挿入する位置と行/列数が決まるという違いがある。
さらにVBAでは、書式(ここでは、セルの色やフォントなど表現形式)をコピーすることも、「CopyOrigin」という引数で行えてしまう。GASで書式をコピーするには、連載第3回で紹介したようなメソッドで、書式を指定するコードを別途追加する必要があるだろう。
使い方は難しいかもしれないが、VBAの方がよりきめ細かい処理が行える。
Copyright © ITmedia, Inc. All Rights Reserved.
関連記事
- Excelマクロ/VBAリファレンス用途別・キーワード別一覧超まとめ
- 初心者のためのJavaScript入門
- ピボットテーブルとは何か──「そもそも、何をする機能か」を理解する
Excelを通じて「ピボットテーブル」の基礎を学び、データ分析を実践するまでを習得する本連載。初回はピボットテーブルの基礎と、「どんなことができるのか」を解説する。