GoogleAppsScript(GAS)でスプレッドシートの値を取得した時にハマったこと

作成していたプログラムはJavaScriptでスプレッドシートを読み込み
データの整理をして画面に描画するプログラム。

以下の様な図のイメージ
![picture_pc_1c7dfa9f8a76eb172afb67d62b89c0a0.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/90415/b1b539a5-ee31-cc3e-8ba9-0740ba9bde7c.png)

最終的に受け取るJSONPのデータは以下

```JSON
apicallback(
             [
               [
                 [
                   "名前",
                   "学園",
                   "イメージカラー",
                   "ブランド"
                 ],
                 [
                   "星宮いちご",
                   "スターライト学園",
                   "ピンク",
                   "Angely Sugar"
                 ],
                 [
                   "霧矢あおい",
                   "スターライト学園",
                   "ブルー",
                   "FUTURING GIRL"
                 ]
               ]
             ]
           )
```

#修正前GASのコード
ABCD列のスプレッドシートの値をfor文で回してJSON形式で取得

```JavaScript
function getRoster() {
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = spreadsheet.getActiveSheet();
   var row1 = spreadsheet.getDataRange().getLastRow();
   var json = [];

   for (var i = 1; i <= row1 -1 ; i++) {
     var value1 = sheet.getRange("A" + i).getValue();
     var value2 = sheet.getRange("B" + i).getValue();
     var value3 = sheet.getRange("C" + i).getValue();
     var value4 = sheet.getRange("D" + i).getValue();

     var name_obj = {
       name: value1 ,
       academy: value2 ,
       imageColor: value3 ,
       brand: value4
     };
     json.push(name_obj);
   }
     var jsonp = "apicallback(" + JSON.stringify(json) + ")";

     return ContentService
         .createTextOutput(jsonp)
         .setMimeType(ContentService.MimeType.JAVASCRIPT);

}
```

#修正前JavaScript(jQuery)側のコード
GASで作成したJSONNPをGETして配列に格納

```JavaScript
function jsonRead() {
 var url = "※GAS側の発行したURL※";
 $.ajax({
     type: "GET",
     url: url,
     dataType: 'jsonp',
     contentType: 'application/javascript',
     jsonpCallback: 'apicallback',
 })
     .then(
         //取得
         function (data) {
             //取得したJSONをHTMLに追加
             for (var i = 0; i < data.length; i++) {
                 //一旦JavaScriptとして扱える形に変換
                 adata.push({
                   "name" : data[i].name ,
                   "academy" : data[i].academy ,
                   "imageColor" : data[i].imageColor ,
                   "brand" : data[i].brand
                 });
             }
         },
         //取得失敗
         function () {
             alert("取得に失敗しました。");
         });
}
```

#何が問題となったのか？
GAS側からの値を受け取れたことを確認して、
しばらくJavaScript側の実装を**約20件のテストデータ**で行っていた時は
読み込みは遅いが何も問題なく動作していた。

だが、**約20件のテストデータを2000件付近まで増幅**させた所問題発生。
Ajaxの処理がずっと帰ってこない状態となってしまった。
テスト環境のApacheの問題かなと思い、思い当たる節を変更してApacheを再起動しても動作せず。

ふと頭によぎったのは**Excel VBAのセルを一つ一つ読み取って配列に格納**する処理で発生する応答なし画面だった…。

#原因にアタリが付いたところで調べた
色々GASの情報が少ないながら調べてみた結果、**APIの呼び出し回数が多いと遅くなる**とのこと


#問題となっていたGASのコード
```javascript
  for (var i = 1; i <= row1 -1 ; i++) {
     var value1 = sheet.getRange("A" + i).getValue();
     var value2 = sheet.getRange("B" + i).getValue();
     var value3 = sheet.getRange("C" + i).getValue();
     var value4 = sheet.getRange("D" + i).getValue();

     var name_obj = {
       name: value1 ,
       academy: value2 ,
       imageColor: value3 ,
       brand: value4
     };
     json.push(name_obj);
   }
```

このループの中で「getValue」だけ見ても4つの記述があり、
当たり前ですが、**単純計算2000件のデータでも約8000回スプレッドシートにアクセス**していた事となります。

Excel VBAでこのハマりは昔経験した事があって、学びを生かせてないなぁと痛感。
同時に早い段階で気付いてと良かったと安堵。

#修正後のGASのコード
とりあえず**どんな形でもいいから**JavaScript側にデータを送れればいい様に修正
シート全体を指定して配列に突っ込んで変換

```JavaScript
function getRoster() {
   var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   var var1 = spreadsheet.getActiveSheet().getDataRange().getValues();
   var json = [];
   
   json.push(var1);
   
     var jsonp = "apicallback(" + JSON.stringify(json) + ")";

     return ContentService
         .createTextOutput(jsonp)
         .setMimeType(ContentService.MimeType.JAVASCRIPT);
}
```

#修正後JavaScript側のコード
GAS側のデータの形に合わせて修正

```javascript
function jsonRead() {
 var url = "※GAS側の発行したURL※";
 $.ajax({
     type: "GET",
     url: url,
     dataType: 'jsonp',
     contentType: 'application/javascript',
     jsonpCallback: 'apicallback',
 })
     .then(
         //取得
         function (data) {
             //取得したJSONをHTMLに追加
             for (var i = 1; i < data[0].length; i++) {
                 adata.push({
                   "name" : data[0][i][0] ,
                   "academy" : data[0][i][1] ,
                   "imageColor" : data[0][i][2] ,
                   "brand" : data[0][i][3]
                 });
             }
         },
         //取得失敗
         function () {
             alert("取得に失敗しました。");
         });
}
```

#まとめ
・GASでループ処理する時は他のプログラムでカバー出来ないか考える。
・やっぱり**セル選択の処理**はスプレッドシートでもExcelでも怖い