【GAS】スプレッドシートのセルを超高速で読み書きする方法【初心者】

はじめに

Googleスプレッドシートでスクリプトを利用する仕組み GoogleAppsScript(以降GAS)を使うと非常に便利です。
ExcelのVBAみたいなものですね。
GAS初心者の筆者がさっそく使ってみました

セルに書き込む

それでさっそく一番左上のセルに書き込みましょう

sample.js

function myFunction() {
  const sheetOut = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('書き込むシート名');
  sheetOut.getRange(1, 1).setValue('hello GAS World');
}

完成です。終了
というわけにはいきません。今度は九九の表を作ってみます。

sample.js

function myFunction() {
  const sheetOut = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('書き込むシート名');
  for(let j = 1; j <= 9; j++) {
    for(let i = 1; i <= 9; i++) {
      sheetOut.getRange(j, i).setValue(j*i);
    }
  }
}

どうですか？動きましたか？
やたらと時間がかかっていますね。
実行中にスプレッドシートを見るとセルに値が入っていくところを見ることが出来る
それぐらいのスピードです。

GASが遅いのでしょうか？
そうでは無いようです。
どうも sheetOut.getRange(j, i).setValue(j*i); 命令が遅いようです。
VBAとは異なり命令実行の度に外部？のスプレッドシートが参照されて・・・とか複雑なことになっているようです。

高速に書き込む

スプレッドシートに高速に書き込む場合は配列に入れておいてそれを一気に書き込めば解決します。
二次元配列 oValues に値を入れて最後に **sheetOut.getRange(1,1,9,9).setValues(oValues); **で書き込んでいます。

sample.js

function myFunction() {
  const sheetOut = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('書き込むシート名');
  let oValues = [[]];
  oValues.length=0;
  for(let j = 1; j <= 9; j++) {
    oValues.push([0,0,0,0,0,0,0,0,0]);
    for(let i = 1; i <= 9; i++) {
      oValues[j-1][i-1] = j*i;
    }
  }  
  sheetOut.getRange(1,1,9,9).setValues(oValues); 
}

セルを高速に読み込む

読み込みも遅いと思うのでこっちも高速化しましょう

sample.js

function myFunction() {
  const sheetIn = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('読み込むシート名');
  const iValues = sheetIn.getRange(1, 1, 9, 9).getValues(); 
}

これで iValues に二次元配列の値として読み込まれるので let no = iValues[i][0]; のようにして値を取り出すことが出来ます。

セルの位置は 1 から始まるのに配列の添え字は 0 から始まるところだけ注意が必要です。

最後に

いかがでしょうか？
これでGASプログラムが早くなるのですが面倒ですね。

GASを始めて3日目なのでよくわかっていません。

実験するときの注意としては読み込むシートと書き込むシートはわけておかないと
ちょっと間違えてシートのデータが消えるなんてこともあるので注意しましょう。