象と散歩: GASでシートを処理するなら配列を使え！

GoogleスプレッドシートをGAS（Google Apps Script）で処理するのであれば配列を覚えましょう。配列を使ってシートの読み書きを一括で行うと処理が格段に速くなります。

なぜ配列を使うのか

GASには、

Google Sheets のアクセスに時間がかかる
6分で処理が強制停止される

という制約があります。

Googleスプレッドシートを操作するときに

getRange()
getValue()/getValues()
setValue()/setValues()
getLastRow()/getLastColmun()

などのメソッドを使いますが、これらの呼び出しには時間がかかります。

行数が多いシートでは、1行づつシートを読み書きするのと、配列で一括で読み書きするのでは分単位で処理時間が違ってきます。

二次元配列とは

「でも、配列って難しそう、、、」

と、思われがちですが、二次元配列はシートと同じと考えれば簡単です。下記のように行と列で表記されます。

values[行][列]

valuesは変数名です。後ろに続く最初の[]が行で、次の[]が列です。シートの"A1"という表記はA列1行目と列行の順番ですが、配列は行列の順番です。

注意点としては配列のインデックスは0から始まるので、 A1と同じ1行1列目のデータは、values[0][0]となります。

二次元配列はシートと同じ行と列
表記の順番は[行][列]
インデックスは0から始まる

これだけ理解できれば配列を自在に扱うことができます。

では、下記がシートで考えた場合にどのデータを指しているか考えてみてください。

values[1][2]
values[2][0]

答えは、

2行3列目のデータで C2
3行1列目のデータで A3

です。

また、values[0]と記載した場合は1行目のデータとなり、values と[]を付けなければ配列全体となります。

サンプルデータで配列の中身を確認する

実際に、4行3列のサンプルデータで二次元配列を確認していきます。

下記のデータをGoogleスプレッドシートにコピーしてください。

グループ	男性	女性
A	10	20
B	20	10
C	15	15

シートをシートIDとシート名から取得していますので、下記のコードの sheetId と sheetName の値を変更してください。

シートIDはURLの赤字の部分となります。

https://docs.google.com/spreadsheets/d/abcd1234/edit

シート名は、シートに付けている名前です。

下記のコードを実行します。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
	
  Logger.log(values[1][2])
  Logger.log(values[2][0])
  Logger.log(values[0])
  Logger.log(values)
}

実行結果は下記の通りです。

20.0
B
[グループ,男性,女性]
[[グループ,男性,女性],[A,10.0,20.0],[B,20.0,10.0],[C,15.0,15.0]]

最後の配列全体の出力結果はカンマ区切りでデータが続いているので一見難しそうです。しかし、各要素を見ると[]が各行のデータで、その中に各列のデータがカンマで区切られていることがわかります。

シートのデータを配列に読み込む

上記サンプルコードの8行目でシート全体（データ範囲）を読み込んでいます。

var values = sheet.getDataRange().getValues()

getRange()で、下記のように範囲を指定してシートを読み込むこともできます。

getRange（開始行、開始列, データを読み込む行数, データを読み込む列数）

データを読み込む行数と列数はgetLastRow()、getLastCloumn()でそれぞれ取得できます。

//範囲を指定して読み込む
var lastRow	= sheet.getLastRow()	//最終行の取得
var lastColumn	= sheet.getLastColumn()	//最終列の取得
var values = sheet.getRange(1, 1, lastRow, lastColumn).getValues()

しかし、シート全体を読み込むのであれば、getDataRange() で範囲を指定しない方が簡単です。

ヘッダー行の削除 shift()メソッド

計算処理などをする場合にはヘッダー行は不要です。

範囲指定で2行目から最終行を読み込むこともできますが、シート全体を配列に読み込んでから1行目を削除した方が簡単です。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
	
  //ヘッダー行の削除
  values.shift()

  Logger.log(values)
}

shift()メソッドは、二次元配列先頭の一次元配列を削除します、、、。簡単にいえば、1行目のデータの削除です。

配列の全行処理は for ... in が便利

配列を全行処理するには、for...in 文が便利です。

下記の11-13行目が for...in の記載となります。これで、in の後ろに記載した配列（values）を1行づつ処理することができます。変数 i には現在処理をしている行数が格納されます。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //各行の出力
  for (var i in values) {
    Logger.log(values[i])
  }
}

前述したように配列のインデックスは０から始まりますので、変数 i の出力結果は、0, 1, 2 となります。

普通にforループで記載するならlengthプロパティで配列の要素数（行数）を求めてループさせます。

下例では要素数（length）は３です。0行目、1行目、2行目と3回ループを回すので配列の要素数未満という条件でループさせます。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //各行の出力
  numOfRow = values.length
  for (var i=0; i < numOfRow; i++) {
    Logger.log(values[i])
  }
}

for ... in文で記載した方が簡単です。

末尾に列を追加する push()メソッド

次に男性と女性の数を合算して列の最後（D列）に追加します。

繰り返しとなりますが、配列のインデックスは0から始まりますので、男性は values[i][1] で女性は values[i][2]です。

行の最後に列を追加するには、push()メソッドを使います。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //各行の出力
  for (var i in values) {
    values[i].push(values[i][1] + values[i][2])
  }
  Logger.log(values)
}

i行目の最後に男性と女性の合算値を追加して、最後に配列全体を出力しています。

ヘッダー行の追加 unshift()メソッド

合算値を追加した新しいヘッダー行を unshift()メソッドで先頭行に追加します。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //男性と女性を合算して行の末尾に列を追加
  for (var i in values) {
    values[i].push(values[i][1] + values[i][2])
  }

  //ヘッダー行の追加
  values.unshift('グループ','男性','女性','合計')

  Logger.log(values)
}

配列のデータをシートに書き戻す

最後にシートに書き戻します。シートにデータを書き込むには、書き込む範囲を getRange() で指定する必要があります。

getRange(開始行, 開始列, 行数, 列数)

開始行と開始列は、それぞれ1行目と1列目です。

行数は、values.length です。列数は配列の最初の行の長さ values[0].length で求まります。

指定した範囲に配列 values　を書き込みます。

function myFunction() {
  //シートオブジェクトの取得
  var sheetId = 'abcd1234'
  var sheetName = 'シート1'
  var sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName)

  //配列へシート全体を読み込む
  var values = sheet.getDataRange().getValues()
  
  //男性と女性を合算して行の末尾に列を追加
  for (var i in values) {
    values[i].push(values[i][1] + values[i][2])
  }

  //ヘッダー行の追加
  values.unshift('グループ','男性','女性','合計')

  //配列をシートに書き込む
  var values = sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

シートを確認すると、配列で計算した結果が書き込まれているのがわかります。

以上が、

シートから配列に読み込む
配列で処理をする
配列をシートに書き込む

という、一連の処理の簡単な説明となります。

GASの基礎を学べる参考図書

ある程度プログラミンがわかっていれば、WEBやYoutubeでも十分に調べられると思いますが、初歩的なところからであれば参考図書は有効な学習手段です。

「詳解! Google Apps Script完全入門 [第3版]」は、プラグラミング初心者にわかりやすく説明されています。GASを最初に学ぶ一冊として良書です。

詳解! Google Apps Script完全入門 [第3版]

高橋宣成 (著)

楽天ブックスで購入

Amazonで購入

象と散歩

Translate

2022年1月3日

GASでシートを処理するなら配列を使え！