GASでAPIの呼び出し回数が減らし、実行速度を高速化したい

実現したいこと

ここに実現したいことを箇条書きで書いてください。

GASでレポート作成の自動化を行いたく、countifs関数のような動作をするGASを実装させました
ただ、実行速度がかなり遅く、APIの呼び出し回数を減らし高速化したいです。

発生している問題・エラーメッセージ

特になし

該当のソースコード

GoogleAppScript
1function GenerateDailyOfferReport() {
2  const ss = SpreadsheetApp.getActiveSpreadsheet();
3  const sheetA = ss.getSheetByName("シートA").getDataRange().getValues();
4  const sheetB_Date = ss.getSheetByName("offer_test").getRange("A3:A33").getValues();
5  const sheetB_Campaign = ss.getSheetByName("offer_test").getRange("C1").getValues();
6  const sheetB_Campaign_2 = ss.getSheetByName("offer_test").getRange("C36").getValues();
7  const sheetB_Campaign_3 = ss.getSheetByName("offer_test").getRange("C71").getValues();
8  const sheetB_Campaign_4 = ss.getSheetByName("offer_test").getRange("C106").getValues();
9  const sheetB_Campaign_5 = ss.getSheetByName("offer_test").getRange("C141").getValues();
10  const sheetB_Campaign_6 = ss.getSheetByName("offer_test").getRange("C176").getValues();
11  const sheetB_Campaign_7 = ss.getSheetByName("offer_test").getRange("C211").getValues();
12  const sheetB_Campaign_8 = ss.getSheetByName("offer_test").getRange("C246").getValues();
13  const sheetB_Campaign_9 = ss.getSheetByName("offer_test").getRange("C281").getValues();
14  const sheetB_Campaign_10 = ss.getSheetByName("offer_test").getRange("C316").getValues();
15
16  const sheetB_Offer = ss.getSheetByName("offer_test").getRange("B2:Z2").getValues();
17  const sheetB_Offer_2 = ss.getSheetByName("offer_test").getRange("B37:Z37").getValues();
18  const sheetB_Offer_3 = ss.getSheetByName("offer_test").getRange("B72:Z72").getValues();
19  const sheetB_Offer_4 = ss.getSheetByName("offer_test").getRange("B107:Z107").getValues();
20  const sheetB_Offer_5 = ss.getSheetByName("offer_test").getRange("B142:Z142").getValues();
21  const sheetB_Offer_6 = ss.getSheetByName("offer_test").getRange("B177:Z177").getValues();
22  const sheetB_Offer_7 = ss.getSheetByName("offer_test").getRange("B212:Z212").getValues();
23  const sheetB_Offer_8 = ss.getSheetByName("offer_test").getRange("B247:Z247").getValues();
24  const sheetB_Offer_9 = ss.getSheetByName("offer_test").getRange("B282:Z282").getValues();
25  const sheetB_Offer_10 = ss.getSheetByName("offer_test").getRange("B317:Z317").getValues();
26
27  const sheetB_Date_Flat = sheetB_Date.flat();
28  
29  const sheetB_Campaign_Flat = sheetB_Campaign.flat();
30  const sheetB_Campaign_Flat_2 = sheetB_Campaign_2.flat();
31  const sheetB_Campaign_Flat_3 = sheetB_Campaign_3.flat();
32  const sheetB_Campaign_Flat_4 = sheetB_Campaign_4.flat();
33  const sheetB_Campaign_Flat_5 = sheetB_Campaign_5.flat();
34  const sheetB_Campaign_Flat_6 = sheetB_Campaign_6.flat();
35  const sheetB_Campaign_Flat_7 = sheetB_Campaign_7.flat();
36  const sheetB_Campaign_Flat_8 = sheetB_Campaign_8.flat();
37  const sheetB_Campaign_Flat_9 = sheetB_Campaign_9.flat();
38  const sheetB_Campaign_Flat_10 = sheetB_Campaign_10.flat();
39
40  const sheetB_Offer_Flat = sheetB_Offer.flat();
41  const sheetB_Offer_Flat_2 = sheetB_Offer_2.flat();
42  const sheetB_Offer_Flat_3 = sheetB_Offer_3.flat();
43  const sheetB_Offer_Flat_4 = sheetB_Offer_4.flat();
44  const sheetB_Offer_Flat_5 = sheetB_Offer_5.flat();
45  const sheetB_Offer_Flat_6 = sheetB_Offer_6.flat();
46  const sheetB_Offer_Flat_7 = sheetB_Offer_7.flat();
47  const sheetB_Offer_Flat_8 = sheetB_Offer_8.flat();
48  const sheetB_Offer_Flat_9 = sheetB_Offer_9.flat();
49  const sheetB_Offer_Flat_10 = sheetB_Offer_10.flat();
50
51  for(let i = 0; i < sheetB_Date_Flat.length; i++){
52    for(let j = 0; j < sheetB_Offer_Flat.length; j++){
53      for(let k = 0; k < sheetB_Campaign_Flat.length; k++){
54        const arrayFilter = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat[j] && value[16] == sheetB_Campaign_Flat[k]);
55        ss.getSheetByName("offer_test").getRange(3 + i, 2 + j).setValue(arrayFilter.length);
56        const arrayFilter_2 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_2[j] && value[16] == sheetB_Campaign_Flat_2[k]);
57        ss.getSheetByName("offer_test").getRange(38 + i, 2 + j).setValue(arrayFilter_2.length);
58        const arrayFilter_3 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_3[j] && value[16] == sheetB_Campaign_Flat_3[k]);
59        ss.getSheetByName("offer_test").getRange(73 + i, 2 + j).setValue(arrayFilter_3.length);
60        const arrayFilter_4 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_4[j] && value[16] == sheetB_Campaign_Flat_4[k]);
61        ss.getSheetByName("offer_test").getRange(108 + i, 2 + j).setValue(arrayFilter_4.length);
62        const arrayFilter_5 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_5[j] && value[16] == sheetB_Campaign_Flat_5[k]);
63        ss.getSheetByName("offer_test").getRange(143 + i, 2 + j).setValue(arrayFilter_5.length);
64        const arrayFilter_6 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_6[j] && value[16] == sheetB_Campaign_Flat_6[k]);
65        ss.getSheetByName("offer_test").getRange(178 + i, 2 + j).setValue(arrayFilter_6.length);
66        const arrayFilter_7 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_7[j] && value[16] == sheetB_Campaign_Flat_7[k]);
67        ss.getSheetByName("offer_test").getRange(213 + i, 2 + j).setValue(arrayFilter_7.length);
68        const arrayFilter_8 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_8[j] && value[16] == sheetB_Campaign_Flat_8[k]);
69        ss.getSheetByName("offer_test").getRange(248 + i, 2 + j).setValue(arrayFilter_8.length);
70        const arrayFilter_9 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_9[j] && value[16] == sheetB_Campaign_Flat_9[k]);
71        ss.getSheetByName("offer_test").getRange(283 + i, 2 + j).setValue(arrayFilter_9.length);
72        const arrayFilter_10 = sheetA.filter(value => value[2] == String(sheetB_Date_Flat[i]) && value[3] == 900 && value[54] == sheetB_Offer_Flat_10[j] && value[16] == sheetB_Campaign_Flat_10[k]);
73        ss.getSheetByName("offer_test").getRange(318 + i, 2 + j).setValue(arrayFilter_10.length);
74
75      }
76    }
77  }
78}

試したこと

getRangeListを活用して、APIの呼び出し回数を減らそうとしましたが
行き詰まってしまい、ヒントなど頂けたら嬉しいです

補足情報（FW/ツールのバージョンなど）

Google App Script

YAmaGNZ

2023/02/22 10:35

ループ内のシートへのアクセスを配列などに代入してあとから一括でセットできたりしませんか？また微々たるものかもしれませんが ss.getSheetByName("offer_test") と何度も呼ぶのであれば、そのシートを変数に入れておけばいいのではないでしょうか

kitaru

2023/03/01 08:40

アドバイスありがとうございます！

行動規範の内容に同意します

回答1件

ベストアンサー

「setValue」を何度も行うことに時間がかかっていると思うのでこういった感じにしたら多少早くなると思います。

function GenerateDailyOfferReport() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetA = ss.getSheetByName("シートA").getDataRange().getValues();
  const sheetB_Date = ss.getSheetByName("シートB").getRange("A3:A33").getValues();
  const sheetB_Campaign = ss.getSheetByName("シートB").getRange("C1").getValues();
  const sheetB_Offer = ss.getSheetByName("シートB").getRange("B2:Z2").getValues();

  const sheetB_Date_Flat = sheetB_Date.flat();
  const sheetB_Campaign_Flat = sheetB_Campaign.flat();
  const sheetB_Offer_Flat = sheetB_Offer.flat();

  let array1=[];
  let array2=[];

  for(let i = 0; i < sheetB_Date_Flat.length; i++){
    for(let j = 0; j < sheetB_Offer_Flat.length; j++){
      for(let k = 0; k < sheetB_Campaign_Flat.length; k++){
        const arrayFilter = sheetA.filter(value => value[0] == String(sheetB_Date_Flat[i]) && value[1] == 900 && value[2] == sheetB_Offer_Flat[j] && value[3] == sheetB_Campaign_Flat[k]);
        array1.push(arrayFilter.length);
      }
    }
    array2.push(array1);
    array1 = [];
  }
  // console.log(array2);
  ss.getSheetByName("シートB").getRange(3 , 2,sheetB_Date_Flat.length,sheetB_Offer_Flat.length).setValues(array2);
}

後、35行ずつ同じ動作するのならこんな感じでも

function GenerateDailyOfferReport() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetA = ss.getSheetByName("シートA").getDataRange().getValues();
  const shB = ss.getSheetByName("シートB");
  
  for(let h = 0; h <= 8; h++){
    const sheetB_Date = shB.getRange((h * 35) + 3,1,1,31).getValues();
    const sheetB_Campaign = shB.getRange(1,3,1,1).getValues();
    const sheetB_Offer = shB.getRange((h * 35) + 2,2,1,26).getValues();
    const sheetB_Date_Flat = sheetB_Date.flat();
    const sheetB_Campaign_Flat = sheetB_Campaign.flat();
    const sheetB_Offer_Flat = sheetB_Offer.flat();
    
    const array2 = [];

    for(let i = 0; i < sheetB_Date_Flat.length; i++){
      const array1 = [];
      for(let j = 0; j < sheetB_Offer_Flat.length; j++){
        for(let k = 0; k < sheetB_Campaign_Flat.length; k++){
          const arrayFilter = sheetA.filter(value => value[0] == String(sheetB_Date_Flat[i]) && value[1] == 900 && value[2] == sheetB_Offer_Flat[j] && value[3] == sheetB_Campaign_Flat[k]);
          array1.push(arrayFilter.length);
        }
      }
      array2.push(array1);
    }
    shB.getRange((h * 35) + 3, 2, sheetB_Date_Flat.length, sheetB_Offer_Flat.length).setValues(array2);
  }
}

投稿2023/02/24 01:41

編集2023/02/24 04:47