🌎

【GAS】スプレッドシートへの勤務表自動入力システムを作ってみた

に公開
Google Sheets
GAS
tech

概要

今の職場では、毎回スプレッドシートに勤務時間を入力しているんですが、この作業が地味にだるいので自動化してみました。（職場先フォーマット）

些細ではありますが、以下のような問題点があります。

  • 入力する都度スプレッドシートを開き、今日の日付を探すのが面倒
  • 出退勤時刻を入力するのも面倒
  • 休憩時間を入力するのも面倒
  • 今日やったことを入力するのも面倒

今回はこれらをGoogelAppsScript（以下GAS）で簡略化することにします。

スプレッドシートで勤務時間を入力している人であれば、コピペしてちょろっと変えれば誰でも使えます。

完成品

GASで作成したプログラムとHTMLをデプロイして、出勤時は出勤ボタンを押すことで現在時刻が記録され、退勤時には休憩時間と今日やったことを入力してボタンを押すと諸々記録されます。

本当はSlackでの出退勤報告をトリガーにしたかったんですが、権限をもらう必要があるのでひとまずすぐに出来ることでやってみました。

手順

  1. GoogleDriveにスプレッドシートを作成
  2. GASを新規作成
  3. GASからスプレッドシートに入力（飛ばしてもOK）
  4. プログラム構築
  5. 簡単なWebアプリ作成
  6. デプロイ

GoogelDriveにスプレッドシートを作成

GoogleDriveにログインして右クリックすると、スプレッドシートを作成できます。すでにある場合は新規作成する必要ありません。

GASを新規作成

スプレッドシートのツールタブを開いて、スクリプトエディタをクリックします。

すると以下の様な画面が開くと思います。

ここまで来ればあとはプログラムを書いていくだけです。

GASからスプレッドシートに入力（飛ばしてもOK）

がっつりプログラムを書いていく前に、ちゃんとスプレッドシートに入力できるか確認してみます。

コード.gs
function myFunction() {
  //openByIdの中身はそれぞれのスプレッドシートURLからコピペしてくる
  const sheet = SpreadsheetApp.openById('17HoBVD0wDPn79k-shpiQcLsvIR4SQ8ehWHK1a5jwqWM');
  //getRangeで入力範囲を指定して、setValueの値を入力する
  sheet.getRange('A1:A1').setValue('test')
}

スプレッドシートURLのIdが記載されている箇所

ショートカットキー（command⌘ + s）で保存すれば実行できるようになります。

初めての実行だと承認が必要になるので、自分のアカウントを選択して権限を与えましょう。

許可すればGASを用いてスプレッドシートの読み書きが可能になります。

権限を与えた後、実行するとA1セルに'test'が入力されました。

ここまでくればあとはプログラムを書いていくだけです。

プログラム構築

プログラム化の手順は以下の様な感じです。

  1. スプレッドシートの指定
  2. スプレッドシートのシート名の指定
  3. スプレッドシートの日付取得
  4. 現在の日付と合致する行を取得
  5. 入力する時刻の処理
  6. 出勤時刻の記録
  7. 休憩時間の（分→時）変換
  8. 退勤時刻の記録（退勤時は休憩時間と今日やったことも含む）

それぞれを関数化して呼び出して使います。最後にコード全文をのせています。

スプレッドシートの指定

これはさっきも出てきたので割愛します。

コード.gs
//スプレッドシート取得
const getSheet = () => {
  return SpreadsheetApp.openById('任意のId');
}

スプレッドシートのシート名の指定

今回のスプレッドシートはシート名が2021/8、2021/9といった命名規則があり、時間と共に増えていくので、getSheetByNameで現在の年月と同じシートに指定させます。

コード.gs
//スプレッドシート名取得
const getSheetName = (sheet) => {
  //スプレッドシートのシート名
  const year = new Date().getFullYear()
  //getMonthは0-11で返ってくるので+１する
  const month = new Date().getMonth() + 1
  const sheetName = sheet.getSheetByName(year + '/' + month );
  return sheetName
}

スプレッドシートの日付取得

今回はA7:A37（以下画像の赤枠）のセルに日付が入っているので、そこを取得します。

コード.gs
//スプレッドシートの日付取得
const dates = sheetName.getRange('A7:A37').getValues();

現在の日付と合致する行を取得

さきほど取得したスプレッドシート内の日付と現在の日付がどんぴしゃになるところを探します。return index + 7 の +7はスプレッドシート内の日付が７行目から始まっているので調整しています。

コード.gs
//書き込む行の検索
const findeTargetRow = (dates,today) => {
  const index = dates.findIndex((date) => {
    return date[date.length - 1].toLocaleString() === today.toLocaleString()
  })
  return index + 7
}

入力する時刻の処理

今回は10時2分だと'10:02'といった感じで勤務時間を入力したいんですが、実際は'10:2'で入力され、スプレッドシート側で時間判定してくれないので、１桁分の時はGAS側で'10:02'に整形します。

コード.gs
//現在時刻の処理
const timeProccess = (today) => {
  const minutes = today.getMinutes();
  if(String(minutes).length == 1) {
    //0〜9分の時の処理
    return today.getHours() + ':0' +  minutes
  } else {
    return today.getHours() + ':' +  minutes
  }
}

出勤時刻の記録

ここまで準備してきたので、あとはgetRangeで記録場所を指定して、setValueで先ほど処理した現在時刻をスプレッドシートに記録します。

コード.gs
// 出勤時間のセル取得
const attendanceTimeCell = sheetName.getRange(`C${row}`)

//記録
attendanceTimeCell.setValue(time)

休憩時間の（分→時）変換

Webアプリ側で入力するのは分のほうが簡単なので、GAS側で分→時に変換します。

コード.gs
const breakTimeProccess = (breakTime) => {
  if(String(breakTime).length == 0) {
    return '0:00'
  } else {
    const hour = Math.floor(breakTime / 60)
    const min = breakTime % 60
    return hour +':'+ min
  }
}

退勤時刻の記録（退勤時は休憩時間と今日やったことも含む）

breakTimeとtodayTaskは、のちほど作成するWebアプリ側からもらいます。

コード.gs
// 退勤時間のセル取得
const leaveTimeCell = sheetName.getRange(`D${row}`)
//記録
leaveTimeCell.setValue(time)

//休憩時間
const breakTimeMin = breakTimeProccess(breakTime)
sheetName.getRange(`E${row}`).setValue(breakTimeMin)
  
// きょうやったこと
sheetName.getRange(`G${row}`).setValue(todayTask)

コード全文

これをコピペして実行すれば動くと思います。（スプレッドシートのIdは変更してね）

コード.gs
//出勤ボタン
const attendanceRecord = () => {
  //スプレッドシートを指定
  const sheet = getSheet();

  //スプレッドシートのシート名
  const sheetName = getSheetName(sheet);

  //スプレッドシートの日付取得
  const dates = sheetName.getRange('A7:A37').getValues();

  //現在の日付と合致する行を取得
  const row = findeTargetRow(dates,new Date(new Date().setHours(0, 0, 0, 0)));

  const today = new Date();
  //時間の処理
  const time = timeProccess(today);

  // 出勤時間のセル取得
  const attendanceTimeCell = sheetName.getRange(`C${row}`)

  //記録
  attendanceTimeCell.setValue(time)
  
}

//退勤ボタン
const leaveRecord = (breakTime,todayTask) => {
  //スプレッドシートを指定
  const sheet = getSheet();

  //スプレッドシートのシート名
  const sheetName = getSheetName(sheet);

  //スプレッドシートの日付取得
  const dates = sheetName.getRange('A7:A37').getValues();

  //現在の日付と合致する行を取得
  const row = findeTargetRow(dates,new Date(new Date().setHours(0, 0, 0, 0)));

  //記録
  const today = new Date();
  //時間の処理
  const time = timeProccess(today);

  // 退勤時間のセル取得
  const leaveTimeCell = sheetName.getRange(`D${row}`)
  //記録
  leaveTimeCell.setValue(time)

  //休憩時間
  const breakTimeMin = breakTimeProccess(breakTime)
  sheetName.getRange(`E${row}`).setValue(breakTimeMin)
  
  // きょうやったこと
  sheetName.getRange(`G${row}`).setValue(todayTask)

}

//スプレッドシート取得
const getSheet = () => {
  return SpreadsheetApp.openById('1ERnb8ZmPwLRrMMLR23e1WI1MM0y6g6e3au8XaWhO3pg');
}

//スプレッドシート名取得
const getSheetName = (sheet) => {
  //スプレッドシートのシート名
  const year = new Date().getFullYear()
  //getMonthは0-11で返ってくるので+１する
  const month = new Date().getMonth() + 1
  const sheetName = sheet.getSheetByName(year + '/' + month );
  return sheetName
}

//書き込む行の検索
const findeTargetRow = (dates,today) => {
  const index = dates.findIndex((date) => {
    return date[date.length - 1].toLocaleString() === today.toLocaleString()
  })
  return index + 7
}

//現在時刻の処理
const timeProccess = (today) => {
  const minutes = today.getMinutes();
  //0〜9分の時の処理
  if(String(minutes).length == 1) {
    
    return today.getHours() + ':0' +  minutes
  } else {
    return today.getHours() + ':' +  minutes
  }
}

// 間違っていたので修正しました🙇
const breakTimeProccess = (breakTime) => {
  if(String(breakTime).length == 0) {
    return '0:00'
  } else {
    const hour = Math.floor(breakTime / 60)
    const min = breakTime % 60
    if(String(min).length == 1) {
      return hour +':0'+ min
    } else {
      return hour +':'+ min
    }
  }
}

さて、これで自動入力そのもののロジックは出来ました。ただし、いちいちGASを立ち上げて実行してたらほぼ無意味なので簡単なWebアプリを作っていきます。

簡単なWebアプリ作成

Webアプリといっても、GASだけで完結するのでご安心を。出来上がりはこんな感じ。

こんな感じの見た目になります。

それでは作っていきましょう。まずはindex.htmlファイルを作成します。


index.htmlの中身はこんな感じです。

index.html
<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script type="text/javascript">
    //HTML内の出勤ボタンが押されたら実行される
    function attendanceRecord() {
      //コード.gsのattendanceRecord()が実行される、成功したらinform()が実行されポップアップが出てくる
      google.script.run
        .withSuccessHandler(inform()).attendanceRecord();
    }
    function leaveRecord(){
      //Webアプリで入力されているtextを引数にして、コード.gsのleaveRecordにが実行される
      google.script.run
        .withSuccessHandler(inform())
        .leaveRecord(document.forms['formText'].elements['breakTime'].value,document.forms['formText'].elements['todayTask'].value);
    }
    //ポップアップが出てくる
    function inform() {
      alert('スプレッドシートに書き込みました');
    }

    </script>
  </head>
  <body>
    <button onclick="attendanceRecord();">出勤</button>
    <form name="formText">
        <p>休憩時間（単位は分）</p>
        <input type="text" name="breakTime">
        <p>今日やったこと</p>
        <input type="text" name="todayTask">
        <input type="button" onclick="leaveRecord()" value="退勤&休憩時間&今日やったこと">
      </form>
    <a href="https://docs.google.com/spreadsheets/d/任意のId/edit"target="_blank">スプレッドシート開く</a>
  </body>
</html>

あとはデプロイするだけ！！

デプロイ

右上のデプロイをクリックして新しいデプロイを選択。

右下のデプロイをクリック。そうすればデプロイ先のURLが表示されるのでブラウザで確認する。

完成！！

あとは使っていくだけです。

おわりに

これを作るのに6時間くらいは掛かったので、おそらく元は取れることはないけど自動化システム作るのおもしろいなと思いました。
次はSlackトリガーのやつをつくってみようかな。

参考にした記事

https://gadgelaun.com/?p=20669

GitHubで編集を提案

Discussion