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

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

以下の様な図のイメージ

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

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

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

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して配列に格納

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のコード

  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側にデータを送れればいい様に修正
シート全体を指定して配列に突っ込んで変換

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側のデータの形に合わせて修正

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でも怖い