[GAS]セル範囲・最終行取得や並べ替え、データ追加・削除の方法 -Sheetクラス-
Google Apps Script (GAS) でセル範囲・最終行の取得や並び替え、データの追加・削除などの方法を説明します。
Sheetクラスのメソッドを利用した手順について説明します。
Google Apps Scriptの基本的な使い方については下記をご覧ください。
- GASによるスプレッドシート操作の概要
- このシートの指定したセル範囲を取得する
- 行・列番号で指定した位置のセル範囲を取得する：getRange(row, column)
- 行・列番号で指定した位置から指定した数の行を取得する：getRange(row, column, numRows)
- 行・列番号で指定した位置から指定した数の行と列を取得する：getRange(row, column, numRows, numColumns)
- A1表記またはR1C1表記で指定した位置のセル範囲を取得する：getRange(a1Notation)
- A1表記またはR1C1表記で指定した位置の複数のセル範囲を取得する：getRangeList(a1Notations)
- A1から最終行・最終列までのセル範囲を取得する：getDataRange()
- このシートの最終行・最終列を取得する
- このシートの現在の行数・列数を取得する
- このシートの指定した行・列を移動する
- このシートのデータを並べ替える
- 1行のデータをこのシートの最終行の下に追加する
- このシートのデータや書式などを削除する
- まとめ
GASによるスプレッドシート操作の概要
GASでスプレッドシートを操作するための「SpreadSheetサービス」は、主に下記のクラスで構成されています。
| クラス | 説明 |
|---|---|
| SpreadSheetApp | SpreadSheetサービスの親クラス |
| Spreadsheet | スプレッドシートを操作するクラス |
| Sheet | シートを操作するクラス |
| Range | セル範囲を操作するクラス |
それぞれ下記のような位置付けになります。
「SpreadSheetApp」→「SpreadSheet」→「Sheet」→「Range」という順の階層構造になっています。操作対象に応じて、下の階層にたどってオブジェクトを取得して利用します。
このページでは、上記の中のSheetクラスの下記のメソッドについて説明します。
| メソッド | 戻り値 | 説明 |
|---|---|---|
| getRange(row, column) | Range | 行・列番号で指定した位置のセル範囲を取得する |
| getRange(row, column, numRows) | Range | 行・列番号で指定した位置から指定した数の行を取得する |
| getRange(row, column, numRows, numColumns) | Range | 行・列番号で指定した位置から指定した数の行と列を取得する |
| getRange(a1Notation) | Range | A1表記またはR1C1表記で指定した位置のセル範囲を取得する |
| getRangeList(a1Notations) | RangeList | A1表記またはR1C1表記で指定した位置の複数のセル範囲を取得する |
| getDataRange() | Range | A1から最終行・最終列までのセル範囲を取得する |
| getLastRow() | Integer | このシートのデータのある最終行を取得する |
| getLastColumn() | Integer | このシートのデータのある最終列を取得する |
| getMaxRows() | Integer | このシートの現在の行数を取得する |
| getMaxColumns() | Integer | このシートの現在の列数を取得する |
| moveRows(rowSpec, destinationIndex) | void | このシートの指定した行を指定した数だけ移動する |
| moveColumns(columnSpec, destinationIndex) | void | このシートの指定した列を指定した数だけ移動する |
| sort(columnPosition) | Sheet | このシートの指定した列を基準に昇順で並べ替える |
| sort(columnPosition, ascending) | Sheet | このシートの指定した列・指定した順でデータを並べ替える |
| appendRow(rowContents) | Sheet | 1行のデータをこのシートの最終行の下に追加する |
| clear() | Sheet | このシートのデータと書式を削除する |
| clearContents() | Sheet | このシートのデータのみを削除する |
| clearFormats() | Sheet | このシートの書式のみを削除する |
| clear(options) | Sheet | オプション指定でこのシートのデータや書式を削除する |
| clearNotes() | Sheet | このシートのメモを削除する |
| clearConditionalFormatRules() | void | このシートの条件付き書式ルールをすべて削除する |
このシートの指定したセル範囲を取得する
このシートの、指定したセル範囲を取得するメソッドを説明します。
行・列番号で指定した位置のセル範囲を取得する：getRange(row, column)
このシートの指定したセルを取得する方法です。
1からはじまる番号で行と列を指定します。
下記のように実行します。
getA1Notation()は、セルの位置を表示するメソッドです。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); console.log(testSheet.getRange(2, 2).getA1Notation()); } |
下記の結果になります。2行目の2列目になるため、B2セルが指定されます。
|
1 |
B2 |
行・列番号で指定した位置から指定した数の行を取得する：getRange(row, column, numRows)
このシートの指定したセルから指定した数の行を取得する方法です。
1からはじまる番号で行と列を指定します。
下記のように実行します。
getA1Notation()は、セルの位置を表示するメソッドです。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); console.log(testSheet.getRange(2, 2, 3).getA1Notation()); } |
下記の結果になります。2行目の2列目であるB2セルから、3行分が指定されます。
|
1 |
B2:B4 |
行・列番号で指定した位置から指定した数の行と列を取得する：getRange(row, column, numRows, numColumns)
このシートの指定したセルから指定した数の行と列を取得する方法です。
1からはじまる番号で行と列を指定します。
下記のように実行します。
getA1Notation()は、セルの位置を表示するメソッドです。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); console.log(testSheet.getRange(2, 2, 3, 5).getA1Notation()); } |
下記の結果になります。2行目の2列目であるB2セルから、3行分と5列分が指定されます。
|
1 |
B2:F4 |
A1表記またはR1C1表記で指定した位置のセル範囲を取得する：getRange(a1Notation)
このシートの指定したセル範囲を取得する方法です。
A1表記またはR1C1表記で指定できます。
A1表記は、列の英字と行の数字の組み合わせでセルを参照する方法で、例えば”A1″、”B2″、”C3″のように表記します。
R1C1表記は、セルの行番号と列番号を使用してセルを参照する方法で、例えば”R1C1″、”R2C2″、”R3C3″のように表記します。
下記のように実行します。
getA1Notation()は、セルの位置を表示するメソッドです。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); console.log(testSheet.getRange("B2:F3").getA1Notation()); } |
下記の結果になります。
|
1 |
B2:F3 |
A1表記またはR1C1表記で指定した位置の複数のセル範囲を取得する：getRangeList(a1Notations)
このシートの指定した複数のセル範囲を取得する方法です。
A1表記またはR1C1表記で指定できます。
下記のように実行します。
取得したセル範囲リストからgetRangesメソッドでセル範囲を取得し、getA1Notationメソッドで内容を表示しています。
getA1Notation()は、セルの位置を表示するメソッドです。
|
1 2 3 4 5 6 7 8 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testRangeList = testSheet.getRangeList([["A1:B2"], ["D4:E6"]]); for (const range of testRangeList.getRanges()) { console.log(range.getA1Notation()); } } |
下記の結果になります。
|
1 2 |
A1:B2 D4:E6 |
A1から最終行・最終列までのセル範囲を取得する：getDataRange()
このシートのA1セルからデータのある最終行・最終列までのセル範囲を取得する方法です。
下記のように実行します。
取得したセル範囲をgetValuesメソッドで表示しています。
|
1 2 3 4 5 6 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); const testRange = testSheet.getDataRange(); console.log(testRange.getValues()); } |
下記のファイルに対して実行すると、
下記の結果になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[ [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', 'D4セル', 'E4セル', '', '', '', '', '', '' ], [ '', '', '', 'D5セル', 'E5セル', '', '', '', '', '', '' ], [ '', '', '', 'D6セル', 'E6セル', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', '' ], [ '', '', '', '', '', '', '', '', '', '', 'K18' ] ] |
A1セルに値がなくてもA1からの範囲になるため、下記の範囲でセル範囲を取得します。
このシートの最終行・最終列を取得する
このシートの、最終行・最終列を取得するメソッドを説明します。
このシートのデータのある最終行を取得する：getLastRow()
このシートのデータのある最終行の行番号を取得する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); console.log(testSheet.getLastRow()); } |
下記のファイルに対して実行すると、
下記の結果になります。データの存在する一番下の行の行番号が表示されます。
|
1 |
18 |
このシートのデータのある最終列を取得する：getLastColumn()
このシートのデータのある最終列を番号で取得する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); console.log(testSheet.getLastColumn()); } |
下記のファイルに対して実行すると、
下記の結果になります。データの存在する一番右の列を番号にしたものが表示されます。
|
1 |
11 |
このシートの現在の行数・列数を取得する
このシートの、現在の行数・列数を取得するメソッドを説明します。
このシートの現在の行数を取得する：getMaxRows()
このシートの現在の行数を取得する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); console.log(testSheet.getMaxRows()); } |
下記のように1000行が上限になっている状態のシートで実行すると、
下記の結果になります。
|
1 |
1000 |
このシートの現在の列数を取得する：getMaxColumns()
このシートの現在の列数を取得する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); console.log(testSheet.getMaxColumns()); } |
下記のようにZ列が上限になっている状態のシートで実行すると、
下記の結果になります。
|
1 |
26 |
このシートの指定した行・列を移動する
このシートの、指定した行・列を移動するメソッドを説明します。
このシートの指定した行を指定した数だけ移動する：moveRows(rowSpec, destinationIndex)
このシートの、指定した行を移動する方法です。
rowSpecには、移動したい行を含むセル範囲を指定します。
例えば、C5:F9と指定した場合は、5行目〜9行目が移動対象になります。
destinationIndexには、移動先の行番号を指定します。13を指定すると13行目の上に挿入されます。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.moveRows(testSheet.getRange("B2:C3"), 5); } |
下記のシートに対して実行するとします。
B2:C3を指定しているため移動対象の行は、下記の2、3行目になります。
（BやCは特に意味はありません。）
移動先は、5を指定しているため、5行目の上（下記の位置）に挿入されます。
実行後は下記の結果になります。指定した行が、指定した移動先に移動されます。
このシートの指定した列を指定した数だけ移動する：moveColumns(columnSpec, destinationIndex)
このシートの、指定した列を移動する方法です。
rowSpecには、移動したい列を含むセル範囲を指定します。
例えば、C5:F9と指定した場合は、C列目〜F列目が移動対象になります。
destinationIndexには、移動先の行番号を指定します。8を指定すると8列目(H列)の左に挿入されます。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.moveColumns(testSheet.getRange("B2:C3"), 5); } |
下記のシートに対して実行するとします。
B2:C3を指定しているため移動対象の行は、下記のB、C列目になります。
（2や3は特に意味はありません。）
移動先は、5を指定しているため、5列目(E列)の左（下記の位置）に挿入されます。
実行後は下記の結果になります。指定した列が、指定した移動先に移動されます。
このシートのデータを並べ替える
このシートのデータを並べ替えるメソッドを説明します。
このシートの指定した列を基準に昇順で並べ替える：sort(columnPosition)
このシートのデータを指定した列の昇順で並べ替えする方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.sort(3); } |
下記のファイルに対して実行すると、
下記の結果になります。
上記のように、特定のセル範囲内で並べ替えるのではなく、シート全体で並べ替えされます。
特定のセル範囲内で並べ替えたい場合は、Rangeクラスのsortメソッドを利用するとよいでしょう。
このシートの指定した列・指定した順でデータを並べ替える：sort(columnPosition, ascending)
このシートのデータを指定した列・指定した順で並べ替えする方法です。
下記のように実行します。第2引数をtrueにすると昇順、falseにすると降順になります。
|
1 2 3 4 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); testSpreadSheet.sort(3, false); } |
下記のファイルに対して実行すると、
下記の結果になります。C列で降順に並べ替えられます。
上記のように、特定のセル範囲内で並べ替えるのではなく、シート全体で並べ替えされます。
特定のセル範囲内で並べ替えたい場合は、Rangeクラスのsortメソッドを利用するとよいでしょう。
1行のデータをこのシートの最終行の下に追加する
1行のデータをこのシートの最終行の下に追加するメソッドを説明します。
1行のデータをこのシートの最終行の下に追加する：appendRow(rowContents)
このシートのデータのある最終行の下に、データを1行追加する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.appendRow(["追加1", "追加2", "追加3"]); } |
下記のファイルに対して実行すると、
下記の結果になります。データの存在する最終行の下に追加されます。
このシートのデータや書式などを削除する
このシートのデータや書式などを削除するメソッドを説明します。
このシートのデータと書式を削除する：clear()
このシートのデータと書式を削除する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.clear(); } |
下記のファイルに対して実行すると、
下記の結果になります。データも書式も削除されます。
このシートのデータのみを削除する：clearContents()
このシートのデータのみを削除する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.clearContents(); } |
下記のファイルに対して実行すると、
下記の結果になります。データは削除されますが、書式は削除されません。
このシートの書式のみを削除する：clearFormats()
このシートの書式のみを削除する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.clearFormats(); } |
下記のファイルに対して実行すると、
下記の結果になります。書式は削除されますが、データは削除されません。
オプション指定でこのシートのデータや書式を削除する：clear(options)
データを削除するか、書式を削除するかを指定して、このシートのデータや書式を削除する方法です。
書式はformatOnly、データはcontentsOnlyというパラメータに対して、true/falseで指定します。
下記のように実行します。
書式を削除し、データを削除しない場合の例です。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.clear({ formatOnly: true, contentsOnly: false }); } |
下記のファイルに対して実行すると、
下記の結果になります。書式は削除されますが、データは削除されません。
このシートのメモを削除する：clearNotes()
このシートのメモを削除する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.clearNotes(); } |
下記のファイルに対して実行します。3つのメモがあります。
下記の結果になります。メモが削除されます。
このシートの条件付き書式ルールをすべて削除する：clearConditionalFormatRules()
このシートの条件付き書式ルールを削除する方法です。
下記のように実行します。
|
1 2 3 4 5 |
function myFunction() { const testSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); const testSheet = testSpreadSheet.getSheetByName("シート1"); testSheet.clearConditionalFormatRules(); } |
下記のファイルに対して実行します。
「”C”という文字を含むセルを黄色セルにする」という条件付き書式ルールが設定されています。
実行すると、下記の結果になります。条件付き書式ルールが削除されます。
まとめ
Google Apps Script (GAS) でセル範囲・最終行の取得や並び替え、データの追加・削除などの方法を説明しました。
GASは無料で利用できてとても便利なツールです。
Googleスプレッドシート等のGoogleアプリケーションを利用されている方は、GASを習得することで作業を大きく効率化できます。
GASの基本構文を知りたい方は、下記もご覧ください。
