GASのスプレッドシート 配列 高速化

Question

###前提・実現したいこと
ここに質問したいことを詳細に書いてください
スプレッドシートにランダム関数を最終行まで（約３０００個）、一列に並べるスクリプトを作りたい
###発生している問題・エラーメッセージ
以下のソースコードでは処理はできるのですが、とても遅いです。
配列を用いてもっと高速化する方法を教えて欲しいと思います。

###該当のソースコード
Google App Script
ここにご自身が実行したソースコードを書いてください
function readRandom() {
  
  var ss = SpreadsheetApp;
  var sh = ss.getSheetByName("シート１");
  var last_row = sh.getLastRow();
  　　　　var i 
  　　　　//とりあえずD1から最終行までに乱数を出力
  　　　　for (i=1;i<last_row;i++){
    　　　　var rand = Math.random();
   　　　　 // Math.random() は0から0.9999....を出力。
   　　　　 ss.getRange('D2').offset(i-1,0).setValue(rand);
   　　　　 //Offsetでiが増える度にセルを移動
 　　　　 }
}

###試したこと
配列を用いてSetValuesを使うこと試みましたが自分自身概念が理解できていないためできなかった

###補足情報(言語/FW/ツール等のバージョンなど)
より詳細な情報

Comments

[kei344]

ご自身で試されたコードを質問文に追記し、「何」が「どのように」わからないのか、コードのどの部分で詰まっているのかなどを具体的に追記されたほうが回答が望めると思います。また、質問文のコードはそれぞれコードブロックで囲んでいただけませんか？ ```（バッククオート3つ）で囲み、前後に改行をいれるか、コードを選択して「<code>」ボタンを押すとコードブロックになります。

Answer 1

SheetオブジェクトのRangeとsetValueの扱いかたで劇的に速度が変わります。Google Apps Script はサーバーサイドで動作するので、なるべくサーバー側で動作するようなコードを心がけると良いです。
Class Range

以下のコードは、3つのやり方で1,000行のセルに乱数をセットし、それぞれの経過時間を計る簡単なベンチマークプログラムです。

1. doRandom1 - 質問者様の提示されたやり方。A1形式でセルを指定してB列に値をセット
2. doRandom2 - 行ごとに、C列にgetRange(row, column)で取得したRangeに乱数をセット
3. doRandom3 - 乱数をあらかじめ配列に格納しておき、getRange(row, column rowNum) で取得したRange に配列でD列に一挙にセット。Range.setValuesの使用例。

function doTest() {
    try {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getSheetByName("シート2");
        var num = 1000;
        var elapse

        sheet.clear();
        elapse = doRandom1(sheet, num);
        Browser.msgBox("#1: " + String(elapse));

        elapse = doRandom2(sheet, num);
        Browser.msgBox("#2: " + String(elapse));

        elapse = doRandom3(sheet, num);
        Browser.msgBox("#3: " + String(elapse));

    } catch (e) {
        Logger.log("Exception: " + e);
    }
    Browser.msgBox(Logger.getLog());
}

// A1 notation
// B列にデータをセット
function doRandom1(sheet, num) {
    var sdt = new Date();

    for (var i = 1; i <= num; i++) {
        var rand = Math.random();
        sheet.getRange('B1').offset(i - 1, 0).setValue(rand);
    }
    var edt = new Date();
    var elapse = edt.getTime() - sdt.getTime();

    return elapse;
}

// C列にデータをセット
function doRandom2(sheet, num) {
    var sdt = new Date();

    for (var i = 1; i <= num; i++) {
        var rand = Math.random();
        // row, column
        sheet.getRange(i, 3).setValue(rand);
    }
    var edt = new Date();
    var elapse = edt.getTime() - sdt.getTime();

    return elapse;
}

// D列にデータをセット
function doRandom3(sheet, num) {
    var sdt = new Date();

    var array = [];
    for (var i = 0; i < num; i++) {
        var rand = Math.random();
        var item = [rand];
        array.push(item);
    }

    // row, column, num
    var range = sheet.getRange(1, 4, num);
    // 配列で一挙にセット
    range.setValues(array);

    var edt = new Date();
    var elapse = edt.getTime() - sdt.getTime();

    return elapse;
}

下の表は実行結果です。下のものほど速いです。本来なら何回もテストして平均を取るべきですが、何回試しても傾向は変わらないので、その内の1回だけを示します。

No	メソッド	経過時間(ミリ秒)	備考
1	doRandom1	199,368m秒	1行毎にセルにセットする様子が確認できる
2	doRandom2	1,295m秒	スクリプトの終了後にシートに表示される
3	doRandom3	22m秒	スクリプトの終了後にシートに表示される

スクリプトの終了後にシートに反映されるのは、サーバー側で処理された後、一挙にクライアント（ブラウザー）側に返されるからだと思います。

参考に、シートの様子を張っておきます。

Comments

[zoira]

大変分かりやすい回答ありがとうございました。処理速度が驚くほど違いますね！