スクリプトでの動作(行を追加等)

###　前提　
工数管理表を作成し、運用中です。
しかしながらシート数が多いのと、月ごとにシートを作りその中に週ごとの合計を管理しているので動作がかなり遅くなっています。
改善方法をご教授いただければ幸いです。
シート名「シート1」の14行目までは固定です。
新しく追加するときは、15行目と16行目に積み上げていく方式です。
右に毎日の工数時間を入力していきます。
工数時間は、各月のシートと連動しています。

実現したいこと

シート名「シート１」
１）追加ボタンを押すと15行目と16行目に列を追加する。(毎回)
C15(C16)=「=if(OR($C15(C16)="",$C15(C16<10006,$C15(C16)>700000),"",vlookup($C15(C16),'リスト'!$A$3:$G$2999,6,false))」セルに数式を表示
E15(E16)=「if(OR($C15(C16)="",$C15<10006,$C15(C16)>700000),"",vlookup($C15(C16),'リストDB'!$A$3:$G$2999,7,false))」セルに数式を表示　　
F15＝「Before」セルに表示、バックカラーがピンク
F16＝「After」セルに表示、バックカラーが青
I15=バックカラー黄色
I16＝「－」セルに表示
J15(J16)=sum(K15(K16):NK15(NK16))セルに数式を表示

シート名「7月、8月、、、、、、、6月」
２）計算ボタンを押して結果の値だけ各セルに反映する
現在の数式
B3=ARRAYFORMULA(SUM(('シート1'!$K$3:$NK$3=$A$1)('シート1'!$K$4:$NK$4=B$1)('シート1'!$F$6:$F$500=$A3)'シート1'!$K$6:$NK$500))
B4==ARRAYFORMULA(SUM(('シート1'!$K$3:$NK$3=$A$1)('シート1'!$K$4:$NK$4=B$1)('シート1'!$F$6:$F$500=$A4)'シート1'!$K$6:$NK$500))
C3==ARRAYFORMULA(SUM(('シート1'!$K$3:$NK$3=$A$1)('シート1'!$K$4:$NK$4=C$1)('シート1'!$F$6:$F$500=$A3)*'シート1'!$K$6:$NK$500))
上記の様に数式がはいっています。

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

「サービスがタイムアウトしました: スプレッドシート」
「起動時間の最大値を超えました」

該当のソースコード

シート名「シート１」
function 工数表() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('シート1');
var note_b = "行程1"+String.fromCharCode(10)+"行程2"+String.fromCharCode(10)+"その他";
var note_a = "行程1"+String.fromCharCode(10)+"行程2"+String.fromCharCode(10)+"その他";
var note_db = "行程1" + String.fromCharCode(10) + "行程2";
var note_da = "行程1";

var ave = sheet.getRange("C15:C16");

sheet.insertRowsBefore(15,2);
sheet.getRange(15,3).setBackground("#FFFCDB");
sheet.getRange(16,3).setBackground("#ffffff");
sheet.getRange(15,9).setBackground("#FFFCDB");
sheet.getRange(16,9).setBackground("#ffffff");

sheet.getRange(15,6).setBackground("#FADBDA");
sheet.getRange(16,6).setBackground("#D3DEF1");

ave.setBorder(true,true,true,true,true,true);
sheet.getRange("A:A").setNumberFormat("yyyy/MM/dd");
sheet.getRange(15,1).setNote(before_day);
sheet.getRange(16,1).setNote(after_day);

for(var i = 15;i <17;i++){
sheet.getRange("B" + i).setFormula("=if(or(" + "A" + i + "="",today()>" + "A" + i + "),"",NETWORKDAYS(today()," + "A" + i + ",reference!$B$2:$B$30))");
}

sheet.getRange(15,4).setFormula("=if(OR($C15="",$C15<10006,$C15>700000),"",vlookup($C15,'リストDB'!$B$4:$H$3000,6,false))");
sheet.getRange(15,5).setFormula("=if(OR($C15="",$C15<10006,$C15>700000),"",vlookup($C15,'リストDB'!$B$4:$H$3000,7,false))");

sheet.getRange(15,6).setValue("Before").setNote(note_b);
sheet.getRange(16,6).setValue("After").setNote(note_a);
sheet.getRange(16,9).setValue("－");

for(var i = 15;i <17;i++){
sheet.getRange("J" + i).setFormula("=if(sum(" + "K" + i + ":" + "NK" + i + ")=0,"",sum(" + "K" + i + ":" + "NK" + i + "))");
}
}

シート名「7月、8月、、、、、、、6月」
function summaryyy() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var Values = sheet.getDataRange().getValues();

　for (var j=0; j<=Values.length-j; j++){
// 目的のセルに計算式を挿入
Values = sheet.getRange(j,2).setFormula("ARRAYFORMULA(SUM(('シート1'!$K$3:$NK$3=$A$1)('シート1'!$K$4:$NK$4=B$1)('シート1'!$F$6:$F$500=$A3)*'シート1'!$K$6:$NK$500))");//　関数を設定して演算

// 計算後、値のみコピーで貼り替える
}

sheet.getRange(3,2,13,2).setValues(Values);
}

Google Apps Script

試したこと

配列を使ったりしたのですが、うまくいきません。

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

これが現在作り直しているシートです。
https://docs.google.com/spreadsheets/d/e/2PACX-1vRb-LqgjJ2K9mF7Iapsc5G04EI5H_PD0pBVEiXs9gR87W2Fct6flZWV-uw9Y-Qgw9QaX0upPv7Anbkn/pubhtml

変更スクリプト

function 工数表() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('工数表');
var zyutakumae = "仕様書確認" + String.fromCharCode(10) + "情報収集" + String.fromCharCode(10) + "ページネーション" + String.fromCharCode(10) + "社内MTG" + String.fromCharCode(10) + "組み手MTG" + String.fromCharCode(10) + "企画書作成" + String.fromCharCode(10) + "書類作成" + String.fromCharCode(10) + "コンペ" + String.fromCharCode(10) + "雑務" + String.fromCharCode(10) + "その他";
var zyutakugo = "キックオフMTG" + String.fromCharCode(10) + "社内MTG" + String.fromCharCode(10) + "組み手MTG" + String.fromCharCode(10) + "実行（納品）" + String.fromCharCode(10) + "書類作成･提出" + String.fromCharCode(10) + "雑務" + String.fromCharCode(10) + "その他";
var before_day = "コンペ日" + String.fromCharCode(10) + "入札日";
var after_day = "履行日";
var ave = sheet.getRange("C15:C16");
var Array = [];

Array[0] = sheet.insertRowsBefore(15,2);

Array[0] =sheet.getRange(15,3).setBackground("#FFFCDB");
Array[1] =sheet.getRange(16,3).setBackground("#ffffff");
Array[2] =sheet.getRange(15,9).setBackground("#FFFCDB");
Array[3] =sheet.getRange(16,9).setBackground("#ffffff");

Array[4] =sheet.getRange(15,6).setBackground("#FADBDA");
Array[5] =sheet.getRange(16,6).setBackground("#D3DEF1");

Array[6] =ave.setBorder(true,true,true,true,true,true);
Array[7] =sheet.getRange("A:A").setNumberFormat("yyyy/MM/dd");
Array[8] =sheet.getRange(15,1).setNote(before_day);
Array[9] =sheet.getRange(16,1).setNote(after_day);

for(var i = 15;i <17;i++){
Array[10] =sheet.getRange("B" + i).setFormula("=if(or(" + "A" + i + "="",today()>" + "A" + i + "),"",NETWORKDAYS(today()," + "A" + i + ",reference!$B$2:$B$30))");
}

Array[11] =sheet.getRange(15,4).setFormula("=if(OR($C15="",$C15<10006,$C15>700000),"",vlookup($C15,'自治体リストDB'!$B$4:$H$3000,6,false))");
Array[12] =sheet.getRange(15,5).setFormula("=if(OR($C15="",$C15<10006,$C15>700000),"",vlookup($C15,'自治体リストDB'!$B$4:$H$3000,7,false))");

Array[13] =sheet.getRange(15,6).setValue("受託前").setNote(zyutakumae);
Array[14] =sheet.getRange(16,6).setValue("受託後").setNote(zyutakugo);
Array[15] =sheet.getRange(16,9).setValue("同上");

for(var i = 15;i <17;i++){
Array[16] =sheet.getRange("J" + i).setFormula("=if(sum(" + "K" + i + ":" + "NK" + i + ")=0,"",sum(" + "K" + i + ":" + "NK" + i + "))");
}
sheet.sheet.getRange().setFormulas(Array);
}

jinshan

2018/02/15 06:35 編集

.setFormulaに対して.setFormulasという、.setValueと.setValuesの関係に似たMetodがあります。.setValuesはお使いになられているので、同じ要領で配列に事前に式を代入した上で.setFormulasで一気にセルに流し込んだら処理時間短縮になると思います。まずはLogger.log('point1：' +new Date())のようなものをところどころに差し込んで、どこで経過時間を要しているか、チェックしてみてください。（←タイムアウトで完走できない場合は途中でreturn（＝関数の途中終了）を挿入して途中経過時間を確認しながら）

yyyyhhhhoooo

2018/02/19 00:33

回答ありがとうございます。上記の通り、setForulasを使用してみたのですがうまくいきません。配列に格納するまででエラーがでてしまいます。

jinshan

2018/02/19 00:40

setForulasを使ったときのスクリプトは質問に追記できますか？

yyyyhhhhoooo

2018/02/19 00:44

変更後を追加しました。よろしくお願いします。

jinshan

2018/02/19 01:18

setFormula()やsetFormulas()はセルに数式をセットしたい時に使うものですので、最初のスクリプトのfor iループ・jループのみをsetFormulas()に置換でよかったと思います。また修正後のスクリプトでのArrayはsetFormulas()で使用したい配列ですので0～9、13～15要素に代入している内容は同じArrayに代入すべきではないですし、set…の実行命令ですのでそもそも配列にセットする意味はないと思います。最後の命令文ですが、getRange()の()内に範囲指定がないと、setValues()もsetFormulas()も実行できず、かつ配列を2次元配列にしてその配列の大きさと範囲の大きさを一致させないとエラーになります。2次元配列に関する例は私の質問回答 https://teratail.com/questions/112365#reply-171299 も参考にしてください。このようにスクリプトがうまく動いているのか、変数や配列に実行途中で何が入っているか追うためにデバッグモードがあります。止めたい行の行番号をクリックし赤●を付けて、上部の虫(デバッグ)ボタンをクリックします。赤●部分で実行が止まりますので、あとは1行ずつ「ステップインボタン」で中間結果を追っていくか、複数の赤●をつけて「デバッグを続行」で複数の場所で止めて配列・変数・実行文途中状態のセル内容を確認してみてください

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

回答1件

修正されたコードについていくつか。

基本的にはセルごとに処理をしているので時間がかかっています。

連続したセル（行や列も含め）単位で、まとめて処理できる部分はまとめましょう。
そうすれば多少は改善すると思いますよ。
特にfor文中で都度、getRange()、setFormula()を実行していますが、処理遅延・負荷の原因となります。
（そもそも2回しか回していないようですので、そこはsetFormulas()でまとめて処理したほうが良いでしょう）

変数の文字列中、String.fromCharCode(10)として+演算子で連結されていますが、視認性が悪いですよね。

つまりメンテナンスしづらい。
そこは「\n」で良いと思いますが、そのように記述した意図があるのでしょうか。

Arrayに処理結果をセットしていますが、その後のArrayの状態をイメージできていますか？

ほとんどが当該シートの当該セルに対して処理を行った後、Rangeオブジェクトをセットしていますが、無駄な処理ですよね。
恐らく最後でまとめて処理したつもりかと思います。
タイムアウトがなかったとしても、最後でエラーが出ますね。
ただし、期待している結果が出た後なので「？？？」と思うかもしれませんけど。

初心者申告されたご質問でしたので、最低限の修正をしました。
setValue()とsetValues()、setFormula()とsetFormulas()の違いなどはおさえたほうが良いでしょう。

なお、動作確認などはしておりませんので参考までに。
特に数式に関しましては再確認してくださいね。

javascript
1function 工数表() {
2	var ss         = SpreadsheetApp.getActiveSpreadsheet();
3	var sheet      = ss.getSheetByName('工数表');
4	var zyutakumae = "仕様書確認\n"+
5					 "情報収集\n"+
6					 "ページネーション\n"+
7					 "社内MTG\n"+
8					 "組み手MTG\n"+
9					 "企画書作成\n"+
10					 "書類作成\n"+
11					 "コンペ\n"+
12					 "雑務\n"+
13					 "その他";
14	var zyutakugo  = "キックオフMTG\n"+
15	                 "社内MTG\n"+
16	                 "組み手MTG\n"+
17	                 "実行（納品）\n"+
18	                 "書類作成･提出\n"+
19	                 "雑務\n"+
20	                 "その他";
21	var before_day = "コンペ日\n入札日";
22	var after_day  = "履行日";
23	var ave        = sheet.getRange("C15:C16");
24
25	sheet.insertRowsBefore(15,2);
26
27	sheet.getRange(15, 3, 2, 7) //C15:I16までをまとめて背景処理
28		 .setBackgrounds([
29		 	["#FFFCDB","#ffffff","#ffffff","#FADBDA","#ffffff","#ffffff","#FFFCDB"],
30		 	["#ffffff","#ffffff","#ffffff","#D3DEF1","#ffffff","#ffffff","#ffffff"]
31		 ]);
32
33	ave.setBorder(true,true,true,true,true,true);
34	//sheet.getRange("A:A").setNumberFormat("yyyy/MM/dd");  //←この処理はシートに直接設定すれば良い
35	sheet.getRange(15, 1, 2, 1).setNotes([[before_day],[after_day]]);　//A15:A16にメモ挿入
36
37	sheet.getRange("B15:B16)
38		 .setFormulas([
39			['=if(or(A15="",today()>A15),"",NETWORKDAYS(today(),A15,reference!$B$2:$B$30))'],
40			['=if(or(A16="",today()>A16),"",NETWORKDAYS(today(),A16,reference!$B$2:$B$30))']
41		 ]);
42
43	sheet.getRange(15, 4, 1, 2)
44		 .setFormula([
45			['=if(OR($C15="",$C15<10006,$C15>700000),"",vlookup($C15,'自治体リストDB'!$B$4:$H$3000,6,false))',
46			'=if(OR($C15="",$C15<10006,$C15>700000),"",vlookup($C15,'自治体リストDB'!$B$4:$H$3000,7,false))']
47		]);
48
49	sheet.getRange(15, 6, 2, 1)
50		 .setValues([
51		 	["受託前"],
52		 	["受託後"]
53		 ])
54		 .setNote([
55		 	[zyutakumae],
56		 	[zyutakugo]
57		 ]);
58	sheet.getRange(16,9).setValue("同上");
59
60	sheet.getRange("J15:J16")
61		 .setFormula([
62		 	['=if(sum(K15:NK15)=0,"",sum(K15:NK15))'],
63		 	['=if(sum(K16:NK16)=0,"",sum(K16:NK16))']
64		 ]);
65
66	//sheet.sheet.getRange().setFormulas(Array);  //不要というより意味不明ですよ
67}