【0からGASを学ぶ】GASを用いたスプレッドシートの実行速度向上のポイントを解説

# はじめに
本シリーズでは、GASの始め方や便利な使い方、ビジネス活用まで幅広く解説します。シリーズをひと通り読んでいただければ、あなたもきっとGASマスターになれるはずです。

# シリーズの対象者
* そもそもGASってなんだか**わからない**方
* GASを学びたいけど**何から始めればいいか**わからない方
* GASはわかり始めたけど、**もっと活用ができないか**と模索している方
* とにかく**Googleが好き！** という方

# 前回記事
https://qiita.com/gas-suke/items/928fdd8d01fafc4aed1b

# スプレッドシートで遊ぶ
では早速始めていきましょう。【0からGASを学ぶ】シリーズの第6回は「GASを用いたスプレッドシートの実行速度向上のポイントを解説」です。今回は少しコーヒーブレイクです。<a href="https://qiita.com/gas-suke/items/928fdd8d01fafc4aed1b#step4%E3%83%A1%E3%83%BC%E3%83%AB%E3%82%92%E9%80%81%E4%BF%A1%E3%81%97%E3%81%9F%E5%BE%8C%E3%81%AB%E7%99%BA%E6%B3%A8%E3%83%A1%E3%83%BC%E3%83%AB%E6%AC%84%E3%81%AB%E9%80%81%E4%BF%A1%E6%97%A5%E6%99%82%E3%82%92%E8%A8%98%E5%85%A5%E3%81%99%E3%82%8B" target="_blank">前回のSTEP.4</a>において、スプレッドシートでは **「都度貼り付け」** を行うのではなく **「最後に一括で貼り付け」** をした方がよいとお伝えしました。もちろんやりたいことによっては、**都度貼り付けを行わなければいけない**こともあるかもしれません。ただし、これによる速度の違いを知っておくことで**柔軟な発想が生まれる**こともあると思いますので、早速計測してみましょう。

## 事前準備
スプレッドシートのデータを取得しなければ始まらないので、**取得元となるスプレッドシート**を作りましょう。私は以下のようなスプレッドシートを作成しました。サンプルなので、この通りである必要はありませんが、これを前提にプログラムは書かせてもらいます。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3575702/258f646d-9af9-1abf-0589-26ed71a9be5d.png" width="600">

## 今回やること
1. スプレッドシート内のすべてのデータを取得する。
1. 都度貼り付けによる速度を計測する（500行ごとにログを出力する）。
1. 一括貼り付けによる速度を計測する（500行ごとにログを出力する）。

## プログラム開始
### GASエディタを開く
今回もコンテナバインド型でGASプログラムを記述していきましょう。<a href="https://qiita.com/gas-suke/items/5cf7bd4c6c3f15a25838#%E3%82%B3%E3%83%B3%E3%83%86%E3%83%8A%E3%83%90%E3%82%A4%E3%83%B3%E3%83%89%E5%9E%8B" target="_blank">こちら</a>を参考にGASエディタを起動してください。では、どんどんいきますよ、ついてきてください。

### STEP.1　スプレッドシート内のすべてのデータを取得する
```javascript:Qiita006.gs
function Qiita006_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();
  console.log(wVals.length);
}
```
```:実行結果
21:06:01	お知らせ	実行開始
21:06:01	情報	5000
21:06:02	お知らせ	実行完了
```
無事にデータを取得できていることが確認できます。

### STEP.2　都度貼り付けによる速度を計測する（500行ごとにログを出力する）
それでは、まずは1セルずつアクセスしながら、データをセットしていきましょう。
```javascript:Qiita006.gs
const COL = {
  NUM:1
  , SEQUENT:2
  , BATCH:3
}

function Qiita006_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();

  for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // セルに毎回アクセスして、値を更新する
    wSheet.getRange(rIdx+1, COL.SEQUENT).setValue(('0000'+rIdx).slice(-4));
    // 500件ごとにログを出力する
    if (rIdx%500==0) console.log(rIdx);
  }
}

```
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3575702/5ff3fafd-1fcf-cd4f-9d81-92b945e0d898.png" width="600">

```:実行結果
21:26:54	お知らせ	実行開始
21:26:55	情報	500
21:26:57	情報	1000
21:26:58	情報	1500
21:27:00	情報	2000
21:27:02	情報	2500
21:27:04	情報	3000
21:27:05	情報	3500
21:27:07	情報	4000
21:27:09	情報	4500
21:27:12	お知らせ	実行完了
```

5,000件を処理するのに**18秒**かかっています。ただ、ループを回してセルに値を入れているだけですが、これだけかかります。各セルの値を評価しながら、処理を分岐させたりすると、もっとかかりそうです。

### STEP.3　一括貼り付けによる速度を計測する（500行ごとにログを出力する）
それでは、次にプログラム内で配列を作成して、最後に一括でデータをセットしてみましょう。
```javascript:Qiita006.gs
const COL = {
  NUM:1
  , SEQUENT:2
  , BATCH:3
}

function Qiita006_SpreadSheet() {
  // コンテナバインド型なので、getActiveSpreadsheetにより自分自身を取得する
  const wSpread = SpreadsheetApp.getActiveSpreadsheet();

  // スプレッドシート内の1番目のシートを取得する
  const wSheet = wSpread.getSheets()[0];

  // シート内の全データを取得する。
  // getDataRangeはデータが存在する範囲のみを取得してくれるため非常に便利
  let wVals = wSheet.getDataRange().getValues();
  let wWriteLst = new Array();

  for (let rIdx=1; rIdx<wVals.length; rIdx++) {
    // 値を一括貼り付け用のリストにセットする
    wWriteLst.push([('0000'+rIdx).slice(-4)]);
    // 500件ごとにログを出力する
    if (rIdx%500==0) console.log(rIdx);
  }
  // 一括で貼り付け
  wSheet.getRange(2, COL.BATCH, wWriteLst.length, 1).setValues(wWriteLst);
}
```
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3575702/5910d95f-4c15-ea76-2532-689da8b22375.png" width="600">

```:実行結果
21:36:59	お知らせ	実行開始
21:36:59	情報	500
21:36:59	情報	1000
21:36:59	情報	1500
21:36:59	情報	2000
21:36:59	情報	2500
21:36:59	情報	3000
21:36:59	情報	3500
21:36:59	情報	4000
21:36:59	情報	4500
21:37:01	お知らせ	実行完了
```
！！！！段違いでした。実行してみるとよりわかりますが、本当に**一瞬**で完了します。

### さらにいうと
**都度貼り付け**の場合は、
```javascript
wSheet.getRange(rIdx+1, COL.SEQUENT).setValue(('0000'+rIdx).slice(-4));
```
本箇所において、`プロパティの読み取り/書き込み`が`getRange`と`setValue`で発生しています。そのため、5,000行の場合は、`2*5,000=10,000`回の読み書きが発生することとなり、以前もお伝えしましたGASの制限事項を考えると、非常に効率の悪いプログラムとなっています。

https://developers.google.com/apps-script/guides/services/quotas?hl=ja

対して、**一括貼り付け**の場合は、
```javascript
wSheet.getRange(2, COL.BATCH, wWriteLst.length, 1).setValues(wWriteLst);
```
と、最後に1度（2回）アクセスするだけとなるため、1 / 5,000 の回数であることがわかります。

### 
# おわりに
お疲れ様でした。
第6回は「GASを用いたスプレッドシートの実行速度向上のポイントを解説」ということで、今後スプレッドシートを扱う上で**プロパティの読み書きを効率化することの大切さ**をお伝えしました。次回も引き続き、スプレッドシートで遊んでいきましょう。
記事を読んで、「良いな」や「今後に期待できる！」と感じて頂けたら**いいね**や**フォロー**、**コメント**いただけると幸いです。それではまた次回をお楽しみに！

## ブログでより詳しく解説しています！
以下画像をクリックしてブログにアクセス！！
<a href="https://gas-4u.com/gas05/" target="_blank"><img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3575702/e1aa3e2e-2c74-ff54-735c-f7f848b8dc1f.png" width="600"/></a>