Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

はじめに

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

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

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

getValues()・setValues()

以下のようなシート構造を例に用います。
Screen Shot 2018-07-14 at 10.51.59.png

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'],
]

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

さて、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);

以下のようになります。

Screen Shot 2018-07-14 at 10.51.59.png

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

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

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

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

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

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

Screen Shot 2018-07-14 at 11.04.15.png

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

Screen Shot 2018-07-14 at 11.03.29.png

2次元配列の転置

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

gas-underscore

ライブラリの導入

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

  1. メニューバーの「リソース」から「ライブラリ」を選択
    Screen Shot 2018-07-14 at 10.32.43.png

  2. 下部の「ライブラリを追加」からuderscoreを検索し追加
    Screen Shot 2018-07-14 at 10.32.52.png

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);

以下のようになります。

Screen Shot 2018-07-14 at 11.25.04.png

総括コード

ご参照ください。

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);
}

sa9sha9
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした