【GAS】処理速度をあげたい【タイムアウトエラー】

実現したいこと

下記コードで正常に動作しますが、データが4万ほどありタイムアウトしてしまいます。
現状データを上から順にあてていくコードになっていますが処理速度をあげる書き方ありますでしょうか。。

発生している問題・分からないこと

データが多くタイムアウトしてしまう

エラーメッセージ

error
1「起動時間の最大値を超えました」

該当のソースコード

GAS
1function transferData() {
2  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('元データ');
3  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート２');
4  var sheet3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート３');
5  var sheet4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート４');  
6 
7  var data = sheet1.getDataRange().getValues();
8  var lastRowSheet2 = sheet2.getRange(sheet2.getLastRow(), 1).getRow() + 1;
9  var lastRowSheet3 = sheet3.getRange(sheet3.getLastRow(), 1).getRow() + 1;
10  var lastRowSheet4 = sheet4.getRange(sheet4.getLastRow(), 1).getRow() + 1;
11  var today = Utilities.formatDate(new Date(), "JST", "MM/dd");
12
13
14～各シートの列番号取得～
15
16  // 該当するデータを転記
17  for (var i = 1; i < data.length; i++) {
18    var Value1 = data[i][s1_syubetu];
19    var Value2 = data[i][s1_rcid]; 
20    var Value3 = data[i][s1_kekka1];
21    var Value4 = data[i][s1_kekka2];
22    var Value5 = data[i][s1_kekka3]; 
23    var Value6 = data[i][s1_kekka4];
24
25    // シート２
26    // 条件1: "★"または"▲"
27    // 条件2: IDが重複していない
28    if ((Value1 === "★" || Value1 === "▲") && !isDuplicate(Value2, sheet2, 3, lastRowSheet2)) {
29
30      // 転記
31      sheet2.getRange(lastRowSheet2, s2_today).setValue(today)
32      sheet2.getRange(lastRowSheet2, s2_sakuseisya).setValue(data[i][s1_sakuseisya])
33      sheet2.getRange(lastRowSheet2, s2_rcid).setValue(data[i][s1_rcid])
34      sheet2.getRange(lastRowSheet2, s2_sakuseibi).setValue(data[i][s1_sakuseibi]) 
35      sheet2.getRange(lastRowSheet2, s2_nyuukyubi).setValue(data[i][s1_nyuukyubi])
36      sheet2.getRange(lastRowSheet2, s2_cmp).setValue(data[i][s1_cmp])
37      sheet2.getRange(lastRowSheet2, s2_tenpo).setValue(data[i][s1_tenpo])
38      sheet2.getRange(lastRowSheet2, s2_tantou).setValue(data[i][s1_tantou])
39      lastRowSheet2++;
40    }
41
42    // シート３
43    // 条件1:「結果1」「結果2」「結果3」「結果4」のどれかに”●”
44    // 条件2: IDが重複していない
45    if ((Value3 === "●" || Value4 === "●" || Value5 === "●" || Value6 === "●") && !isDuplicate(Value2, sheet3, 3, lastRowSheet3)) {
46
47      // 転記
48      sheet3.getRange(lastRowSheet3, s3_today).setValue(today)
49      sheet3.getRange(lastRowSheet3, s3_sakuseibi).setValue(data[i][s1_sakuseibi])
50      sheet3.getRange(lastRowSheet3, s3_rcid).setValue(data[i][s1_rcid])
51      sheet3.getRange(lastRowSheet3, s3_syubetu).setValue(data[i][s1_syubetu]) 
52      sheet3.getRange(lastRowSheet3, s3_nyuukyubi).setValue(data[i][s1_nyuukyubi])
53      sheet3.getRange(lastRowSheet3, s3_cmp).setValue(data[i][s1_cmp])
54      sheet3.getRange(lastRowSheet3, s3_tenpo).setValue(data[i][s1_tenpo])
55      lastRowSheet3++;
56    }
57
58    // シート４
59    // 条件1:「結果1」「結果2」「結果3」「結果4」のどれかに”◎”
60    // 条件2: レコードIDが重複していない
61    if ((Value3 === "◎" || Value4 === "◎" || Value5 === "◎" || Value6 === "◎") && !isDuplicate(Value2, sheet4, 3, lastRowSheet4)) {
62
63      // 転記
64      sheet4.getRange(lastRowSheet4, s4_today).setValue(today)
65      sheet4.getRange(lastRowSheet4, s4_sakuseibi).setValue(data[i][s1_sakuseibi])
66      sheet4.getRange(lastRowSheet4, s4_rcid).setValue(data[i][s1_rcid])
67      sheet4.getRange(lastRowSheet4, s4_nyuukyubi).setValue(data[i][s1_nyuukyubi])
68      sheet4.getRange(lastRowSheet4, s4_cmp).setValue(data[i][s1_cmp])
69      sheet4.getRange(lastRowSheet4, s4_tenpo).setValue(data[i][s1_tenpo])
70      sheet4.getRange(lastRowSheet4, s4_tantou).setValue(data[i][s1_tenpo])
71      sheet4.getRange(lastRowSheet4, s4_bikou).setValue(data[i][s1_bikou])
72      lastRowSheet4++;
73    } 
74  }
75  // テキストボックス表示
76  var ui = SpreadsheetApp.getUi();
77  ui.alert('完了しました', ui.ButtonSet.OK);
78}
79// 重複チェック
80function isDuplicate(value, sheet, column, poge) {
81  var columnData = sheet.getRange(2, column, poge - 1, 1).getValues().flat();
82  return columnData.includes(value);
83}

試したこと・調べたこと

teratailやGoogle等で検索した
ソースコードを自分なりに変更した
知人に聞いた
その他

上記の詳細・結果

処理の並列化など調べましたがこのコードにどう適応するのか思いつかなかったため。

補足

特になし

kikukiku

2024/02/29 02:19

処理を早くするというアプローチではなく、６分の壁を超える方法を検討した方が良いと思いました。下記の情報が参考になると思います。 https://uncle-gas.com/avoid-timeout-error/

yosy

2024/02/29 02:44

こちらの記事も読んでいたのですが（30分上限に該当）そもそも30分も処理にかかるのがネックだったので質問させていただきました。やりようがなければ処理を分割するこちらの方法も検討したいと思います。ありがとうございます！

YellowGreen

2024/02/29 03:24

ご提示のスクリプトは、転記処理がセル単位で行われていたり、重複チェックに毎回列の値を取得しているなど、大量のデータを処理することを想定していないものとなってます。タイムアウトの対策として考えられるのは、元のシートからシート2～4への転記の処理を 1) 今は元データのみ配列で処理しているのを、重複チェックも含めて配列で処理し、転記用の配列を生成した上で、結果の配列を一括でシートに転記できるのではないか。 2) それぞれのシートごとのスクリプトに分割して処理ことができるのではないか。ということでしょうか。

yosy

2024/02/29 07:06

YellowGreen様ご回答ありがとうございます！ーーーーーーーーーーーーーーーーーーーーーーーーーーーー 2) それぞれのシートごとのスクリプトに分割して処理ことができるのではないか。ーーーーーーーーーーーーーーーーーーーーーーーーーーーーこちらで進めていきたいのですが、それぞれForで処理する、で良いのでしょうか？

YellowGreen

2024/02/29 10:40

順序としては、1) → 2)だと思いますので、 1)について、回答欄に記しましたので試してみてください。

yosy

2024/03/01 02:56

１）で処理速度が格段に上がりましたので２）の処理は大丈夫かなと思います。本当にありがとうございました！！

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

回答1件

ベストアンサー

ひとまず、次のコードを試してみてください。

JavaScript
1function transferData() {
2  const ss = SpreadsheetApp.getActiveSpreadsheet();
3  const sheet1 = ss.getSheetByName('元データ');
4  const sheet2 = ss.getSheetByName('シート２');
5  const sheet3 = ss.getSheetByName('シート３');
6  const sheet4 = ss.getSheetByName('シート４');
7
8  const data = sheet1.getDataRange().getValues();
9  const lastRow2 = sheet2.getLastRow() + 1;
10  const lastRow3 = sheet3.getLastRow() + 1;
11  const lastRow4 = sheet4.getLastRow() + 1;
12  const checkData2 = new Map(sheet2.getRange(2, 3, lastRow2 - 2, 1).getValues());
13  const checkData3 = new Map(sheet3.getRange(2, 3, lastRow3 - 2, 1).getValues());
14  const checkData4 = new Map(sheet4.getRange(2, 3, lastRow4 - 2, 1).getValues());
15  const today = Utilities.formatDate(new Date(), "JST", "MM/dd");
16
17  // 該当するデータを転記
18  const data2 = [];
19  const data3 = [];
20  const data4 = [];
21  for (const d of data) {
22    const Value1 = d[s1_syubetu];
23    const Value2 = d[s1_rcid];
24    const Value3 = d[s1_kekka1];
25    const Value4 = d[s1_kekka2];
26    const Value5 = d[s1_kekka3];
27    const Value6 = d[s1_kekka4];
28
29    // シート２
30    // 条件1: "★"または"▲"
31    // 条件2: IDが重複していない
32    if ((Value1 === "★" || Value1 === "▲") && !checkData2.has(Value2)) {
33      const d2 = [];
34      d2[s2_today - 1] = today;
35      d2[s2_sakuseisya - 1] = d[s1_sakuseisya];
36      d2[s2_rcid - 1] = d[s1_rcid];
37      d2[s2_sakuseibi - 1] = d[s1_sakuseibi];
38      d2[s2_nyuukyubi - 1] = d[s1_nyuukyubi];
39      d2[s2_cmp - 1] = d[s1_cmp];
40      d2[s2_tenpo - 1] = d[s1_tenpo];
41      d2[s2_tantou - 1] = d[s1_tantou];
42      data2.push(d2);
43    }
44
45    // シート３
46    // 条件1:「結果1」「結果2」「結果3」「結果4」のどれかに”●”
47    // 条件2: IDが重複していない
48    if ((Value3 === "●" || Value4 === "●" || Value5 === "●" || Value6 === "●") && !checkData3.has(Value2)) {
49      const d3 = [];
50      d3[s3_today - 1] = today;
51      d3[s3_sakuseibi - 1] = d[s1_sakuseibi];
52      d3[s3_rcid - 1] = d[s1_rcid];
53      d3[s3_syubetu - 1] = d[s1_syubetu];
54      d3[s3_nyuukyubi -1 ] = d[s1_nyuukyubi];
55      d3[s3_cmp - 1] = d[s1_cmp];
56      d3[s3_tenpo- 1] = d[s1_tenpo];
57      data3.push(d3);
58    }
59
60    // シート４
61    // 条件1:「結果1」「結果2」「結果3」「結果4」のどれかに”◎”
62    // 条件2: レコードIDが重複していない
63    if ((Value3 === "◎" || Value4 === "◎" || Value5 === "◎" || Value6 === "◎") && !checkData4.has(Value2)) {
64      const d4 = [];
65      d4[s4_today - 1] = today;
66      d4[s4_sakuseibi - 1] = d[s1_sakuseibi];
67      d4[s4_rcid - 1] = d[s1_rcid];
68      d4[s4_nyuukyubi - 1] = d[s1_nyuukyubi];
69      d4[s4_cmp - 1] = d[s1_cmp];
70      d4[s4_tenpo - 1] = d[s1_tenpo];
71      d4[s4_tantou - 1] = d[s1_tantou]; // ★s1_tenpo??
72      d4[s4_bikou - 1] = d[s1_bikou];
73      data4.push(d4);
74    }
75  }
76
77  // 各シートへの記入
78  if (data2.length > 0) {
79    sheet2.getRange(lastRow2, 1, data2.length, data2[0].length).setValues(data2);
80  }
81  if (data3.length > 0) {
82    sheet3.getRange(lastRow3, 1, data3.length, data3[0].length).setValues(data3);
83  }
84  if (data4.length > 0) {
85    sheet4.getRange(lastRow4, 1, data4.length, data4[0].length).setValues(data4);
86  }
87  // テキストボックス表示
88  const ui = SpreadsheetApp.getUi();
89  ui.alert('完了しました', ui.ButtonSet.OK);
90}

投稿2024/02/29 10:37

YellowGreen

総合スコア731

yosy

2024/03/01 03:13 編集

YellowGreen様こちらのコードで試したところ、爆速で処理が完了しました…！！時間にして、1分弱です。また、 d4[s4_tantou - 1] = d[s1_tantou]; // ★s1_tenpo??　この部分につきましてもコードが誤っておりましたのでとても助かりました！処理自体は爆速になったのですが、転記結果が一部反映されておりません。。（シート３とシート４）元データの上部の方のデータが転記できていないようなので諸々確認していきますがもし何かお分かりでしたらご教示ください…！