2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

【はじめてのGAS】Googleカレンダーでつけた稼働時間イベントをスプレッドシートに出力したよ

Posted at

はじめに

フリーランスになってから、お仕事の稼働時間をGoogleカレンダー上で管理しています

スクリーンショット 2020-02-08 11.56.36.png

Googleカレンダーは通知もくるし、カレンダーのメモに実施内容を書いたりするなど、タスク管理としてもけっこう好きなツールです😼
有料ならい〜感じの勤怠管理システムがあるのでしょうが、直近では節約も兼ねてこの運用でと思っています
(🙇‍♂️< フリーランスの先輩方、駆け出しフリーランスにも優しいすてきなツールがあれば教えてください🙏)

そんなわけですが先日お仕事先への稼働報告の際に、
😹「作業の開始時刻が中途半端だったりちまちま数えるのがつらい・・・」
となったので、稼働時間をスプレッドシートに出力するGASを書いてみました :tada:

Googleカレンダー側の準備

まずは https://calendar.google.com/

カレンダーの作成とIDの確認

イベントを出力したいカレンダー > 設定を共有 を開きます

スクリーンショット 2020-02-08 23.54.07.png

設定画面の下の方に カレンダー ID があるのでそちらをコピーしておきましょう

スクリーンショット 2020-02-08 23.59.12.png

イベントのネーミングルールを決める

わたしは、休憩時間もイベントとして作成したかったので稼働と休憩と分けるためルールを決めました
こちらはGASでの文字列判定に利用します

🙇‍♂️< イベントの識別はほんとうはもっといい方法があるかもしれません!
🙇‍♂️< ご存知の方いらっしゃったらコメントいただけると😹🙏

稼働時間 = "Working hours👩‍💻"
休憩時間 = "Rest🍹🌴"
スクリーンショット 2020-02-08 23.42.44.png

スプレッドシート & .gsファイル作成

  1. イベントを出力するスプレッドシートを作成します
  2. スプレッドシート画面の ツール > スクリプトエディタ.gsファイルを作成します
スクリーンショット 2020-02-08 12.16.51.png

Google Apps Script を書く

今回のコードはとりあえずスプレッドシートに出力する目的です
コードのうつくしさやパフォーマンスは度外視とさせていただきます🙏

まずは完成形

スクリーンショット 2020-02-09 1.19.32.png
  • その日にどのくらい稼働と休憩しているか
  • 合計の稼働と休憩時間はどのくらいか

を出力できるようにしました

🙇‍♂️< 日付を跨いだ稼働の場合などは今回考慮しておりません

これをこうしてこうじゃ(実際のスクリプト)

var calendar = CalendarApp.getCalendarById(...);のところでさっき確認したカレンダー IDをセットしてください


function calendar() {

  // 各カレンダーイベントを格納するためのEventオブジェクト
  var Event = function(event) {
    var startAt = event.getStartTime();
    var endAt = event.getEndTime();
    var duration = (endAt - startAt) / (1000 * 60);
    // イベントタイトル
    this.title = event.getTitle();
    // イベントの開始日時
    this.startAt = startAt;
    // イベントの開始月
    this.month = startAt.getMonth() + 1;
    // イベントの開始日
    this.date = startAt.getDate() + 1;
    // イベントの時間(分)
    this.minutes = duration;
    // イベントの時間(時間)
    this.hours = Math.floor(duration / 60 * 100) / 100;
    // これが稼働時間のイベントかどうかのフラグ
    this.isWork = (event.getTitle().match(/Working/) != null);
  };

  // スプレッドシートの取得 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  // 一度まっさらな状態にする
  sheet.clear();
  // カレンダーを取得 

  // 👇ここでさっき確認したカレンダー IDをセットする👇
  var calendar = CalendarApp.getCalendarById('hogehoge@group.calendar.google.com');
  // 🙌ここでさっき確認したカレンダー IDをセットする 🙌

  // 今回はとりあえず当月分をごりごり指定
  var date = new Date("2020/2/1");
  var startDay = new Date(date.getFullYear(), date.getMonth(), 1);
  var endDay = new Date(date.getFullYear(), date.getMonth() + 1, 0);
  
  // カレンダーから指定した範囲のイベントを取得
  var events = calendar.getEvents(startDay, endDay);
  var workingEvents = [];

  for(var i = 0; i < events.length; i++) {
    var event = events[i];
    var workingEvent = new Event(event);

    // 同じ名前、同じ月・日にちのものを足し上げ
    var hasSameDate = false;
    for(var j = 0; j < workingEvents.length; j++) {
      var stored = workingEvents[j];
  
      hasSameDate = stored.title == workingEvent.title 
        && stored.month == workingEvent.month
        && stored.date == workingEvent.date;

      if (hasSameDate) {
        stored.minutes = stored.minutes + workingEvent.minutes;
        stored.hours = stored.hours + workingEvent.hours;
        workingEvents[j] = stored;
        break;
      }
    }
    
    if (!hasSameDate) {
      workingEvents.push(workingEvent);
    }    
  }

  // タイトルとかをスプレッドシートに書き込む
  sheet.getRange('A'+(1)).setValue('Event name');
  sheet.getRange('B'+(1)).setValue('Work date');
  sheet.getRange('C'+(1)).setValue('(Minutes)');
  sheet.getRange('D'+(1)).setValue('(Hours)');
   
  // 各イベントをスプレッドシートに書き込む
  for (var i = 0; i < workingEvents.length; i++) {

    var title = workingEvents[i].title;
    var startAt = workingEvents[i].startAt;
    var minutes = workingEvents[i].minutes;
    var hours = workingEvents[i].hours;

    sheet.getRange('A'+ (i + 2)).setValue(title);
    sheet.getRange('B'+ (i + 2)).setValue(startAt);
    sheet.getRange('C'+ (i + 2)).setValue(minutes.toString(10));
    sheet.getRange('D'+ (i + 2)).setValue(hours.toString(10));
  }
  
  // 合計時間の計算
  var sumMinutes = 0;
  var sumHours = 0;
  var workingMinutes = 0;
  var workingHours = 0;
  for (var i = 0; i < workingEvents.length; i++) {
    sumMinutes = sumMinutes + workingEvents[i].minutes;    
    sumHours = sumHours + workingEvents[i].hours;

    if (workingEvents[i].isWork) {
      workingMinutes = workingMinutes + workingEvents[i].minutes;
      workingHours = workingHours + workingEvents[i].hours;
    }
  }

  sheet.getRange('A'+(workingEvents.length + 3)).setValue('Sum: Work');
  sheet.getRange('C'+(workingEvents.length + 3)).setValue(workingMinutes.toString(10));
  sheet.getRange('D'+(workingEvents.length + 3)).setValue(workingHours.toString(10));

  sheet.getRange('A'+(workingEvents.length + 4)).setValue('Sum: Rest + Work');
  sheet.getRange('C'+(workingEvents.length + 4)).setValue(sumMinutes.toString(10));
  sheet.getRange('D'+(workingEvents.length + 4)).setValue(sumHours.toString(10));  
};

.gaファイルを実行▶️すると・・・

(再掲)
スクリーンショット 2020-02-09 1.19.32.png

できました!
でもちょっとプレーンすぎて見にくいし、テンション上がらないので・・・

ちょっとユメカワ🦄🌈にする(おまけ)

テンションを上げるためにせっかくなので色やフォントの調整をします

前述のコードのfunction calendar()の最後にスプレッドシートのビジュアルについてのスクリプトを入れます

function calendar() {
   
  // (省略)

  // Color & Font
  var dataRange = sheet.getDataRange();
  dataRange.setBackground('#B19CD9');
  dataRange.setFontColor('#FFFFFF');

  var headerRange = sheet.getRange('A1:D1');
  headerRange.setBackground('#89CFF0');
  headerRange.setFontWeight("bold");

  for (var i = 0; i < workingEvents.length; i++) {    
    if (workingEvents[i].isWork) {
      sheet.getRange('A'+ (i + 2) + ':D'+ (i + 2)).setBackground('#FEC8D8');
    }
  }
};

.gaファイルを実行▶️すると・・・

💙💚💛🧡💜ほらかわいい💙💚💛🧡💜(めっちゃ見にくい)

スクリーンショット 2020-02-09 1.28.53.png

以上、完成です!

さいごに

GAS、はじめての上、JavaScriptもあまり書かないのでモダンだったりパフォーマンスが良いプログラムは組めていないと思います
今後使いつづける間にチューニングとかもできればな〜と思いつつ・・・もし稼働時間管理の(お財布に優しくて)良いツールがあれば教えてください🙏

以上です :tada:
ありがとうございました

Thanks to

https://qiita.com/chihiro/items/09c996d41d80f0d30e17
https://www.atmarkit.co.jp/ait/articles/1702/27/news023.html
https://qiita.com/2355/items/97fe0d6b7cdeac5855f1

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?