GAS

【GAS】セルのデータが連続している範囲を選択する方法

ある日、このようなスプレッドシートのデータから「全員分の社員情報をリスト化してくれ」と依頼されたら、あなたならどうしますか？

社員情報が空白の行、列で区切られたレイアウトをしており、一般的な表形式のデータとは違いプログラムでは処理がしにくい構造になっています。

今回はこんなシートでも正しく値が取得できる、範囲の選択方法をご紹介します。

プログラム

早速、データを取得するプログラムを見てみましょう。

function main() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  const sheet = spreadsheet.getSheets()[0]

  let currentRange = sheet.getRange(2, 1)
  while (true) {
    const upperRange = currentRange
    currentRange = currentRange.getNextDataCell(SpreadsheetApp.Direction.DOWN)
    const lowerRange = currentRange

    if (lowerRange.getValue() === "") {
      // データを最後まで走査したらループを終わる
      break
    } else if (typeof upperRange.getValue().getContentUrl !== "function") {
      // 画像がRangeの先頭出ない場合は、データの間なので次のループへ
      continue
    }

    const employee = sheet
      .getRange(upperRange.getRow() + 1, upperRange.getColumn(), lowerRange.getRow() - upperRange.getRow())
      .getValues()
    console.log(employee)
  }
}
実行結果

[ [ '社員 太郎' ], [ '2000年入社' ], [ '部長' ] ]
[ [ '社員 次郎' ], [ '2005年入社' ] ]
[ [ '社員 三郎' ], [ '2005年入社' ], [ '主任' ] ]

無事、画像の縦の部分のデータが配列で取得できていることがわかります。

あとはこのプログラムに横方向の走査を追加すればシート内の全データが取得できそうです。

getNextDataCell関数を使った範囲選択

今回のプログラムで重要なことは「getNextDataCell関数」を使って、連続したデータ範囲を選択していることです。

このgetNextDataCell関数は、シート上で「Ctrl+矢印キー」を押したときと同じ動きをします。この「Ctrl+矢印キー」は、Excelやスプレッドシートを使い慣れたかなたらご存知かもしれませんが、「データが含まれる連続するセル範囲の端」にカーソルを移動する操作です。

画像の通りA2の画像が選択状態のときに下方向「Ctrl+↓」を押すと、A5の「部長」のセルまで選択が移動します。

その次に押すとA7、次にA9、A11、A13・・・の順で最後にシートの下端まで移動します。

今回の例では社員の情報は、空欄のセルを挟んで連続したデータという法則性があります。まさに「getNextDataCell関数」の使いどころであることがわかります。

プログラムのこの部分で「getNextDataCell関数」の前後のRangeを取得して、社員情報部分の一番上と一番下のRangeを取得しています。

const upperRange = currentRange
currentRange = currentRange.getNextDataCell(SpreadsheetApp.Direction.DOWN)
const lowerRange = currentRange

この2つのRangeを使って選択範囲を改めて定義し、値を取得しています。

const employee = sheet
  .getRange(upperRange.getRow() + 1, upperRange.getColumn(), lowerRange.getRow() - upperRange.getRow())
  .getValues()

まとめ

今回は、「セルのデータが連続している範囲を選択する方法」をご紹介しました。

一見してプログラムでは取得できなさそうなレイアウトのスプレッドシートでも、法則性を見つけ出すことで効率的に処理ができることが分かったと思います。

今回紹介した以外にもGASには多くの便利な関数が準備されています。複雑な機能の実装に詰まったときは是非公式のリファレンスを読んでみてください。思わぬヒントがあるかもしれません。

https://developers.google.com/apps-script/reference/