GASでシフト作成 カスタムメニューから日単位・週単位・月単位を選択するだけ
過去にデイリーシフト、ウィークリーシフト、マンスリーシフトのコードを公開しましたが、今回は3つのシフトをカスタムメニューから選択できるようにしました。簡単なコードです。
コードは一つのファンクションにまとめています。GASの基本的な使い方などは過去のnoteからどうぞ。リンク張りました。
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('カスタムメニュー') // メニューのタイトルを指定
.addItem('デイリーシフト', 'デイリーシフト') // メニューに項目を追加し、クリック時に実行する関数を指定
.addItem('ウィークリーシフト', 'ウィークリーシフト')
.addItem('マンスリーシフト', 'マンスリーシフト')
.addToUi(); // メニューをUIに追加
}
function デイリーシフト() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
// シート名を「シフト表1」から始めて重複しない名前を見つける
let sheetName = 'デイリーシフト1';
let count = 1;
while (ss.getSheetByName(sheetName)) {
count++;
sheetName = `デイリーシフト${count}`;
}
// 新しいシートを指定の名前で作成
const sheet = ss.insertSheet(sheetName);
// 必要な列数を確保
if (sheet.getMaxColumns() < 35) {
sheet.insertColumnsAfter(sheet.getMaxColumns(), 35 - sheet.getMaxColumns());
}
// A1とA2に指定の値を入力
// セルの初期設定
// セルの初期設定
sheet.getRange('A1').setValue('デイリーシフト').setFontWeight("bold");
sheet.getRange('A2').setValue('DATE:');
sheet.getRange("A5:C20").setBackground("#FFF2CC");
sheet.getRange("E1").setValue("■使い方");
sheet.getRange("E2").setValue("①氏名、開始時間、終了時間を入力 ※終了時間が午前0時までの場合には24:00と入力")
// 4行目に見出しを設定
const headers = [
'氏名', '開始', '終了', '休憩', '勤務', '残業',
'0:00', '1:00', '2:00', '3:00', '4:00', '5:00', '6:00', '7:00', '8:00',
'9:00', '10:00', '11:00', '12:00', '13:00', '14:00', '15:00', '16:00',
'17:00', '18:00', '19:00', '20:00', '21:00', '22:00', '23:00'
];
sheet.getRange(4, 1, 1, headers.length).setValues([headers]);
// 4行目を左寄せに設定
sheet.getRange(4, 1, 1, headers.length).setHorizontalAlignment('left');
// B列からAD列までの時間形式を設定し、幅を40ピクセルに設定
for (let col = 2; col <= 30; col++) {
sheet.setColumnWidth(col, 40); // 列幅を50ピクセルに設定
}
sheet.getRange('B5:AD').setNumberFormat('h:mm');
// C3～C5に例を入力
sheet.getRange('A5').setValue('例');
sheet.getRange('B5').setValue('9:00');
sheet.getRange('C5').setValue('18:00');
// D5に関数を設定
sheet.getRange('D5').setFormula('=IF(B5="","", IF(C5-B5>TIME(8,,), TIME(1,,), IF(C5-B5>TIME(6,,), TIME(,45,), 0)))');
// E5に関数を設定
sheet.getRange('E5').setFormula('=IF(B5="","", C5-B5-D5)');
// F5に関数を設定
sheet.getRange('F5').setFormula('=IF(B5="","", IF(E5>TIME(8,,), E5-TIME(8,,), 0))');
// G5からAD5までのセルに個別に関数を設定
sheet.getRange('G5').setFormula('=IF($B5="","",AND($B5<=G$4,$C5>G$4))');
sheet.getRange('H5').setFormula('=IF($B5="","",AND($B5<=H$4,$C5>H$4))');
sheet.getRange('I5').setFormula('=IF($B5="","",AND($B5<=I$4,$C5>I$4))');
sheet.getRange('J5').setFormula('=IF($B5="","",AND($B5<=J$4,$C5>J$4))');
sheet.getRange('K5').setFormula('=IF($B5="","",AND($B5<=K$4,$C5>K$4))');
sheet.getRange('L5').setFormula('=IF($B5="","",AND($B5<=L$4,$C5>L$4))');
sheet.getRange('M5').setFormula('=IF($B5="","",AND($B5<=M$4,$C5>M$4))');
sheet.getRange('N5').setFormula('=IF($B5="","",AND($B5<=N$4,$C5>N$4))');
sheet.getRange('O5').setFormula('=IF($B5="","",AND($B5<=O$4,$C5>O$4))');
sheet.getRange('P5').setFormula('=IF($B5="","",AND($B5<=P$4,$C5>P$4))');
sheet.getRange('Q5').setFormula('=IF($B5="","",AND($B5<=Q$4,$C5>Q$4))');
sheet.getRange('R5').setFormula('=IF($B5="","",AND($B5<=R$4,$C5>R$4))');
sheet.getRange('S5').setFormula('=IF($B5="","",AND($B5<=S$4,$C5>S$4))');
sheet.getRange('T5').setFormula('=IF($B5="","",AND($B5<=T$4,$C5>T$4))');
sheet.getRange('U5').setFormula('=IF($B5="","",AND($B5<=U$4,$C5>U$4))');
sheet.getRange('V5').setFormula('=IF($B5="","",AND($B5<=V$4,$C5>V$4))');
sheet.getRange('W5').setFormula('=IF($B5="","",AND($B5<=W$4,$C5>W$4))');
sheet.getRange('X5').setFormula('=IF($B5="","",AND($B5<=X$4,$C5>X$4))');
sheet.getRange('Y5').setFormula('=IF($B5="","",AND($B5<=Y$4,$C5>Y$4))');
sheet.getRange('Z5').setFormula('=IF($B5="","",AND($B5<=Z$4,$C5>Z$4))');
sheet.getRange('AA5').setFormula('=IF($B5="","",AND($B5<=AA$4,$C5>AA$4))');
sheet.getRange('AB5').setFormula('=IF($B5="","",AND($B5<=AB$4,$C5>AB$4))');
sheet.getRange('AC5').setFormula('=IF($B5="","",AND($B5<=AC$4,$C5>AC$4))');
sheet.getRange('AD5').setFormula('=IF($B5="","",AND($B5<=AD$4,$C5>AD$4))');
// D6からAD20に関数をコピー
for (let row = 6; row <= 20; row++) {
sheet.getRange(`D${row}`).setFormula(`=IF(B${row}="","", IF(C${row}-B${row}>TIME(8,,), TIME(1,,), IF(C${row}-B${row}>TIME(6,,), TIME(,45,), 0)))`);
sheet.getRange(`E${row}`).setFormula(`=IF(B${row}="","", C${row}-B${row}-D${row})`);
sheet.getRange(`F${row}`).setFormula(`=IF(B${row}="","", IF(E${row}>TIME(8,,), E${row}-TIME(8,,), 0))`);
sheet.getRange(`G${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=G$4,$C${row}>G$4))`);
sheet.getRange(`H${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=H$4,$C${row}>H$4))`);
sheet.getRange(`I${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=I$4,$C${row}>I$4))`);
sheet.getRange(`J${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=J$4,$C${row}>J$4))`);
sheet.getRange(`K${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=K$4,$C${row}>K$4))`);
sheet.getRange(`L${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=L$4,$C${row}>L$4))`);
sheet.getRange(`M${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=M$4,$C${row}>M$4))`);
sheet.getRange(`N${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=N$4,$C${row}>N$4))`);
sheet.getRange(`O${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=O$4,$C${row}>O$4))`);
sheet.getRange(`P${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=P$4,$C${row}>P$4))`);
sheet.getRange(`Q${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=Q$4,$C${row}>Q$4))`);
sheet.getRange(`R${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=R$4,$C${row}>R$4))`);
sheet.getRange(`S${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=S$4,$C${row}>S$4))`);
sheet.getRange(`T${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=T$4,$C${row}>T$4))`);
sheet.getRange(`U${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=U$4,$C${row}>U$4))`);
sheet.getRange(`V${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=V$4,$C${row}>V$4))`);
sheet.getRange(`W${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=W$4,$C${row}>W$4))`);
sheet.getRange(`X${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=X$4,$C${row}>X$4))`);
sheet.getRange(`Y${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=Y$4,$C${row}>Y$4))`);
sheet.getRange(`Z${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=Z$4,$C${row}>Z$4))`);
sheet.getRange(`AA${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=AA$4,$C${row}>AA$4))`);
sheet.getRange(`AB${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=AB$4,$C${row}>AB$4))`);
sheet.getRange(`AC${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=AC$4,$C${row}>AC$4))`);
sheet.getRange(`AD${row}`).setFormula(`=IF($B${row}="","",AND($B${row}<=AD$4,$C${row}>AD$4))`);
}
// 条件付き書式をG5からAD20までに設定
const range = sheet.getRange('G5:AD20');
const trueRule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=G5=TRUE')
.setBackground('#FFF2CC') // 薄いオレンジ色
.setFontColor('#FFF2CC')
.setRanges([range])
.build();
const falseRule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=G5=FALSE')
.setBackground('#FFFFFF') // 白色
.setFontColor('#FFFFFF')
.setRanges([range])
.build();
sheet.setConditionalFormatRules([trueRule, falseRule]);
// G5からAD20までの文字サイズを7に設定
sheet.getRange('G5:AD20').setFontSize(7);
// 表全体の文字の高さをセルの真ん中に設定
sheet.getRange('A1:AD20').setVerticalAlignment('middle');
}
function ウィークリーシフト() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheetNameBase = "ウィークリーシフト";
let sheetName = sheetNameBase + "1";
let sheetNumber = 1;
// 重複しないシート名を決定
while (ss.getSheetByName(sheetName) !== null) {
sheetNumber++;
sheetName = sheetNameBase + sheetNumber;
}
// 新しいシートを作成
const sheet = ss.insertSheet(sheetName);
// セルの初期設定
sheet.getRange("A1").setValue("週間シフト表").setFontWeight("bold");
sheet.getRange("H1").setValue("■パターン設定 ※終了時間が翌日2時の場合は26:00");
sheet.getRange("B2").setValue("開始日");
sheet.getRange("C2").setValue("終了日");
sheet.getRange("C3").setFormula('=IF(B3="", "", B3 + 6)');
// H2～Q2にパターン情報を入力
const patternHeaders = ["パターン", "開始時間", "終了時間", "必要休憩", "勤務時間",
"パターン", "開始時間", "終了時間", "必要休憩", "勤務時間"];
sheet.getRange("H2:Q2").setValues([patternHeaders]);
// A3～E6に各種入力セルを設定
sheet.getRange("A3").setValue("Week");
sheet.getRange("E1").setValue("■使い方");
sheet.getRange("E2").setValue("①この色のセルを入力").setBackground("#FFF2CC"); // 明るい黄色
sheet.getRange("F2").setBackground("#FFF2CC"); // 明るい黄色
sheet.getRange("E3").setValue("②週の開始日を入力 (例: 11/4)");
sheet.getRange("E4").setValue("③パターン設定を入力");
sheet.getRange("E5").setValue("④氏名入力");
sheet.getRange("E6").setValue("⑤シフトをプルダウン選択");
// A8に「日付」、B8に関数「=B3」を設定
sheet.getRange("A8").setValue("日付");
sheet.getRange("B8").setFormula("=B3");
// C8～H8に日付の連続設定をする関数を入力
for (let col = 3; col <= 8; col++) {
let cell = sheet.getRange(8, col);
let prevCell = sheet.getRange(8, col - 1);
cell.setFormula(`=if(${prevCell.getA1Notation()}="","",${prevCell.getA1Notation()}+1)`);
}
// A9に「曜日」、B9に曜日表示の関数を入力
sheet.getRange("A9").setValue("氏名");
sheet.getRange("B9").setFormula('=IF(B8="","",TEXT(B8,"dddd"))');
// C9～H9に曜日の関数を入力
for (let col = 3; col <= 8; col++) {
let cell = sheet.getRange(9, col);
let prevCell = sheet.getRange(8, col);
cell.setFormula(`=IF(${prevCell.getA1Notation()}="","",TEXT(${prevCell.getA1Notation()},"dddd"))`);
}
// I9:Q9に指定されたヘッダーを追加
const headerLabels = ["勤務時間", "勤務日数", "公休", "振替休日", "有給休暇", "届出欠勤", "育児休暇", "出張", "その他"];
sheet.getRange("I9:Q9").setValues([headerLabels]);
// H3:H6とM3:M6に指定された値を設定
const patternValuesH = [["A"], ["B"], ["C"], ["D"]];
const patternValuesM = [["E"], ["F"], ["G"], ["H"]];
sheet.getRange("H3:H6").setValues(patternValuesH);
sheet.getRange("M3:M6").setValues(patternValuesM);
// J3～J6とO3:O6の表示形式をhh:mmに設定
sheet.getRange("J3:J6").setNumberFormat("hh:mm");
sheet.getRange("O3:O6").setNumberFormat("hh:mm");
// K3～L6、P3～Q6の関数を追加
for (let row = 3; row <= 6; row++) {
sheet.getRange(`K${row}`).setFormula(`=IF(I${row}="","", IF(J${row}-I${row}>TIME(8,,), TIME(1,,), IF(J${row}-I${row}>TIME(6,,), TIME(,45,), 0)))`).setNumberFormat("hh:mm");
sheet.getRange(`L${row}`).setFormula(`=IF(I${row}="","", J${row}-I${row}-K${row})`).setNumberFormat("hh:mm");
sheet.getRange(`P${row}`).setFormula(`=IF(N${row}="","", IF(O${row}-N${row}>TIME(8,,), TIME(1,,), IF(O${row}-N${row}>TIME(6,,), TIME(,45,), 0)))`).setNumberFormat("hh:mm");
sheet.getRange(`Q${row}`).setFormula(`=IF(N${row}="","", O${row}-N${row}-P${row})`).setNumberFormat("hh:mm");
}
// I10:I30に関数を設定して、表示形式を経過時間に設定
for (let row = 10; row <= 30; row++) {
sheet.getRange(row, 9).setFormula(`=IF(A${row}="","",SUM(
COUNTIF($B${row}:$H${row},"A")*$L$3,
COUNTIF($B${row}:$H${row},"B")*$L$4,
COUNTIF($B${row}:$H${row},"C")*$L$5,
COUNTIF($B${row}:$H${row},"D")*$L$6,
COUNTIF($B${row}:$H${row},"E")*$Q$3,
COUNTIF($B${row}:$H${row},"F")*$Q$4,
COUNTIF($B${row}:$H${row},"G")*$Q$5,
COUNTIF($B${row}:$H${row},"H")*$Q$6
))`);
sheet.getRange(row, 9).setNumberFormat("[hh]:mm");
}
// J10:J30に関数を設定し、表示形式を0日に設定
for (let row = 10; row <= 30; row++) {
sheet.getRange(row, 10).setFormula(`=IF(A${row}="","", 7-SUM($L${row}:$Q${row})-K${row})`);
sheet.getRange(row, 10).setNumberFormat("0日");
}
// K10:Q30に指定の関数を設定し、表示形式を0日に設定
for (let row = 10; row <= 30; row++) {
for (let col = 11; col <= 17; col++) {
let headerCell = sheet.getRange(9, col);
let cell = sheet.getRange(row, col);
cell.setFormula(`=IF($A${row}="","", COUNTIF($B${row}:$H${row}, ${headerCell.getA1Notation()}))`);
cell.setNumberFormat("0日");
}
}
// B10:H30にプルダウン設定を追加
const dropdownValues = [
"A", "B", "C", "D", "E", "F", "G", "H",
"公休", "振替休日", "有給休暇", "届出欠勤", "育児休暇", "出張", "その他"
];
const dropdownRange = sheet.getRange("B10:H30");
const rule = SpreadsheetApp.newDataValidation()
.requireValueInList(dropdownValues)
.build();
dropdownRange.setDataValidation(rule);
// 条件付きフォーマットを設定
const rules = [
SpreadsheetApp.newConditionalFormatRule()
.whenTextEqualTo("公休")
.setBackground("#FFE6E6") // 薄いピンク
.setRanges([dropdownRange])
.build(),
SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=OR(B10="A", B10="B", B10="C", B10="D", B10="E", B10="F", B10="G", B10="H")')
.setBackground("#E0F7FA") // 薄いスカイブルー
.setRanges([dropdownRange])
.build(),
SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=AND(B10<>"公休", NOT(OR(B10="A", B10="B", B10="C", B10="D", B10="E", B10="F", B10="G", B10="H")), B10<>"")')
.setBackground("#E6F9E6") // 薄いライトグリーン
.setRanges([dropdownRange])
.build(),
SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=ISBLANK(B10)')
.setBackground("#FFFFFF") // 白
.setRanges([dropdownRange])
.build()
];
sheet.setConditionalFormatRules(rules);
// I3:J6、N3:O6に明るい黄色を設定
sheet.getRange("B3").setBackground("#FFF2CC");
sheet.getRange("A10:A30").setBackground("#FFF2CC");
sheet.getRange("I3:J6").setBackground("#FFF2CC");
sheet.getRange("N3:O6").setBackground("#FFF2CC");
// A31～A40に「計」、「A」、「B」、「C」、「D」、「E」、「F」、「G」、「H」、「I」を入力
const valuesA = [["計"], ["A"], ["B"], ["C"], ["D"], ["E"], ["F"], ["G"], ["H"]];
sheet.getRange("A31:A39").setValues(valuesA);
// B31:H31に関数を設定し、0名と表示
for (let col = 2; col <= 8; col++) { // BからH列
const range = sheet.getRange(31, col);
range.setFormula(`=IF(SUM(${String.fromCharCode(64 + col)}32:${String.fromCharCode(64 + col)}40)=0, "", SUM(${String.fromCharCode(64 + col)}32:${String.fromCharCode(64 + col)}40))`); // 合計を計算し、0の場合は空文字を表示
range.setNumberFormat('0"名"'); // カスタム数値形式で表示
}
// B32:H40に関数を設定し、0名と表示
for (let row = 32; row <= 39; row++) {
for (let col = 2; col <= 8; col++) { // BからH列
const range = sheet.getRange(row, col);
range.setFormula(`=IF(COUNTIF(${String.fromCharCode(64 + col)}$10:${String.fromCharCode(64 + col)}$30,$A${row})=0,"",COUNTIF(${String.fromCharCode(64 + col)}$10:${String.fromCharCode(64 + col)}$30,$A${row}))`);
range.setNumberFormat('0"名"'); // カスタム数値形式で表示
}
}
// セルの中央揃えを設定
sheet.getRange("B8:Q39").setHorizontalAlignment("center").setVerticalAlignment("middle");
// B列からQ列のセル幅を75ピクセルに設定
for (let col = 2; col <= 17; col++) { // BからQ列
sheet.setColumnWidth(col, 75);
}
}
function マンスリーシフト() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
let sheetName = "マンスリーシフト";
let sheet;
let suffix = 1;
// シート名が重複しないように調整
while (ss.getSheetByName(sheetName + suffix)) {
suffix++;
}
sheet = ss.insertSheet(sheetName + suffix);
// 新しいシートを選択して表示
ss.setActiveSheet(sheet);
// 1. 列をAH列まで8列増やす
sheet.insertColumnsAfter(3, 8);
// 2. A1に「月間シフト」と太字
sheet.getRange("A1").setValue("マンスリーシフト").setFontWeight("bold");
// 3. B1に「開始日」
sheet.getRange("A3").setValue("月初日");
// 4. D1～D4に使い方の説明文を設定
const usageText = [
["■使い方"],
["①この色のセルを入力"],
["②B3に月初日を入力 例：11/1"],
["③氏名,契約出勤曜日(例：月火水木)、休み希望(○日、○日と複数入力可能)を入力"]
];
sheet.getRange("G1:G4").setValues(usageText);
// 5. A6～C6に「日付」「契約」「休み希望」を設定
sheet.getRange("A6:C6").setValues([["日付", "契約", "休み希望"]]);
// 6. A7～C7に「曜日」「出勤日」「例：1日、14日」を設定
sheet.getRange("A7:C7").setValues([["氏名", "出勤曜日", "例：1日、14日"]]);
// 8. D6に関数=IF(B3="","",B3)を入れる
sheet.getRange("D6").setFormula('=IF(B3="","",B3)').setNumberFormat("d日");
// 9. E6～AH6に日付を動的に変化させる関数を設定
sheet.getRange("E6:AH6").setFormula('=IF(D6="","",IF(MONTH(D$6+1)=MONTH($D6),D$6+1,""))').setNumberFormat("d日");
// 10. D7～AH7に曜日を動的に表示する関数を設定
sheet.getRange("D7:AH7").setFormula('=IF(D6="","",TEXT(D6,"ddd"))');
// 11. D8～AH37に出勤判定関数を設定
sheet.getRange("D8:AH37").setFormula('=IF(D$7="","",IF(AND(SUMPRODUCT(--ISNUMBER(SEARCH(D$7,$B8)))>0,SUMPRODUCT(--ISNUMBER(SEARCH(D$6,$C8)))=0),"出勤",""))');
// 12. C38に「出勤者数」を設定
sheet.getRange("C38").setValue("出勤者数");
// 出勤者数表示
sheet.getRange("D38:AH38").setFormula('=IF(D6="","",COUNTIF(D8:D37,"出勤"))');
sheet.getRange("D38:AH38").setNumberFormat("0名");
// 14. D列～AH列の幅を45に設定
for (let col = 4; col <= 34; col++) { // D列(4)からAH列(34)まで
sheet.setColumnWidth(col, 45);
}
// 15. 指定セルの背景色を明るい黄色3に設定
sheet.getRange("G2:I2").setBackground("#FFF2CC");
sheet.getRange("B3").setBackground("#FFF2CC");
sheet.getRange("A8:C37").setBackground("#FFF2CC");
// 16. シート全体のセルのテキストの縦位置を中央に設定
sheet.getRange("A1:AH38").setVerticalAlignment("middle");
// 17. B3を左寄せに設定
sheet.getRange("B3").setHorizontalAlignment("left");
}
この記事が気に入ったらサポートをしてみませんか？