Q&A
処理を早くするというアプローチではなく、
６分の壁を超える方法を検討した方が良いと思いました。
下記の情報が参考になると思います。
https://uncle-gas.com/avoid-timeout-error/
実現したいこと
下記コードで正常に動作しますが、データが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等で検索した
- ソースコードを自分なりに変更した
- 知人に聞いた
- その他
上記の詳細・結果
処理の並列化など調べましたがこのコードにどう適応するのか思いつかなかったため。
補足
特になし
こちらの記事も読んでいたのですが（30分上限に該当）
そもそも30分も処理にかかるのがネックだったので質問させていただきました。
やりようがなければ処理を分割するこちらの方法も検討したいと思います。
ありがとうございます！
ご提示のスクリプトは、
転記処理がセル単位で行われていたり、
重複チェックに毎回列の値を取得しているなど、
大量のデータを処理することを想定していないものとなってます。
タイムアウトの対策として考えられるのは、
元のシートからシート2～4への転記の処理を
1) 今は元データのみ配列で処理しているのを、
重複チェックも含めて配列で処理し、
転記用の配列を生成した上で、
結果の配列を一括でシートに転記できるのではないか。
2) それぞれのシートごとのスクリプトに分割して
処理ことができるのではないか。
ということでしょうか。
YellowGreen様
ご回答ありがとうございます！
ーーーーーーーーーーーーーーーーーーーーーーーーーーーー
2) それぞれのシートごとのスクリプトに分割して
処理ことができるのではないか。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーー
こちらで進めていきたいのですが、
それぞれForで処理する、で良いのでしょうか？
順序としては、1) → 2)だと思いますので、
1)について、回答欄に記しましたので試してみてください。
１）で処理速度が格段に上がりましたので２）の処理は大丈夫かなと思います。
本当にありがとうございました！！
回答1件
0
ベストアンサー
ひとまず、次のコードを試してみてください。
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
総合スコア827
YellowGreen様
こちらのコードで試したところ、爆速で処理が完了しました…！！
時間にして、1分弱です。
また、 d4[s4_tantou - 1] = d[s1_tantou]; // ★s1_tenpo?? この部分につきましてもコードが誤っておりましたのでとても助かりました！
処理自体は爆速になったのですが、
転記結果が一部反映されておりません。。（シート３とシート４）
元データの上部の方のデータが転記できていないようなので諸々確認していきますが
もし何かお分かりでしたらご教示ください…！
セルに含まれる●や◎が同じように見えて違う文字だったり、
●や◎の前後に空白が含まれていたりはしませんか。
現状、●のみが条件になっていましたが実際は●＊も該当だったため差異がでていたようです。
Excelのワイルドカードのように、●＊（●を含む文字列）を条件にしたいのですが可能でしょうか。。
VBAでいうValue Likeのような感じです。
例えば、
Value3.startsWith("●") // ●で始まる
とか
Value3.includes("●") // ●を含む
とか
2つの処理速度に違いがあるかもしれません。
includesで試してみたらすべて転記でき、処理時間も変わらず爆速でした！
なにからなにまでサポートしていただきありがとうございました！
あなたの回答
tips
プレビュー
