質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.51%

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

受付中

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,584

score 10

 前提 

工数管理表を作成し、運用中です。
しかしながらシート数が多いのと、月ごとにシートを作りその中に週ごとの合計を管理しているので動作がかなり遅くなっています。
改善方法をご教授いただければ幸いです。
シート名「シート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);
}

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • jinshan

    2018/02/19 09:40

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

    キャンセル

  • yyyyhhhhoooo

    2018/02/19 09:44

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

    キャンセル

  • jinshan

    2018/02/19 10: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

0

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

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

  2. 変数の文字列中、String.fromCharCode(10)として+演算子で連結されていますが、視認性が悪いですよね。
    つまりメンテナンスしづらい。
    そこは「\n」で良いと思いますが、そのように記述した意図があるのでしょうか。

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

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

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

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

    sheet.insertRowsBefore(15,2);

    sheet.getRange(15, 3, 2, 7) //C15:I16までをまとめて背景処理
         .setBackgrounds([
             ["#FFFCDB","#ffffff","#ffffff","#FADBDA","#ffffff","#ffffff","#FFFCDB"],
             ["#ffffff","#ffffff","#ffffff","#D3DEF1","#ffffff","#ffffff","#ffffff"]
         ]);

    ave.setBorder(true,true,true,true,true,true);
    //sheet.getRange("A:A").setNumberFormat("yyyy/MM/dd");  //←この処理はシートに直接設定すれば良い
    sheet.getRange(15, 1, 2, 1).setNotes([[before_day],[after_day]]); //A15:A16にメモ挿入

    sheet.getRange("B15:B16)
         .setFormulas([
            ['=if(or(A15="",today()>A15),"",NETWORKDAYS(today(),A15,reference!$B$2:$B$30))'],
            ['=if(or(A16="",today()>A16),"",NETWORKDAYS(today(),A16,reference!$B$2:$B$30))']
         ]);

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

    sheet.getRange(15, 6, 2, 1)
         .setValues([
             ["受託前"],
             ["受託後"]
         ])
         .setNote([
             [zyutakumae],
             [zyutakugo]
         ]);
    sheet.getRange(16,9).setValue("同上");

    sheet.getRange("J15:J16")
         .setFormula([
             ['=if(sum(K15:NK15)=0,"",sum(K15:NK15))'],
             ['=if(sum(K16:NK16)=0,"",sum(K16:NK16))']
         ]);

    //sheet.sheet.getRange().setFormulas(Array);  //不要というより意味不明ですよ
}

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.51%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る