見出し画像

GASで週間勤務シフトのテンプレートを作成

29119

日別シフトをGASで作る

週間の勤務シフトをGASで作るコードです。
1度組み込んでおくと意外と使えます。
薄い黄色の部分だけ入力すれば自動計算設定となっています。
休憩時間は労働基準法を遵守して計算しています。

必要事項を入れたものが下の画像です。
印刷もA4に収まります。

GASとは

GASは簡単に言えばグーグルが提供しているプログラミング言語です。無料で利用できます。

シフト表のサンプルコード

GASをある程度知っている方…下のコードをコピペすれば完了です。
GASをあまりよく分からない方…コードの下に説明を詳しく入れています。ご参照ください。

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('カスタムメニュー') // メニューのタイトルを指定
    .addItem('週間シフト', '週間シフト')
    .addToUi(); // メニューをUIに追加
}

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);
  }
}

GASの基本から

Dドライブにアクセスして、スプレッドシートを開きます。
拡張機能からAppScriptを選択します。

下のような画面が出てきたら、function myfunctionと書かれている部分をすべて消します。

次に上記のコードをすべて選択して貼り付けます。
保存を押し、実行を押します。

以下、赤印で囲んだところを選択

実行ログに実行完了と表示されたら、スプレッドシートを見て下さい。
カスタムメニューという項目が増えています。このファイルを開けば、次回以降も表示されます。他のファイルには影響はありません。
週間シフトを選択すれば出来上がりです。


この記事が気に入ったらサポートをしてみませんか？