@azazazazaposted at 2020-10-14

GASでの計画管理表作成について

Q&A

Closed

GoogleAppsScript GoogleSpreadSheet

解決したいこと

現在私は自分のGASの勉強の為に友達の計画管理表を作成しています。

様々な方の協力もあり、

１.項目列に色を塗る
２.開始日と終了日を記入
３.カレンダーに塗りたい色の項目を範囲選択
４.スクリプト実行

の、流れで自動的に開始日と終了日に該当するカレンダー範囲に色を塗るスクリプトができました。

しかしこのスクリプトだと、すべてに色を塗ろうとすると（例：4～11行の８行分）５分以上の時間がかかってしまいます。
多分ですが、var t = sh.getLastColumn();　で読み込む範囲が長ければ長いほど（月数が多いほど）です。
色塗りも一つ一つのセルを塗っていってます。

もう少し早く処理をするにはどのような方法がありますでしょうか？
また、それは可能でしょうか？

方法があれば、宜しくお願い致します。

ソースコード

function Color() {

var test = Browser.msgBox("色を塗りたい項目を選択した状態で「OK」を押してください。", Browser.Buttons.OK_CANCEL);
    if (test == 'ok') {
        Browser.msgBox("該当範囲に色を塗ります");
        var App=SpreadsheetApp
        var sh = App.getActiveSheet();
        var r= sh.getActiveRange();            //  選択範囲
        var n = r.getRow();                    // 選択範囲の開始行
        var h = r.getHeight();                 // 選択範囲の行数
        for (var i = n; i < n + h; i++) {
        var rng=sh.getRange(i,13);
        var rgb=rng.getBackground();
        var t = sh.getLastColumn();
        for(var j=16;j<=t;j++){
        if(sh.getRange(2,j).getValue()>=sh.getRange(i,14).getValue() && sh.getRange(2,j).getValue()<=sh.getRange(i,15).getValue()){
        sh.getRange(i,j).setBackground(rgb);
        }
        }
        }
        Browser.msgBox("色塗り処理が終わりました");
    }
    if (test == 'cancel') {

    }
}

0 likes

3Answer

@tetr4lab posted at 2020-10-14

手っ取り早く、「ループの内側で変化しない値はループの外側で算出しておく」だけで速くなります。
- 例えば、var t = sh.getLastColumn();は、タスク行のループ内で変化しません。
ループの内側の処理が少ないほど速くなります。
- 例えば、rngは一度しか使われませんので、以下のように書き替えることで、変数への代入を一つなくせます。var rng=sh.getRange(i,13); var rgb=rng.getBackground(); ⇒ var rgb=sh.getRange(i,13).getBackground();
タスク毎のガントチャート列を走査するループ範囲を最小限にする課題は、「タスクの開始日から終了日のカラムを算定して、その範囲だけループする」という解決手法になると思います。
- このスプレッドシートの使い方を以下のように仮定します。
  - ガントチャートの年月(1:1)は、見た目通りに「2020年10月」などと入力するものとします。
    - これによって、セルの値は「2020/10/01」となり、書式によって年月だけが表示されます。
  - ガントチャートの日(2:2)の値は、見た目通りに整数値のみが入力するものとします。
  - タスクの年月日(N:O)の値は、見た目通りに「10/5」などと入力するものとします。
    - これによって、セルの値は「2020/10/05」となり、書式によって月日だけが表示されます。
- 《ガントチャートの開始日》= P1 - 1 + P2です。
  - 2:2列をスキャンして、列が終わるか数字が現れなくなったら、ガントの終端です。
- 《タスクの開始列》=《ガントチャートの開始日》-《タスクの開始日》+《ガントチャートの開始列》です。
- 《タスクの終了列》=《タスクの終了日》-《タスクの開始日》+《タスクの開始列》です。
- ループにせずに、sh.getRange(i, 《タスクの開始列》, 1, 《タスクの終了列》-《タスクの開始列》+ 1).setBackground(rgb);みたいにできなかったでしょうか。
質問の範囲外ですが、以下のような課題も考えられます。
- マジックナンバーは、あらかじめ一箇所でまとめて定義しておいたほうが良いと思います。
  - 例えば、ガントチャートの開始列である16などがそれです。
- 選択範囲がタスク領域外を含まないようにチェックが必要だと思います。
  - 具体的には、「開始と終了の日付列に日付と認識できる値があるか」をチェックするとかでしょうか。
  - 他にも必要なエラーチェックがあると思います。
- 選択範囲のガントチャートをあらかじめクリアする必要があると思います。
  - 具体的には、sh.getRange(n, 16, h, 《ガントチャートの列幅》).setBackground("white");とかです。

2Like

Comments

@azazazaza
Questioner
tetr4lab.さん
前回同様、とても詳しく質問にお答えいただき誠にありがとうございます！

tetr4lab.さんの言う通りに改善してみました。
合っていますでしょうか

初心者質問で申し訳ないのですが、
<<ガントチャートの開始日>>である、P1 - 1 + P2をどのように整数として表現すればよろしいのでしょうか...

```
var App=SpreadsheetApp
var sh = App.getActiveSheet();
var r= sh.getActiveRange();
var n = r.getRow(); // 開始行
var h = r.getHeight(); // 行数
var t = sh.getLastColumn();
sh.getRange(n, 16, h).setBackground("white");
for (var i = n; i < n + h; i++) {
var rgb=sh.getRange(i,13).getBackground();
var ganttStartColumn = 16; // ガントチャートの開始カラム
var ganttStartDate = ; // ガントチャートの開始日
var taskStartColumn = ganttStartDate - sh.getRange(i,14) + ganttStartColumn; // タスク開始列
var taskEndColumn = sh.getRange(i,15) + sh.getRange(i,14) - taskStartColumn; // タスク終了列
for (var j = taskStartColumn; j <= taskEndColumn; j++){
if(sh.getRange(2,j).getValue()>=sh.getRange(i,14).getValue() && sh.getRange(2,j).getValue()<=sh.getRange(i,15).getValue()){
sh.getRange(i,j).setBackground(rgb);
}
}
}
Browser.msgBox("色塗り処理が終わりました");
}
if (test == 'cancel') {

}
}
```

@tetr4lab posted at 2020-10-14

こんな感じでいかがでしょうか。

【動作確認済み版】

function Color() {
    var test = Browser.msgBox ("色を塗りたい項目を選択した状態で「OK」を押してください。", Browser.Buttons.OK_CANCEL);
    if (test == 'ok') {
        Browser.msgBox ("該当範囲に色を塗ります");
        var sh = SpreadsheetApp.getActiveSheet ();
        var OneDay = new Date (1900, 1, 2) - new Date (1900, 1, 1); // 1日分の時間を表す数値
        var colorCol = 13; // 色の列
        var beginDateCol = 14; // 開始日の列
        var endDateCol = 15; // 終了日の列
        var ganttCol = 16; // ガントチャートの開始列
        var ganttWidth = sh.getLastColumn() - ganttCol + 1; // ガントチャートの横幅
        var ganttMonthLabelRow = 1; // ガントチャートの年月の行
        var ganttDayLabelRow = 2; // ガントチャートの日の行
        var ganttDate = sh.getRange (ganttMonthLabelRow, ganttCol).getValue ();
        ganttDate = new Date (ganttDate.getYear () + 1900, ganttDate.getMonth (), ganttDate.getDate () + (sh.getRange (ganttDayLabelRow, ganttCol).getValue () - 1)); // ガントチャートの開始日
        var selected = sh.getActiveRange (); // 選択範囲
        var selectedRow = selected.getRow (); // 選択範囲の開始行
        var selectedHeight = selected.getHeight (); // 選択範囲の行数
        sh.getRange (selectedRow, ganttCol, selectedHeight, ganttWidth).setBackground ("white"); // 既存のガントチャートをクリア
        for (var row = selectedRow; row < selectedRow + selectedHeight; row++) { // 選択範囲の各行をループ
            var begin = sh.getRange (row, beginDateCol).getValue (); // タスクの開始日
            var width = (sh.getRange (row, endDateCol).getValue () - begin) / OneDay + 1; // タスクの日数
            sh.getRange (row, ganttCol + (begin - ganttDate) / OneDay, 1, width).setBackground (sh.getRange (row, colorCol).getBackground ());   // タスクのガントチャートを記入
        }
        Browser.msgBox ("色塗り処理が終わりました");
    } else if (test == 'cancel') {

    }
}

修正して動作を確認しました。

ただし、エラーチェックの課題が解決していないことにご留意ください。

1Like

Comments

@azazazaza
Questioner
ありがとうございます。
テストしてみました。

結果、私自身初めて目にする

Exception: 「Thu Oct 08 2020 00:00:00 GMT+0900 (日本標準時)NaN」を int に変換できません。
at Color(コード:76:16)

というエラーがでました。
調べたのですが私の知識では理解できませんでした。

ご教授の程、宜しくお願い致します。
@tetr4lab
エラーが出ている行は`var ganttDate = sh.getRange (ganttMonthLabelRow, ganttCol).getValue () - 1 + sh.getRange (ganttDayLabelRow, ganttCol).getValue (); // ガントチャートの開始日`でしょうか?
@azazazaza
Questioner
返信が遅れてしまい、申し訳ございません。
エラーが出ている行は

`sh.getRange (row, begin, 1, width).setBackground (sh.getRange (row, colorCol).getBackground ()); // タスクのガントチャートを記入`

のようです
@tetr4lab
ご回答ありがとうございました。

直してみました。該当の行だけでなく、直前の行がなくなっています。
同じエラーが出るかどうか確認していただけないでしょうか。

`var begin = sh.getRange (row, beginDateCol).getValeu (); // タスクの開始日
var width = sh.getRange (row, endDateCol).getValeu () - begin + 1; // タスクの日数
sh.getRange (row, ganttCol + (begin - ganttDate), 1, width).setBackground (sh.getRange (row, colorCol).getBackground ()); // タスクのガントチャートを記入`
@azazazaza
Questioner
ありがとうございます。

実行してみたところ、

`sh.getRange (row, ganttCol + (begin - ganttDate), 1, width).setBackground (sh.getRange (row, colorCol).getBackground ()); // タスクのガントチャートを記入`

で、`Exception: 範囲の開始列の値が小さすぎます。
at Color`

というエラーが出てきました..

かなり前に見た、今回と同じエラーが出てきた際に
「getActiveSheet()ではなくgetSheetByName("シート名")を使って直接シートを指定」という変更を行ってみたのですが。
変わらず上記のエラーが出てきました...
@tetr4lab
「範囲の開始列の値が小さすぎ」ということで、
`getRange`の第二引数に問題があるようですから、
直前で`ganttCol`、`begin`、`ganttDate`の値を確認してみてください。
@azazazaza
Questioner
`ganttCol` 16
`begin` Thu Oct 08 2020 00:00:00 GMT+0900 (日本標準時)
`ganttDate` 1601477999999Thu Oct 01 2020 00:00:00 GMT+0900 (日本標準時)

というログが出ております。

`ganttDate`の Thuの前にある謎の数字がエラーの原因でしょうか？
@tetr4lab
はい。'ganttDate'の値がおかしいので、
'ganttDate'への代入式で、'-1'だったところを'- new Date (0, 0, 1)'にしてみてください。
@azazazaza
Questioner
長いことお付き合いいただき誠にありがとうございます。

その後変更してみて再度行ったのですが

`var ganttDate = sh.getRange (ganttMonthLabelRow, ganttCol).getValue () - new Date (0, 0, 1) + sh.getRange (ganttDayLabelRow, ganttCol).getValue (); // ガントチャートの開始日`

の時、同じように

3810499200000Thu Oct 01 2020 00:00:00 GMT+0900 (日本標準時)

という値になっていました。
`Exception: 範囲の開始列の値が小さすぎます。at Color`
という同じエラーです。
@tetr4lab
なるほど。
`3810499200000`が``var ganttDate = sh.getRange (ganttMonthLabelRow, ganttCol).getValue () - new Date (0, 0, 1) `の結果で、
`Thu Oct 01 2020 00:00:00 GMT+0900 (日本標準時)`が`sh.getRange (ganttDayLabelRow, ganttCol).getValue ()`の値ですね。
どうやら、`+`で文字列として連結されているようです。

セルP1の値(見かけの表示ではなく)はなんでしょうか?
その値は、最初の回答で「このスプレッドシートの使い方を以下のように仮定します。」と書かせていただいた要件を満たしているでしょうか?
@azazazaza
Questioner
なるほど...連結...

セルP1の値は「2020/10/01」です。見かけは「2020年10月」ですが。

```
‣このスプレッドシートの使い方を以下のように仮定します。
　　・ガントチャートの年月(1:1)は、見た目通りに「2020年10月」などと入力するものとします。
　　・これによって、セルの値は「2020/10/01」となり、書式によって年月だけが表示されます。
```

もしかして要件満たしていないでしょうか…？
@azazazaza
Questioner
申し訳ありません。今日の午後から日曜まで検査入院となるので、次に見れるのが月曜になるかもしれません…
@tetr4lab
了解しました。
実行環境を構築したので、解決後に報告いたします。
@tetr4lab
修正して動作を確認の上で、ソースコードを更新しました。
日付の計算は思ったより面倒でした。
細部にご不明な点があればお尋ねください。
@azazazaza
Questioner
おはようございます。

`sh.getRange (row, ganttCol + (begin - ganttDate) / OneDay, 1, width).setBackground (sh.getRange (row, colorCol).getBackground ()); // タスクのガントチャートを記入`

の列で、「範囲の開始列の値が小さすぎます。」というエラーが出ていました...

tetr4lab.さんの月表示が2020/10に対し、こちらの表示が2020年10月
となっているのが問題だったりするのでしょうか…？
@tetr4lab
年月の表示形式は関係ありません。
スクリプトエディタから実行すると、選択範囲が正しく取得できないことがあります。
シートにボタンを配置するかメニューを追加するなどして、シートから実行させてみてください。
@azazazaza
Questioner
はい、どちらの条件で行っても「範囲の開始列の値が小さすぎます。」というエラーが出ています。

新しくスプレッドシートを作成して再度行ってみたのですが、エラーが出ました。

曜日の列も削除してtetr4lab.さんの画像と同じ通りにしたのですが...
同じ条件のはずなのになぜでしょう...
@tetr4lab
選択範囲を正しく取得できないので、スクリプトエディタから実行しないでください。
「シートにボタンを配置する」か「メニューを追加する」などして、シートからスクリプトを実行させてください。
@tetr4lab
手っ取り早くボタンを作るには…

(1) 「挿入」メニュー > 「図形描画」で適当な図形を描き、保存して編集を終えます。
(2) シートに現れた図形を右クリックして、表示されたメニューアイコンを開き、「スクリプトを割り当て」を選んで、関数名の「Color」を入力します。
(3) 図形を左クリックしてスクリプトを実行します。(右クリックすれば再編集可能です。)
@azazazaza
Questioner
はい。存じ上げております。
ボタンを作成した上で、そのボタンからスクリプトを実行しているのですがエラーが発生しています。

もし可能であれば、お手数をおかけしますが、確認していただけると幸いです。

https://docs.google.com/spreadsheets/d/1z7_6l-Wc58oXN5mQvqBB8XfckuEO96ncsqWtAxOi9M8/edit?usp=sharing
@tetr4lab
シートの使い方の前提条件と異なって、`2:2`行が数値でなく日付になっていますね。
この使い方の場合は、スクリプトを少し変更する必要があります。
この後別記しますので、少しお待ちください。

@tetr4lab posted at 2020-10-19

`2:2`行に日付が入力される場合

このスプレッドシートの使い方を以下のように仮定します。

ガントチャートの年月(1:1)は、何が書かれていても無視します。
ガントチャートの日(2:2)の値は、見た目と異なり、セルの値として日付を「2020/10/01」などと入力するものとします。
- 書式設定によって日だけが表示されます。
タスクの年月日(N:O)の値は、見た目通りに「10/5」などと入力するものとします。
- これによって、セルの値は「2020/10/05」となり、書式によって月日だけが表示されます。

function Color() {
    var test = Browser.msgBox ("色を塗りたい項目を選択した状態で「OK」を押してください。", Browser.Buttons.OK_CANCEL);
    if (test == 'ok') {
        Browser.msgBox ("該当範囲に色を塗ります");
        var sh = SpreadsheetApp.getActiveSheet ();
        var OneDay = new Date (1900, 1, 2) - new Date (1900, 1, 1); // 1日分の時間を表す数値
        var colorCol = 13; // 色の列
        var beginDateCol = 14; // 開始日の列
        var endDateCol = 15; // 終了日の列
        var ganttCol = 16; // ガントチャートの開始列
        var ganttWidth = sh.getLastColumn() - ganttCol + 1; // ガントチャートの横幅
        var ganttDayLabelRow = 2; // ガントチャートの日の行
        var ganttDate = sh.getRange (ganttDayLabelRow, ganttCol).getValue (); // ガントチャートの開始日
        var selected = sh.getActiveRange (); // 選択範囲
        var selectedRow = selected.getRow (); // 選択範囲の開始行
        var selectedHeight = selected.getHeight (); // 選択範囲の行数
        sh.getRange (selectedRow, ganttCol, selectedHeight, ganttWidth).setBackground ("white"); // 既存のガントチャートをクリア
        for (var row = selectedRow; row < selectedRow + selectedHeight; row++) { // 選択範囲の各行をループ
            var begin = sh.getRange (row, beginDateCol).getValue (); // タスクの開始日
            var width = (sh.getRange (row, endDateCol).getValue () - begin) / OneDay + 1; // タスクの日数
            sh.getRange (row, ganttCol + (begin - ganttDate) / OneDay, 1, width).setBackground (sh.getRange (row, colorCol).getBackground ());   // タスクのガントチャートを記入
        }
        Browser.msgBox ("色塗り処理が終わりました");
    } else if (test == 'cancel') {

    }
}

1Like

Comments

@azazazaza
Questioner
返信が遅れて申し訳ありません。
エラーなく機能しました！
最初に書いたコードより圧倒的に早くて感動しております。

tetr4lab.さん。長い間、お付き合いいただき誠にありがとうございました...！
@tetr4lab
お役に立てたなら何よりでした。
解決に時間がかかってしまい申し訳ないです。
@azazazaza
Questioner
いえ、とても勉強になりました。
本当にありがとうございました。

Are you sure you want to delete the question?

GASでの計画管理表作成について

解決したいこと

ソースコード

3Answer

Comments

Comments

`2:2`行に日付が入力される場合

Comments

Your answer might help someone💌

GASでの計画管理表作成について

解決したいこと

ソースコード

3Answer

Comments

Comments

2:2行に日付が入力される場合

Comments

Your answer might help someone💌

`2:2`行に日付が入力される場合