### 前提
工数管理表を作成し、運用中です。
しかしながらシート数が多いのと、月ごとにシートを作りその中に週ごとの合計を管理しているので動作がかなり遅くなっています。
改善方法をご教授いただければ幸いです。
シート名「シート1」の14行目までは固定です。
新しく追加するときは、15行目と16行目に積み上げていく方式です。
右に毎日の工数時間を入力していきます。
工数時間は、各月のシートと連動しています。
実現したいこと
シート名「シート1」
1)追加ボタンを押すと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月」
2)計算ボタンを押して結果の値だけ各セルに反映する
現在の数式
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))
上記の様に数式がはいっています。
発生している問題・エラーメッセージ
「サービスがタイムアウトしました: スプレッドシート」 「起動時間の最大値を超えました」
該当のソースコード
シート名「シート1」
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);
}