Swarm（旧Foursquare）のチェックイン過去履歴をGoogleカレンダーに一括登録する方法（前編）

情報管理LOGの@yoshinonです。
先日来より懸案事項だったSwarmのチェックイン履歴がGoogleカレンダーに反映されなくなった問題ですが、その完結編になります。前回の記事から時間的に空いてしまったのは、非常に手こずっていたからです。うむむ。というわけで、この記事は前後編になります。
とにかく大変だったので、そのあたりの流れも含めてレポートします。

【　Swarm（旧Foursquare）のチェックイン過去履歴をGoogleカレンダーに一括登録する方法　】　　 　１．Swarm（旧Foursquare）のチェックイン履歴が、Googleカレンダーに反映されなくなった問題 　２．Foursquareからチェックイン履歴をダウンロードする方法 　３．Excelでやろうとすると… 　４．さらに、「unixtime」問題

先日来より情報管理LOGで話題にしていたのですが、今までSwarmのチェックイン履歴が iCal形式で提供されており、Googleカレンダーに自動で反映していたものの、どうやら今年３月末までで提供がストップしていたようなのです。

Swarm（旧Foursquare）でチェックイン履歴のGoogleカレンダーが使えなくなった...

なんてこったい…。




今までは、このようにGoogleカレンダーを見れば、「いつ」「どこへ」行ったのかが、即分かったのに、それが使えなくなるのは、非常に痛いです。




とはいえ、IFTTTを使うことによって、これからチェックインしたものの履歴をGoogleカレンダーに残すことは、できるようにしました。

Swarm（旧Foursquare）でチェックイン履歴がGoogleカレンダーに残せなくなってしまったので、IFTTTで自動的に記録されるようにしてみた

もしも、これから自分のチェックイン履歴を残したいという人は、これで解決です。





さて、ここからは、長いことSwarmでチェックインしてきた人が対象となります。今までのチェックイン履歴を全てGoogleカレンダーに反映させたいですよね？
あと、今までSwarm使ってきたけど、アプリ内でしか履歴見てなかったけど、Googleカレンダーで見られるなら、ぜひやってみたいという人もぜひ、読んでみてください。

※注：今回の記事では、完全解決まで至りません。次回の後編で解決しますので、読むお時間のない人は後編をお待ちください。

まずは、Swarmのチェックイン履歴をダウンロードします。
Swarmのチェックイン履歴は、実はFoursquareにあります。ここが、すでに分かりにくいですよね。

FoursquareのWeb版にログイン＞設定＞プライバシー設定

からダウンロードできます。





設定




「自分のデータをダウンロード」でダウンロードできます。







Zipで「data_export_○○」という名称でダウンロードされるので、それを解凍します。そうすると、色んなデータが入っていますが、必要なのは、「checkins.csv」１つだけです。
これですね。





CSV形式でダウンロードできたので、それをExcelで開いてみます。





えっと…。これ、やたら扱いづらいデータの並び方ですよね。
なぜ、１行おきにタイトル行来るし…。
しかも、「venue.name」は、文字化けしているし。

文字化けに関しては、「checkins.csv」をメモ帳で開くと、ちゃんと文字化けせずに表示されます。





これは、文字コードの問題だと思ったので、文字コードを変更して、何回か保存し、Excelで表示するということを何度かしてみたのですが、解決しませんでした。
※誰か、このあたりの解決方法分かる方いたら教えてください。

ちなみに、Excelで１行おきのデータを１つにまとめるには、OFFSET関数を使うとできます。
例えば、１列目を表示させるための数式は、このようになります。

=OFFSET(checkins!B$2,ROW(A1)*2-2,0)

こうすると、１行おきにデータがあっても１個飛ばししながらデータを拾ってくれます。





さらに、問題だったのが、「venue.name」でした。
文字化けもそうですが、それ以外にも

例えば、１行目

繧ｻ繧､ｹ裂（文字化け中略）ｫ蠎・,https://foursquare.com/v/%E3%82%BB%E3（URL中略）%88-

みたいな感じで、チェックイン名らしき文字列＋FoursquareのURLとなっているのですよ。これは、どうやら、CSVをExcel側でちゃんと読み取りできなかったため、隣の列の「venue.url」とデータが混じってしまったと考えられます。

これも、先ほどと同じように別タブで１行飛ばしのデータを読み取った上で、さらにmid関数とfind関数でデータの区切り位置を見つけ出して、切り出すという数式でやれそうです（正直めんどい…）。

数式的には、こんな感じですね。

=IFERROR(MID(OFFSET(checkins!F$2,ROW(D1)*2-2,0),1,FIND(",",OFFSET(checkins!F$2,ROW(D1)*2-2,0))),OFFSET(checkins!F$2,ROW(D1)*2-2,0))






まあ、文字列の抽出とかだったら、やれないレベルではないのですが、さらにもう一つ大きな問題が…。
それが、「unixtime」問題です。

unixtimeって何？

という人もたくさんいますよね。私もその一人でした。

「createAt」の部分が、チェックインの時間を示しているのですが、エクセルの表示形式でやってみると、全然時間として認識してくれないのです。





unixtimeについては、ここら辺を読むと分かります。


UNIX時間 - Wikipedia




GMT、UTC、UnixTimeって何が違うの？ - きりだるまの記録帳




1970年1月1日を起点として、「0」とします。1秒進むと数字が1つ上がって「1」となるというシンプルな仕組みです。とはいえ、あまり一般的とは言いがたいような形式ですよね（考え方としては、便利そうだけど）。

とはいえ、これをシリアル値に直したいので、ここを参照しながらやってみました。


UNIX Time から EXCELのシリアル値（日付・時間）に変換する方法 | あさひグループ （税理士法人あさひ会計、旭ブレインズ） 山形県山形市 税理士法人 税務会計 経営コンサルティング




[Excel]UNIX Time から日付（yyyy/mm/dd(aaa) hh:mm:ss）に変換 | ntの備忘録




数式的には、こうなります。

=(A1 + 32400) / 86400 + 25569

これで、シリアル値になりそうです。
ふぅ。

さて、ここまでが前編です。
Foursquareのデータは、個性が強すぎて、いじるだけでも面倒極まりない形式に名手います。そういうわけで後編では、Foursquareデータを一気にGoogleカレンダーに反映させるまでのフォーマットを含めて公開します。お楽しみに！！






Foursquareのデータは、上でも書きましたが、非常に扱いづらいデータでした。とても、エクスポートして利用する人のことを考えてない感じがします。こういう最終的には、個人のもののデータは、汎用性を高めて欲しいですよね。
少なくても、unixtimeは、カンベンして欲しかったです。

関連記事

• Swarm（旧Foursquare）のチェックイン過去履歴をGoogleカレンダーに一括登録する方法（後編）
• Swarm（旧Foursquare）のチェックイン過去履歴をGoogleカレンダーに一括登録する方法（前編）
• Amazonプライムデーを利用して7360円分得をしよう！