【勤怠管理】GAS からスプレッドシートを読み込んで書き込みする！

GoogleAppsScriptを使ったことのある人なら、その便利さにかなりの可能性を感じてしまったかもしれない。GASに関しては、いくつか記事を書いたこともあって例えばこんなのやったなあ。

使いようによっては結構便利なことできちゃうよね。まあ、プログラムを書いていくから最初書き上げるときはちょっと時間かかるし面倒だけどね。それでも後のことを考えると結構自動化できたりして楽。

そこで今回は、GASを使ってスプレッドシートへの書き込みを自動化してしまおうかなーっていうことをやっていく。最終的には出勤と退勤時間を書き込むスプレッドシートを作ってGASからそこに書き込みできるようにしようかな。

早速やっていこう。

GASからスプレッドシートを取得・書き込む方法

いきなりプログラムを書いていってもいいけど、あれこれどうやるんだろう？っていうのが出てくるから最初に手順を確認しておこう。後で面倒なことになるの嫌だしね、最初に考えられることは考えておきたい。まず、スプレッドシートに書き込むには以下が必要。

• GASから作成したスプレッドシートを読み込む
• 読み込んだスプレッドシートに書き込む

スプレッドシートを読み込む処理と、そこに書き込む処理がどんなものかなんとなく分かればできそうだよね。軽く見ていこう。

GASからスプレッドシートを読み込む

GASを使ってスプレッドシートを読み込む関数ははいくつか用意されているね。

• 現在開いているアクティブなシートを読み込む・・・getActiveSpreadSheet()
• idからスプレッドシートを読み込む・・・openById(id)
• urlからスプレッドシートを読み込む・・・openByUrl(url)

今回は今開いていないシートにも書き込む可能性は高いから、getActiveSpreadSheet()っていうのはなし。自分が作成したスプレッドシートのidかurlから読み込むかな。読み込みに関してはopenById(id)かopenByUrl(url)を使えばいけそう。

GASからスプレッドシートに書き込む

書き込むっていってもスプレッドシート はセルで構成されているから、どのセルに書き込むの？っていう書き込む場所を指定する処理と、そこに書き込む処理が必要かな。

• どのセルに書き込むのか指定・・・getRange()
• 書き込む処理・・・setValue()

ほんの少し調べただけで、使うものは実装中に変わるかもしれないけど、とりあえずこんな感じの関数を使えばできそう。スプレッドシートの読み込みと書き込みは実現可能そうだから、次は実際にスプレッドシート用意してGAS書いていくかな。

書き込むスプレッドシートを作成する

まずはGASで扱うスプレッドシートを作ろう。今回は勤怠管理っぽくしたいから出勤時間と退勤時間、休憩時間を入力したら合計の労働時間が出るっていうやつを作る。

労働時間は適当に=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",(SUM(C2-B2)-D2))みたいな式で入力した。とりあえずスプレッドシート はこれでいいだろう。ここの出勤時間と退勤時間に値が書き込まれると、休憩時間を引いた労働時間っていうのが表示される感じだ。GASでやりたいのはこの出勤時間と退勤時間を入力すること。

GASで出勤時間と退勤時間を入力する

GoogleAppsScriptに飛んで、早速新しいプロジェクトを作成しよう。

ここにコードを書いていくんだけど、とりあえずさっき作ったスプレッドシートを読み込んで書き込みができるかどうか確認したいから、ひとまずそれを確かめる。適当にこんな感じで書いて実行すれば、スプレッドシートのB3セルに10:00っていう値が書き込まれるはず。

const main = () => {
  const sheet = SpreadsheetApp.openById('作成したスプレッドシートのID');
  sheet.getRange('B3').setValue('10:00');
}

[作成したスプレッドシートのID]っていうのはURLで表示されるこの部分。

指定しているB3っていうのはこの部分。

実際に書き込まれるかどうか、GASを実行して確かめてみる。[実行]を押してみよう。

確認画面が出てきてそのままだと突破できないから、許可してやろう。

そうして確認してみると、10:00っていう値が指定したセルに書き込まれるね。やったじゃん。

GASでスプレッドシートにその日の出勤・退勤を書き込めるようにする

スプレッドシートを読み込んで書き込むっていうのはできるのが確認できた。これをちゃんと機能させるためには以下が必要だ。

• その日の出勤時間を適切な行に書き込む
• その日の退勤時間を適切な行に書き込む

例えば、1/03に実行したら2021/1/03(日)の行に値を書き込ませたいっていう感じだ。その日の出勤前や退勤後に起動すると自動で入るようにしたいからね。これをやるためには結構コードをゴリゴリ書いていかなそうでちょっと面倒だけど、まあやっていくか。。

実行時にやること

大体こんな感じだろうか。やること結構あるやん、作り終わった後は実行すればいいだけだから楽だけど、最初作るときは色々考えて実装しないといけなから結構時間かかるんだよなあ。。

GASで出勤・退勤を書き込むコードを書く

さあ、面倒だろうが書いていこうか。コードを書いて実行できるところまでやっていきたい。

GASのタイムゾーンを設定する

[ファイル→プロジェクトのプロパティ]と進んでタイムゾーンを東京にしておく。スプレッドシートのタイムゾーンとGASのタイムゾーンが合わなくて死ぬ。なんかGAS新しいデザインのエディタになったみたいだけど、そこからだと設定できないっぽかったから前のバージョンのエディタに戻してから設定した。

 

出勤・退勤のコードを書く

とりあえず書いた。適当な感じがするかもしれないけど、その辺りは色々いじって欲しい。。

const findTargetRow = (vals, today) => {
  const index = vals.findIndex((date) => {
    return date[date.length - 1].toLocaleString() === today.toLocaleString()
  })
  return index + 1
}

const isEmpty = (cell) => {
  return cell.getValue() === ''
}

const main = () => {
  const sheet = SpreadsheetApp.openById('自分のスプレッドシートのID');
  const dates = sheet.getRange('A:A').getValues()
  // 書き込む行を検索
  const row = findTargetRow(dates, new Date(new Date().setHours(0, 0, 0, 0)))
  // 出勤時間のセル取得
  const attendanceTimeCell = sheet.getRange(`B${row}`)
  // 退勤時間のセル取得
  const leaveTimeCell = sheet.getRange(`C${row}`)
 
  const today = new Date()
  const time = today.getHours() + ':' +  today.getMinutes()

  switch (true) {
    case isEmpty(attendanceTimeCell):
      attendanceTimeCell.setValue(time)
      break;
    case isEmpty(leaveTimeCell):
      leaveTimeCell.setValue(time)
      break;
    default:
  }
}

これを実行するとどんな動きになるかというとこんな感じだ。

• 出勤時間が入力されていなかった出勤時間を入力する
• 出勤時間が入力されていて、退勤時間が入力されていなかったら退勤時間を入力する

これなら作業を開始する前に実行すれば、出勤時間を入力。退勤時に実行すれば退勤時間を入力させることができるはず。実行すると出勤時間と退勤時間にその実行した時刻が入力される。

まとめ

今回やったのは、出勤時刻と退勤時刻をプログラムを実行するだけで入力してくれるというもの。まあ、プログラムを実行するのはGASの関数を実行しないといけないから、画面から今回は試した。

でも、使うとしたらパソコンからワンクリックだけとかiPhoneからワンタップだけで実行したいですよね。今回書いたプログラムをAPIのようにして使えば、そういったこともできるはずだから近いうちにやろうかな。