ExcelやGoogleスプレッドシートを使って稼働スケジュールやシフト表を作るときのメモ
セルに色を塗って作る（Excelマクロ）
お題は「パソコンの操作ができない人でも操作できそうなもの」だったので
タイピング要素をできるだけ減らして、セルに色を塗るだけにしました
Excelマクロにユーザー関数を登録して、色が塗られているセルを数えて時間を計算します
1マスが1時間ならそのまま、30分なら0.5を掛けて何時間か計算できます
このやり方は、セルに色を塗っても自動計算してくれない欠点があります
適当なセルをダブルクリックして文字入力すれば動くと説明が必要です
マクロ
Function ColorCount(Rng As Range) As Long
Dim myRng As Range
Dim lngCount As Long'再計算時に呼び出し
Application.VolatilelngCount = 0
'指定範囲の塗りつぶされているセルの数を数える
For Each myRng In Rng
If myRng.Interior.ColorIndex > 0 Then
lngCount = lngCount + 1
End If
Next myRng
ColorCount = lngCountEnd Function
セルに色を塗って作る（Googleスプレッドシート＋GAS）
この仕組みをGoogleスプレッドシートに移植します
考えかたは同じなのでマクロというかGoogle Apps Scriptに同じ仕組みを組み込みます
注意点として
＝countColor(D4:BK4)
と、範囲指定しても動きません
＝countColor("D"&ROW()&":BK"&ROW())
と、「””」で囲ってあげると動きます
このやり方は大きな欠点が2つあります
・セルに色を塗っても再計算しない
これはExcelマクロも同じですが、Googleスプレッドシートはセルをダブルクリックしても動きません
列や行を追加して、そのユーザー関数が入ってるセルが動くと再計算されるようです
「refresh」という処理を作りましたが、列追加→再計算→列削除→再計算とムダに処理が走ってしまうので微妙
・処理に時間がかかる
Excelマクロは数秒で終わってましたが、Googleスプレッドシートだと数分待たないといけません
さすがに時間がかかりすぎるので実用には耐えられない気がします
・権限の許可が必要
これは大きな問題ではありませんが、初回起動時にマクロを動かす権限を許可しないといけません
許可を押すだけとはいえ、許可ボタンにたどり着くまでの導線が分かりづらいので説明が必要です
【GAS】スクリプトを実行する時に出る「承認」を許可する方法 | よろっと行こか -地方ITエンジニアのブログ-
GAS
function countColor(myRange) {
var app = SpreadsheetApp;
var ash = app.getActiveSheet();
var bgColors = ash.getRange(myRange).getBackgrounds();//白以外を数える
var targetColor = "#ffffff";
var count = 0;
//二次元配列bgColorsのすべての要素をZ字の順番でループする
for(var r = 0; r < bgColors.length; r++){
for(var c = 0; c < bgColors[r].length; c++) {
//toUpperCaseメソッドを使ってカラーコードを大文字にして比較
if(bgColors[r][c].toUpperCase() != targetColor.toUpperCase()){
count++
}
}
}
return count;
}function refresh() {
let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
//計算式が入ってるセルが行列の追加で移動したタイミングでマクロの再計算が走る
sheet.insertColumns(1, 1);
sheet.deleteColumns(1, 1);
};
時間を入力して作る（Excel、Googleスプレッドシート）
Googleスプレッドシートでも使えそうな仕組みに切り替えることにしました
開始時間と終了時間を入力して作ります
それだけだと分かりづらいので条件付き書式でタイムテーブルに色を塗って見栄えをよくしました
このやり方の欠点は、塗る色を変えられない、3回目の枠を取れない、あとは時間を「8：00」と入力するやり方が分からないと聞かれるんだろうなぁという予感があります
計算式
時間の計算
＝( (S6-R6)+(U6-T6))*24
条件付き書式で色を塗る
＝OR(AND($R6<=V$2,V$2<$S6),AND($T6<=V$2,V$2<$U6))
以上～未満と設定する
