見出し画像

GASでデイリーシフトのテンプレートを作ってみた

29119

デイリーシフトのフォーマットをGASで作る

1度GASでデイリーシフトのコードを作っておけば、意外と楽になります。本来であればサンプルのリンクを貼ればいいと思いますが、リンクが嫌いなのでコードにしました。
カスタムメニューからデイリーシフトを選択すれば出来ます。
ウィークリーとマンスリーは他の日に紹介しています。

GASとは

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

デイリーシフトのサンプルコード

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

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

}

GASの基本から

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

AppScript選択

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

初期に書かれているコードを消す

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

保存→実行の順番で押す

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

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

備考

ファイルのリンクを貼れば簡単ですけど、自分がそれを嫌いなので、コードにしています。

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