[GoogleAppsScript] getValues & setValuesでGASを高速化しよう

はじめに

GoogleAppsScript(以下、GASとよびます)では一つ一つのセルに対して値を取得(getValue)することや値を設定(setValue)することは速度的な面をみてもオススメされていません。

またGASには実行時間が5分を越えると強制終了されてしまう「5分の壁問題」もあるわけで。

したがって、GASでは複数のセルに対して一気に値を取得・設定するメソッド、getValues()・setValues()を使用し、高速化を図りましょう。

getValues()・setValues()

以下のようなシート構造を例に用います。

getValuesは以下のようにお使いいただけます。以下については一例ですので詳細に関しては公式リファレンスを。

// getValues()
var startRow    = 1,
    startColumn = 1,
    numOfRow    = 3,
    numOfColumn    = 2;
var sheet = SpreadsheetApp.getActive().getSheetByName( '<SHEET_NAME>' );
var data = sheet.getRange( startRow, startColumn, numOfRow, numOfColumn ).getValues();

これで以下のような結果が取得できます。

[
  ['1','hoge'],
  ['2','fuga'],
  ['3','bar'],
]

ここでは２次元配列の値が返ってくるため、イテレーションを回す際には注意してください。

さて、setValues()も同様にして、

// setValues()
var startRow    = 1,
    startColumn = 1,
    numOfRow    = 3,
    numOfColumn    = 2;
var sheet = SpreadsheetApp.getActive().getSheetByName( '<SHEET_NAME>' );

var data = [ ['1','a'], ['2','b'], ['3','c' ] ];
sheet.getRange( startRow, startColumn, numOfRow, numOfColumn ).setValues(data);

以下のようになります。

「ArrayをObject[][]に変換できません。」に関して

setValues()の使用にあたって付いて回るエラーとして、「ArrayをObject[][]に変換できません。」というものがあります。こちらの方の記事などもご参照ください。

これは単純に、setValues()の引数に２次元配列で渡すべきところをそれ以外の形（特に１次元配列など）で渡してしまうことに問題があります。

解決策としては、単純にデータ構造をよく見て、適切な２次元配列に変換してあげることです。

データ構造としては以下のようなものになるのでご参照ください。

外側の配列( [ ['1','a'], ['2','b'], ['3','c'] ] )の要素が１行１行になります。

内側の配列( [ [ '1','a' ], [ '2','b' ], [ '3','c' ] ] )の要素が１列１列になります。

２次元配列の転置

ロジックの中で、配列の転置をしたいとなった場合には以下のようなライブラリが有効です。

gas-underscore

ライブラリの導入

GASではこのようなライブラリを以下のように設定して使うことができます。残念ながらrequireやimportのようなものは現段階では使えません。

1. メニューバーの「リソース」から「ライブラリ」を選択
   

2. 下部の「ライブラリを追加」からuderscoreを検索し追加
   

gas-underscore

さて、以下のようにして転置します。

  var _ = Underscore.load();
  var transData = _.zip.apply(_, data);

結果

[ 
  ['1','2','3'], 
  ['a','b','c'],
]

これをsetValues()する際には転置の性質上、RowとColumnの値が逆転しますのでご注意ください。

例えば今回の例だと以下のようにします。

// setValues() 
var startRow    = 1,
    startColumn = 1,
    numOfRow    = 3,
    numOfColumn    = 2;
var sheet = SpreadsheetApp.getActive().getSheetByName( '<SHEET_NAME>' );

var data = [ ['1','a'], ['2','b'], ['3','c' ] ];
var _ = Underscore.load();
var dataTrans = _.zip.apply(_, data);

sheet.getRange( startRow, startColumn, numOfRow-1, numOfColumn+1 ).setValues(transData);

以下のようになります。

総括コード

ご参照ください。

function transpose(arr) {
    var sheet = SpreadsheetApp.getActive().getSheetByName('sheet7');
    var startRow    = 1,
        startColumn = 1,
        numOfRow    = 3,
        numOfColumn    = 2;

    // getValues()
    var data = sheet.getRange( startRow, startColumn, numOfRow, numOfColumn ).getValues();

    // setValues()
    sheet.getRange( startRow, startColumn, numOfRow, numOfColumn ).setValues(data);

    // Transpose data and setValues()
    var _ = Underscore.load();
    var transData = _.zip.apply(_, data);

    sheet.getRange( startRow, startColumn, numOfRow-1, numOfColumn+1 ).setValues(transData);
}