【GAS】スプレッドシートに勤怠表テンプレートを自動生成する方法

今回は、Googleスプレッドシートに勤怠表テンプレートを自動生成する方法を紹介します。

作成するのは以下のようなシートです。コピペしてもさほど大変ではありませんが、カレンダー部分を毎回修正するのは面倒なのでテンプレート化して自動生成できるようにします。

仕様

主な仕様は以下のとおりです。

出力内容

指定した年の1〜12月のシートを出力します。出力する項目は以下のとおりです。

• 総支給額
• 時給
• 日付
• 出勤
• 退勤
• 休憩
• 稼働時間
• 1日の給料

注意事項

既に同じ名前でシートが作成されている場合、シートの作成は行わず、ログにその旨を出力します。

ソースコード

以下がテンプレート作成用のテンプレートです。

スプレッドシートIDの設定

以下のソースコードを動かす際に、対象となるスプレッドシートのIDを設定する必要があります。

スプレッドシートのURLからスプレッドシートIDをコピーし、以下にペーストします。

const SHEET_ID = 'ご自身のスプレッドシートIDを記入';

ソースコード

// GoogleスプレッドシートのID
const SHEET_ID = 'ご自身のスプレッドシートIDを記入';

function myFunction() {

  // 1年分作成したい場合
  for (let i = 11; i >= 0; i--) {
    let date = new Date(2023, i); // 作成したい年に設定
    createKintai(date);
  }
}

function createKintai(targetDate) {

  // 対象のスプレッドシートを取得
  let spreadSheet = SpreadsheetApp.openById(SHEET_ID);

  // 今月の月初日を取得
  targetDate.setDate(1);

  // 今月の月末日を取得
  targetDate.setMonth(targetDate.getMonth() + 1, 0);
  let day = targetDate.getDate();

  // 対象の年月をyyyy/MM形式で取得
  let dayStr = Utilities.formatDate(targetDate, 'JST', 'yyyy/MM')

  // シートが存在するか確認
  let sheet = spreadSheet.getSheetByName(dayStr);

  // 対象のシートが存在しないときのみ作成
  if (sheet) {
    console.log('既にシートが存在するため、シートは生成されませんでした：' + dayStr);
  }
  else {

    let newSheet = spreadSheet.insertSheet();
    newSheet.setName(dayStr);
    let targetSheet = spreadSheet.getSheetByName(dayStr);

    // ヘッダ1行目
    mergeCell = targetSheet.getRange(2, 2, 1, 2).merge();
    mergeCell.setHorizontalAlignment('center').setFontWeight('bold').setFontSize(24).setBackground('#87CEFA');
    mergeCell.setValue(dayStr);
    targetSheet.getRange('D2').setHorizontalAlignment('center').setBackground('#FFFACD').setValue('総支給額');
    targetSheet.getRange('F2').setHorizontalAlignment('center').setBackground('#FFFACD').setValue('時給');
    // セル色
    targetSheet.getRange('B2').setBackground('#87CEFA');

    // ヘッダ2行目
    targetSheet.getRange('B4').setBackground('#FFFACD').setValue('日付');
    targetSheet.getRange('C4').setBackground('#FFFACD').setValue('出勤');
    targetSheet.getRange('D4').setBackground('#FFFACD').setValue('退勤');
    targetSheet.getRange('E4').setBackground('#FFFACD').setValue('休憩');
    targetSheet.getRange('F4').setBackground('#FFFACD').setValue('稼働時間');
    targetSheet.getRange('G4').setBackground('#FFFACD').setValue('一日の給料');

    // カレンダー作成
    for (let i = 1; i <= day; i++) {
      let lastRow = targetSheet.getLastRow();
      let targetCell = targetSheet.getRange(lastRow + 1, 2);
      targetCell.setValue(i);
    }

    // 罫線・フォント等の設定
    for (let i = 1; i <= day + 3; i++) {
      let targetCell = targetSheet.getRange(i + 1, 2, 1, 6);
      targetCell.setFontWeight('bold').setBorder(true, true, true, true, true, true);
    }
  }
}

実行方法

GASのエディタ上部にある「実行」を押すとスクリプトが実行されます。このとき、実行ボタンの右横にある関数名が”myfunction”になっていることを確認してください。

作成するシート数が多いとけっこう時間がかかります。実行完了すると以下のようにログが出力されます。

作成しようとしたシートと同名のシートが既に存在する場合は、その旨も出力されます。

実行が完了したら、スプレッドシートを開き正しく作成されていることを確認してください。

特定の月だけ作成したい場合

「2023/08だけ作成したい」という場合は、myFunctionのループの部分を以下に変更して実行してください。

このとき、セットする月は、実際の月マイナス1になります。

function myFunction() {

    // 作成したい月-1をセット(2023/08なら7)
    let date = new Date(2023, 7); 
    createKintai(date);

}

月初に自動で作成したい場合

月初に勤務表のテンプレートを自動で作成するようにしたい場合、GASの「トリガー」機能を使います。トリガーを設定すると、指定した日にちにスクリプトが実行されるようになります。

詳しくは以下記事をご参照ください。

特定の年を作成したい場合

「2024年の1月〜12月分を作成したい」という場合は、for文の中の内容を変更してください。

function myFunction() {

  // 1年分作成したい場合
  for (let i = 11; i >= 0; i--) {
    let date = new Date(2024, i); // 作成したい年に設定
    createKintai(date);
  }
}

まとめ

今回は、Googleスプレッドシートに勤怠表テンプレートを自動生成する方法について解説しました。

せっかく勤務表のテンプレを作るなら、1日の給料や稼働時間が自動で計算されるようにしたほうが便利だと思うので、数式も含めたテンプレができたら反映したいと思います。

勤怠管理にスプレッドシートを使っている方も増えていると思うので、少しでも参考になれば幸いです。もし、何か不明点や不具合と思われる箇所がありましたら、コメントにてご指摘いただければと思います。