rcmdnk's blog
Last update

20210308_graph_200_200

身の回り記録の1つとして、IFTTTを使って自分が特定の場所にどれくらい居たか、 を記録するようにしてみました。

IFTTTを使った位置情報記録

IFTTTではIfLocationというサービスで携帯電話の位置情報を使って特定の領域に入ったか出たタイミングで記録を取ることが出来ます。

  • You enter or exit an area
  • You exit an area
  • You enter an area

といった具合で特定の領域に入ったか出たか、またはその両方で記録が取れます。

領域として指定できるのは最小で半径100m程です。

ThenでGoogle Sheetsを選ぶことでその情報をGoogle Sheetsに保存できます。

記録できる値としては、

  • OccurredAt (時間)
  • LocationMapImageUrl (該当地域のイメージのURL)
  • LocationMapUrl (該当地域の地図のURL)
  • EnteredOrExited (入ったか、出たかでenteredexitedと記録される。)

MapImageとMapURLとかに関しては1つのルールで使っても意味ないですが(毎回同じなので)、 複数のルールで同じSheetsに書き込む様にすると意味があるものが作れるかもしれません。

今回は時間と入ったか出たか、を記録したいと思いますが、 例えばGoogle Spreadsheetsに記録するには

{{OccurredAt}} ||| {{EnteredOrExited}}

みたいにすればA列に日時、B列にentered/exitedが書き込まれます。

ただ、ちょっとこのOccurredAtの書式が微妙です。

March 1, 2021 at 08:51AM

な感じの入力で変更が出来ないので色々処理しようとしても Spreadsheetsで日時として認識される形ではないので辛い。

Google Spreadsheetsで直接分解して滞在時間を出す

enteredexitedがわかればその間の時間を計算すれば滞在時間がわかるわけですが、 Google Spreadsheetsで時間を計算しようと思うと 上のOccurredAtのままだとうまく出来ません。

色々文字列を処理すればできそうですが、 IFTTTに関数とかをすべて書いて行おうとすると以下の様な感じでできます。

{{OccurredAt}} |||
{{EnteredOrExited}} |||
=date(index(H:H, row(), 1), if(index(F:F, row(), 1)="January", 1, if(index(F:F, row(), 1)="February", 2, if(index(F:F, row(), 1)="March", 3, if(index(F:F, row(), 1)="April", 4, if(index(F:F, row(), 1)="May", 5, if(index(F:F, row(), 1)="June", 6, if(index(F:F, row(), 1)="July", 7, if(index(F:F, row(), 1)="August", 8, if(index(F:F, row(), 1)="September", 9, if(index(F:F, row(), 1)="October", 10, if(index(F:F, row(), 1)="November", 11, 12))))))))))), substitute(index(G:G, row(), 1), ",", "")) |||
=timevalue(index(J:J, row(), 1)) |||
=if(AND(index(B:B, row(), 1)="entered", index(B:B, row()+1, 1)="exited"), 24*(index(C:C, row()+1, 1)-index(C:C, row(), 1) + index(D:D, row()+1, 1)-index(D:D, row(), 1)), "") |||
=split(index(A:A, row(), 1), " ")

これを使うと以下のような表が出来ます。

A B C D E F G H I J K
March 1, 2021 at 08:51AM entered 2021/03/01 0.36875 3.166666667 March 1, 2021 at 8:51  
March 1, 2021 at 12:01PM exited 2021/03/01 0.5006944444   March 1, 2021 at 12:01  

やってることは、まず、G列にsplit関数を使ってA列に書き込んだ日時の文字列を分解する命令を 埋め込んでG~Kに入れます。

C列ではG列の月の名前を数字に直して、H列の日付(,が着いてるのでそれを削除)とH列の 年を合わせて日付を出します。

D列ではK列に出している時刻を1日を1とした時間に直したときの値にtimevalue関数を使って変更しています。K列ではもともと06:58PMの様にAMPMが付いてる形のものが自動的に24時間表記になっていますが、これはデフォルトでこの形は時刻として認識されるので表記がそうなっていますし、値としても時刻として認識された形で入っています。

E列最終的に欲しい値で enteredの列の場合にC列の次のexitedの行との差(日付の差なので1日差があると1になる)と timevalueの差を足して、それに24をかけることで時間単位にしています。

Google Apps Scriptを使う

上の形だとごちゃごちゃするに無駄な入力が増えるので、 Google SpreadsheetsでApps Scriptと連携して新たな関数を作って同じ様なことをすることが考えられます。

IFTTT側で以下の様なものを用意。

{{OccurredAt}} ||| {{EnteredOrExited}} ||| =getTimeDiff(row())
getTimeDiff.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
const ORIG_SHEET = 'シート1';
const TIMEZONE_OFFSET = '0900';
const HOUR_MS = 1000 * 60 * 60;


/**
 * Fill timevalue difference of `A{row}` - `A{row-1}`, if `B{row}` is 'exited', else empty
 * @param {'4'} row use `row()` to use current row
 * @return timevalue difference or '' if is_exited is 'entered'
 * @customfunction
 */
function getTimeDiff(row) {
  try{
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(ORIG_SHEET);
    const values = sheet.getRange('A' + String(row) + ':B' + String(row+1)).getValues();
    if (values[0][1] != 'entered') return '';
    return (getDateTime(values[1][0])-getDateTime(values[0][0]))/HOUR_MS;
  }catch(e){
    return '';
  }
}

function getDateTime(datetime){
  let ta = String(datetime).split(' ');
  ta.splice(3, 1);
  let time = ta[3].split(':');
  if(time[0] == '12')time[0] = '0';
  if(time[1].endsWith('PM'))time[0] = String(Number(time[0]) + 12);
  ta[3] = time[0] + ':' + time[1].replace('AM', '').replace('PM', '');
  ta.push('GMT+' + TIMEZONE_OFFSET);
  return new Date(ta.join(' '));
}

これで、

A B C
March 1, 2021 at 08:51AM entered 3.166666667
March 1, 2021 at 12:01PM exited  

みたいな入力になります。

日毎に分ける

上の方法だと入った日に関してそこからどれだけ滞在したか、が見れます。

ただ、一日に何度も入ったり出たりするかもしれないし、 家の滞在時間とかを考えると日をまたいだりします。

場合によっては24時間以上になることも。

この辺を可視化しようとする場合に、ある特定の日に何時間居たか、をまとめてくれてたほうが嬉しかったりします。

そこで以下の様なスクリプトで別のシートに日毎の滞在時間を計算し直して書き込むようにしてみます。

makeEachDay.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
const ORIG_SHEET = 'シート1';
const NEW_SHEET = 'StayingTime';
const TIMEZONE = 'Asia/Tokyo';
const TIMEZONE_OFFSET = '0900';
const HOUR_MS = 1000 * 60 * 60;

function getDateTime(datetime){
  let ta = String(datetime).split(' ');
  ta.splice(3, 1);
  let time = ta[3].split(':');
  if(time[0] == '12')time[0] = '0';
  if(time[1].endsWith('PM'))time[0] = String(Number(time[0]) + 12);
  ta[3] = time[0] + ':' + time[1].replace('AM', '').replace('PM', '');
  ta.push('GMT+' + TIMEZONE_OFFSET);
  return new Date(ta.join(' '));
}

function getDayStart(datetime){
  let dt = new Date(datetime.getTime());
  dt.setHours(0, 0, 0, 0);
  return dt;
}

function makeEachDay(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setSpreadsheetTimeZone(TIMEZONE);
  const s_orig = ss.getSheetByName(ORIG_SHEET);

  const datetimes = s_orig.getRange('A:A').getValues();
  const inout = s_orig.getRange('B:B').getValues();
  let dt = null;
  let time = 0;
  let values = [];
  let i = -1;
  while(i < datetimes.length){
    i++;
    if(datetimes[i] == '')break;
    if(inout[i] != 'entered')continue;
    let dt_entered = getDateTime(datetimes[i]);

    i++;
    if(i == datetimes.length || datetimes[i] == '')break;
    let dt_exited = getDateTime(datetimes[i]);
    let dt_entered_start = getDayStart(dt_entered);
    let dt_exited_start = getDayStart(dt_exited);

    if(dt === null)dt = new Date(dt_entered_start.getTime());
    while(true){
      if(dt < dt_entered_start){
        values.push([new Date(dt.getTime()), time / HOUR_MS]);
        time = 0;
        dt.setDate(dt.getDate() + 1);
        continue;
      }
      if(dt.getTime() == dt_entered_start.getTime() && dt_entered_start.getTime() == dt_exited_start.getTime()){
        time += (dt_exited - dt_entered);
        break;
      }
      if(dt.getTime() == dt_entered_start.getTime()){
        let dt_next = new Date(dt.getTime());
        dt_next.setDate(dt_next.getDate()+1);
        time += dt_next - dt_entered;
        values.push([new Date(dt.getTime()), time / HOUR_MS]);
        time = 0;
        dt.setDate(dt.getDate() + 1);
        continue;
      }
      if(dt < dt_exited_start){
        values.push([new Date(dt.getTime()), 24]);
        time = 0;
        dt.setDate(dt.getDate() + 1);
        continue;
      }
      time = dt_exited - dt;
      break;
    }
  }
  if(time != 0){
    values.push([new Date(dt.getTime()), time / HOUR_MS]);
  }
  if(values.length == 0)return;

  let s_new = ss.getSheetByName(NEW_SHEET);
  if (!s_new) {
    s_new = ss.insertSheet(NEW_SHEET);
  }

  const range = s_new.getRange("A:B");
  range.deleteCells(SpreadsheetApp.Dimension.COLUMNS);
  s_new.getRange(1, 1, 1, 2).setValues([['StartDateTime', 'Hours']]);
  s_new.getRange('A:A').setNumberFormat('yyyy/MM/dd');
  s_new.setFrozenRows(1);

  const numRows = values.length;
  const numColumns = values[0].length;
  s_new.getRange(2, 1, numRows, numColumns).setValues(values);
}

上のスクリプト内でシート側のタイムゾーンをAsia/Tokyoに設定していますが、 スクリプト側のタイムゾーンが違うと getDayStart(datetime)とかで0時にセットする際にそのタイムゾーンの時間での0時にせっとされたりしてしまいます。

そういったことを避けるためにスクリプトのタイムゾーンを設定。 設定方法は、旧エディタであればファイルプロジェクトのプロパティからタイムゾーンを東京とかに変更できます。

新エディタだと

  • 左にある歯車マークからプロジェクトの設定を呼び出して
  • 「appsscript.json」マニフェストファイルをエディタで表示する、にチェック
  • エディタに戻るとappsscript.jsonがあるので、そこのtimeZoneの値を

      "timeZone": "Asia/Tokyo",
    

    に変える

という作業で出来ます。

これでmakeEachDay関数をエディタから実行すると

A B
March 1, 2021 at 08:51AM entered
March 1, 2021 at 12:01PM exited
March 1, 2021 at 01:05PM entered
March 1, 2021 at 06:58PM exited

みたいにお昼に外に出て滞在が2つに分かれてしまっている場合にも

A B
StartDateTime Hours
2021/03/01 9.05

みたいな感じで3/1分でまとめてくれます。

A B
March 2, 2021 at 08:31AM entered
March 3, 2021 at 01:08AM exited
March 3, 2021 at 09:28AM entered
March 3, 2021 at 07:33PM exited

の3/2みたいに次の日に渡ってしまった場合でも

A B
StartDateTime Hours
2021/03/02 15.48333333
2021/03/03 11.21666667

な感じで、3/2分は8:31から24:00まで、24:00から1:08分は次の日の分として 3/3に足されています。

A B
March 10, 2021 at 08:18AM entered
March 12, 2021 at 08:41PM exited

みたいに丸一日越した場合でも、

A B
StartDateTime Hours
2021/03/10 15.7
2021/03/11 24
2021/03/12 20.68333333

な感じで中の日は1日分にしてそれぞれの日で表示してくれます。

自動で実行

ここまで出来たら後はIFTTTで出入りするたびに記録させ、適当なタイミングで makeEachDayを実行させればOK。

Apps Scriptの設定でトリガーで適当に夜に走る様にしておけば良いかと。

20210308_trigger.jpg

データポータル(DataStudio)で表示してみる

せっかくデータをとってるのでデータポータルデータポータルを使って可視化してみます。

リソースとして該当のSpreadsheets内のStayingTimeシートを選んで追加します。

職場と家とかで、2つ以上の場所で記録して居たりする場合にはそれぞれのシートを追加。

2つ以上ある場合は混合データとしてそれらをStartDateTimeを基準に混合します。

混合する時は左のシートのStartDateTimeに合わせてテーブルが作られるのでより長い期間の記録がある方を左に。 指標にHoursをそれぞれ加えてHoursWorkHoursHomeとかわかりやすい名前で登録しておきます。

20210308_combine.jpg

(注: 上の絵ではちょっと同じSpreadsheets内に作った別シートからデータを取っています。)

積み上げ縦棒グラフを選んでデータソースに上で作った統合データを追加。

ディメンジョンにはStartDateTime

指標に職場時間(HoursWork)、家時間(HoursHome)を集計方法を合計(SUM)にして加えます。 加えて24時間で分かりやすいように 指標を追加からフィールドを作成、で、

24-HoursWork-HoursHome

といった感じの指標を作って加えます。

これでこんな感じのグラフが出来ます。

20210308_graph.jpg

今の実際のところはほとんど家で絵にならないので上の値は適当に入れたものです… (自粛に入った後に作ったもので未だにあまり活躍してないのが悲しい)

上の様な感じで長期的に記録していけば月ごとだったり年ごとだったりでも 家に居た時間とか職場や学校に居た時間とか見ることが出来て新たな発見ができるかもしれません。

追記: 2021/03/09

別にこういうことをしたいわけじゃないんですが、 こんなアプリがあるようです。(もうすぐサービス終了)

残業証拠レコーダー 自動で簡単に残業時間の証拠を確保できるアプリ!

IFTTT+Google Spreadsheetsでこれと同じことが出来ますね! (実際には弁護士側かなにかが管理しているサーバーで改竄できないようにデータ管理できるから法的証拠になるよ、とかなのでしょうかね?単なるメモでも証拠になることもあるみたいなんで使い方次第かもしれませんが。)

ほんとたまたま目についたので追記しておきました。

追記ここまで

Sponsored Links
Sponsored Links

« Raspberry Piからシャープの加湿空気清浄機を操作する GhostText: 好きなブラウザでの入力を好きなエディタで行う »