» 無分類 » GASを使うときの注意 その1 〜 値の取得設定はvaluesと配列を使え！

GASを使うときの注意 その1 〜 値の取得設定はvaluesと配列を使え！

2019年8月18日日曜日

今回のキーポイント 

・値の範囲取得→2次元配列
　・sheet.getRange(始まり行,始まり列,取得する行数,取得する列 数).getValues() 
・2次元配列→値の範囲設定 
　・sheet.getRange(始まり行,始まり列,設定する行数,設定する列数).setValues(2次元配列)

唐突に始まったGASネタ紹介。
今勤めている会社ではGASを絶賛プッシュ中なのですが、その中で出てきたGASネタやら問題やら課題やらを共有していきたい、って思いで書き出してみます。

さて今回は、

値の取得設定はvaluesと配列を使え！

です。
まあこれExcelVBAとかGASとかちょっとは使えるよん♪ってなってる人なら常識なのですが、
ことGASでは大事なことになります。
下記の実験結果をご覧ください。試しにスクリプトも作りましたので、実行してみてもいいですよ！

こちらからどうぞ！(GoogleSpreadsheetが別タブで開きます)
注意：他の方が実行している場合、正しく実行できない場合があります。確実に実行したい方は、スプレッドシートを自分のドライブにコピーして実行してください。

どうでしょう、Case1の実行がかなり遅かったではないでしょうか？
この2種類のCase1とCase2の違いは、

  • Case1:値を1つ取得→1つ設定の繰り返し
  • Case2:valuesで値を範囲取得→valuesで範囲設定

[GAS]
function Case1(sheetName) {
  //値を一つずつ転記します
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  //値の取得
  for (var i=1;i<101;i++){
    for (var j=1;j<4;j++){
      var value = sheet.getRange(i+1, j, 1, 1).getValue()
      sheet.getRange(i+1, j+8, 1, 1).setValue(value)
    }
  } 
}

function Case2(sheetName) {
  //値をまとめて取得して、まとめて貼り付けます。
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var values = sheet.getRange(2, 1, 100, 3).getValues()
  var values = [1,2,3]
  sheet.getRange(2,9,100,3).setValues(values);
}

GASは値の取得と設定が遅いです！理由はよくわからないのですがとにかく遅い。
場合によっては、値設定が反映される前に次の処理に行こうとするため、値設定の結果を用いようとすると失敗するほどです。
ExcelVBAだと、近年PCの性能が上がっているせいもあり、Case1の実装でも気にならないことが多いです。
しかしGASではそうはいかないのです。 valuesによる範囲取得設定や配列をうまく使わないと、後続処理やパフォーマンスに大きく影響を与えかねません。

一工夫する観点で、次のような例を考えてみましょう。
10行1列のデータがある。
文字列「りんご」があるデータには、2列目に「◯」をつける。

言葉の通りにするとコードにすると下記のようになります。
[GAS]
function CheckData(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var values = sheet.getRange(1, 1, 10, 1).getValues()
  for (var i=0;i<10;i++){
    if (values[i][0] == "りんご"){
      sheet.getRange(i+1, 2, 1, 1).setValue("◯")
    }
  }
}
ちゃんとgetValues()しているので一見いいように見えますが、あともう一歩です。 setValueもsetValuesにしましょう。こうなります。 
[GAS]
function CheckData2(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var getValues = sheet.getRange(1, 1, 10, 1).getValues()
  var setValues = new Array()
  for (var i=0;i<10;i++){
    if (getValues[i][0] == "りんご"){
      setValues.push(["◯"])
    }else{
      setValues.push([""])
    }
  }
  sheet.getRange(1, 2, 10, 1).setValues(setValues)
}
こうするとさらに処理が早くなります！
気をつけてほしいのは、配列はあくまで2次元で扱うということです。pushしている文字列を"[]"で囲っています。
setValuesのときにうまくいかない方は、配列が2次元になっているか確認しましょう！
これExcelでも同じことが言えて、下記のように書きます。
[VBA]
’例：A1からD4まで値を範囲取得する場合
Dim values() As String
values = WorkSheet.Range("A1:D4").Value

’例：2次元配列をA1からD4まで設定する場合
WorkSheet.Range("A1:D4").Value = values

という感じで、みなさん処理をすべて配列で処理することを覚えると、速度も改善するのですが、コード全体がすっきりした見た目になるのでぜひお試しください！


備考：
2次元配列なので、例えばA1からD1まで貼り付けたい場合は、
4行1列の2次元配列を当て込む必要があります。1行4列の配列ではGASでもVBAでもエラーになっちゃうので気をつけてくださいね！！
失敗するときのエラーメッセージは下記のとおりです。

[GAS]
Array を Object[][] に変換できません。
時刻: