IFTTTを使って学校/職場/家の滞在時間を記録する

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

IFTTTを使った位置情報記録
Google Spreadsheetsで直接分解して滞在時間を出す
Google Apps Scriptを使う
日毎に分ける
自動で実行
データポータル(DataStudio)で表示してみる

IFTTTを使った位置情報記録

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

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 (入ったか、出たかでenteredかexitedと記録される。)

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

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

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

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

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

March 1, 2021 at 08:51AM

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

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

enteredとexitedがわかればその間の時間を計算すれば滞在時間がわかるわけですが、 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の様にAM、PMが付いてる形のものが自動的に24時間表記になっていますが、これはデフォルトでこの形は時刻として認識されるので表記がそうなっていますし、値としても時刻として認識された形で入っています。

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

Google Apps Scriptを使う

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

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

{{OccurredAt}} ||| {{EnteredOrExited}} ||| =getTimeDiff(row())

getTimeDiff.gs

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

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関数をエディタから実行すると