Googleカレンダーの記録を活用してスプレッドシートに業務別の工数計算

photo credit: Office Desk via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

引き続きGoogleカレンダーの記録を活用して過去の仕事の生産性を測定する方法を進めていきます。

前回はこちらの記事です。

Google Apps Scriptで任意の月のカレンダーデータをスプレッドシートに書き出す方法

GASでGoogleカレンダーの記録をスプレッドシートに書き出して活用する方法についてお伝えしています。今回は、Google Apps Scriptで任意の月のカレンダーデータをスプレッドシートに書き出す方法です。

GASで任意の月のイベント情報をスプレッドシートに書き出す方法についてお伝えしました。

今回は、出力したイベント情報をカテゴリごとに集計をしていきます。

ということで、カレンダーの記録にスプレッドシートのQUERY関数を使って業務別に工数計算をする方法です。

では、行ってみましょう！

前回のおさらい
業務カテゴリごとの所要時間を算出する
QUERY関数の実行結果
1. 所要時間の集計結果の表示形式を整える
まとめ
連載目次：GASとカレンダーの記録を活用して過去の仕事を振り返る

前回のおさらい

前回のスクリプトがこちらです。

const CALENDAR_ID = '**********'; //カレンダーID
function getCalendarEvents() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, 1, sheet.getLastRow(), 6).clearContent();
 
  const startTime = new Date(sheet.getRange('I2').getValue());
  const endTime = new Date(startTime);
  endTime.setMonth(endTime.getMonth() + 1);
 
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);    
  const events = calendar.getEvents(startTime, endTime);
 
  const values = [];
  for(const event of events){
    const record = [
      event.getTitle(),
      event.getStartTime(),
      event.getEndTime(),
      event.getLocation(),
      event.getDescription(),
      "=INDIRECT("RC[-3]",FALSE)-INDIRECT("RC[-4]",FALSE)",
      "=IFERROR(SUBSTITUTE(LEFT(INDIRECT("RC[-6]",FALSE),FIND("】",INDIRECT("RC[-6]",FALSE))-1),"【",""),"")"
    ];
    values.push(record);
  }
 
  SpreadsheetApp.getActiveSheet().getRange(2, 1, values.length, values[0].length).setValues(values);
 
}

これにより、スプレッドシートに出力したデータがこちらです。

G列に、イベントタイトルに仕込んでおいた隅カッコ内の「業務カテゴリ」を抽出しています。

これをキーとして、F列の「所要時間」を加算集計すれば、その月の業務カテゴリ別の工数を算出できますね。

業務カテゴリごとの所要時間を算出する

では、J列に業務カテゴリごとの所要時間の総和を算出します。

スプレッドシートであれば、それはQUERY関数を使えば一発で出せます。

QUERY関数の使い方はこちらです。

QUERY(データ, クエリ, [見出し])

データは対象となるデータ範囲、クエリにどのような集計をするかを表す文字列、見出しは範囲内で見出し行とする行数を指定します。

今回、データ範囲は「A:G」、見出しは「1」ですね。

クエリはちょっと作らないといけません。

まず、結論からお伝えすると、J1セルに以下の数式を入力します。

=QUERY(A:G,"select G, sum(F) where G is not null group by G", 1)

以降、第二引数のクエリの部分についてお伝えしていきます。

group by句

まず、クエリの一番後ろ、「group by」のところから見ていきましょう。

group by G

ここはgroup by句といい、これを指定するとどの列をキーに集計をするか決めることができます。

今回は、G列なので「業務カテゴリ」単位で集計するということになります。

【QUERY関数】group by句とcount関数で列のデータ数をカウントする

GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第九回目は、group by句を使用して値を集計する方法の紹介です。group by句は集計関数と併せて使用します。count関数データの個数を数えます。

select句

次に、クエリの冒頭の「select」の部分です。

ここはselect句といい、範囲からどの列を抽出するかを決めます。

select G, sum(F) group by G

G列と、F列の合計を抽出するわけです。

F列所要時間の合計はG列単位、つまり業務カテゴリ単位でその合計を算出します。

QUERY関数の基本！別シートのデータからselect句で列を取得する方法

GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第二回目は、select句で特定の列を抽出する方法です。1列のみ・複数列・すべての列を抽出する書き方を紹介しています。

ここで、F列の所要時間ですが、その表示形式を「経過時間」にせずに「数値」のままにしている理由があります。

「sum(F)」の部分ですが、対象となる列は日付ではダメで、数値がある必要があるんです。

日付にしちゃうと、スプレッドシート関数の結果がエラーとなってしまうので注意してください。

where句

最後に、「where」の部分です。

select G, sum(F) where G is not null group by G

これは抽出するデータの条件を指定するもので、「G is not null」はG列つまり業務カテゴリが空欄でないものだけを集計対象とするというものです。

【QUERY関数】where句と比較演算子を使って単一条件に一致した行を抽出する

GoogleスプレッドシートのQUERY関数を紹介するシリーズ。第三回目は、where句と比較演算子を使用して、指定条件に一致する行を抽出する方法を紹介しています。

QUERY関数の実行結果

上記のQUERY関数を入力すると、以下のように集計がされます。

カテゴリ別のカテゴリ名と所要時間の合計がJ, K列に出力されました。

所要時間の集計結果の表示形式を整える

ただ、実行直後、K列の所要時間の集計結果は小数点以下が多数表示されてわかりづらいはずです。

そこで表示形式を変更していきます。

K列を選択した状態で「表示形式」→「数字」→「経過時間」を選択します。

これで、経過時間表記になるのですが、「秒」が邪魔なので消しちゃいましょう。

同じく、K列を選択した状態で「表示形式」→「数字」→「表示形式の詳細設定」→「その他の日付や時刻の形式」を選択します。

「カスタムの日付と時刻の形式」ダイアログが開きますので「:秒（01）」の部分を削除して、「適用」をクリックします。

コロン記号（:）も削除するのを忘れずにどうぞ。

これで、秒の表記が省略され、見やすくなります。

まとめ

以上、Googleカレンダーの記録を活用してスプレッドシートに業務別の工数計算をする方法をお伝えしました。

一定まではGASで行い、必要に応じてQUERY関数などで集計をする…うまく機能をすみわけて、スマートに目的を実現したいですよね。

ぜひ、ご活用ください！

連載目次：GASとカレンダーの記録を活用して過去の仕事を振り返る

企業ではスプレッドシートを勤怠管理やプロジェクト進捗管理などに使っている場合も多いと思います。このシリーズでは、カレンダーとスプレッドシート、そしてGoogle Apps Scriptを使って仕事の生産性を測定する方法についてお伝えしています。