あくぽろぐ aqpolog

へなちょこエンジニアの備忘録

Googleスプレッドシート上で短縮URLを展開する[Google Apps Script]

ブログスモールスタート第3段。
やや前置き長め。

--目次--

Twitterでの情報収集

情報収集ツールとしてのTwitterはなんだかんだ優秀だと思っていて、
ニュースメディアのアカウントをフォローしておけば気になる記事を割とタイムリーに見ることができるし、
そうでなくても思いも寄らない情報をたまたまTL（タイムライン）で見つけた！みたいなこともまあまあよくある。
※この辺は個人のフォローの仕方にもよるだろうけど…

ただ、その場で読むまでもないかな、とか後でじっくり読もう、みたいな情報も結構ある。
そんなときに使われるのが公式ならいいね（かつてのFav）機能だし、
非公式クライアントなら「あとで読む」に対応しているものも結構ある。

あとで読む（Pocket）

「あとで読む」サービスはざっくり言えば、文字通り何らかの情報をあとで読むために使うサービス。
あとで読むサービスにURLを追加してあげると、それをリストとして保存してくれる。
※保存されたURLをパースしていい感じに本文だけ表示する機能とかもあるけど本題ではないので省略。

現状生き残っている「あとで読む」サービスだとPocketとInstapaperがメジャーどころ。
API対応しているアプリなら、URLを選択して「あとで読む」してあげれば追加できる。
他にもブラウザの機能拡張、ブックマークレットなんかからURLを追加する事もできる。

https://getpocket.com/getpocket.com

www.instapaper.com

あくぽろ流情報収集術

あくぽろ流情報収集術としてはTwitter＆Pocketで上記のことを実践している。

Twitterクライアントは公式アプリではなく、非公式クライアントアプリの「feather」を愛用している。
Pocketのみだが「あとで読む」対応していて、URLを長押しすると追加することができる。
また、これは今の所featherしかできるアプリを知らないのだけど、"TweetそのもののURL"を追加できる。

流れとして、

  1. featherでTL見ている途中で面白そうな記事のURLを見つけた！
    1. 後でじっくり読もう→feather上でURLを長押しして「Pocketに保存」
    2. 内蔵or外部ブラウザで開いてちょっと中身を読む→後でじっくり読もう→ブラウザ上で「Pocketに保存」
  2. featherでTL見ている途中で面白そうなツイートを見つけた！
    1. スワイプメニューから「…」アイコンを選択→「Pocketに保存」※カスタマイズしてPocket保存アイコン追加も可能

みたいに収集して、あとで自宅PCのブラウザで確認する、といった感じで使っている。

ちょっとしたTIPSを紹介したツイートとか、Fav...いいねを辿るより、
こっちのほうが集約できていい感じなのでfeatherのツイート自体を保存できる機能は重宝している。

ちなみにPocketのスマホアプリを入れておけば、公式アプリでもTL上のURL長押しから
iOS共有シート表示→共有シートからPocketアイコン1 をタップして追加できる。 Androidは知らないけど多分似たような感じでできるはず。

憎いt.co

収集方法はさておき、ようやく本題に入る。

featherからURLをPocketに追加すると、twitterの短縮URLであるt.co形式で保存されてしまう。
それ自体はまあ仕方ないのだけれど、職場でPocket上に保存した記事を参照したい時にそのまま開こうとすると、
Firewallバッチリの弊社だとTwitter（とその短縮URLであるt.co）は遮断されてしまう。

見たいのは技術情報が書いてあるブログとかであって、
Twitterではないのに間に短縮URLという形でt.coが挟まってるせいで一発では開けない。

それはめんどくさい。めんどくさいはエンジニアの敵だ（持論）。
じゃあ短縮URL（t.co）の転送先を取得して、そっちを見よう。
でも、t.coそのものにアクセス出来ないんじゃ無理では？ わざわざ自宅でやっておくのか？2
そうだ、Google Apps Scriptでスクレイピングできるって聞いたことあるしそれならやれるのでは？
という発想のもとに出来上がったのが下記スクリプト。

Googleスプレッドシート上で短縮URLを展開する[Google Apps Script]

作業の流れ

  1. PocketからURLをエクスポートする。（HTML形式）
  2. 取得したHTMLの中を見ると<li>で列挙されてるだけだったので、適当に整形3してCSVへ
  3. CSVをGoogleスプレッドシートにインポート
  4. スプレッドシート上で「スクリプト」を選択してスクリプトエディタを起動。
  5. myFunctionを適当な名前にリネームして短縮URLの転送先を取得する関数(expandURL)を作成
  6. 保存して、スプレッドシートに戻る。適当なセルに=expandURL(URLの書かれたセル)を入力して実行
  7. expandURLで展開されたURLが入手できる
  8. フィルタ機能などでt.coドメインだけ抽出して一気に実行
  9. すべて展開出来たら対象列をコピーして、Ctrl+Shift+V（値のみ貼り付け）で上書き
  10. CSVでエクスポート。

※9は再度スプレッドシートを開いたときに不用意に数百～数千回expandURL再実行されるのを防止するため

GAS（Google Apps Script）のコード

function expandURL(url) {
  //var url = 'https://t.co/qSZ4Aj0o4A'; // テスト用
  var options = {"method" : "GET","followRedirects" : false, 'muteHttpExceptions': false};
  Utilities.sleep(1000) // 1sec wait
  var redirect_url = UrlFetchApp.fetch(url,options).getAllHeaders()[ 'Location' ];
  //Logger.log(UrlFetchApp.fetch(url,options).getAllHeaders().toSource()); // テスト用header全表示
  Logger.log(redirect_url); 
  return redirect_url
}

今後の展望

これを機に古いURLや昔何でもかんでも突っ込んだURLを処分・整理したいナーとなっているので、
pythonのpandasで色々して分類整理とか404なURLの削除とかをしたい（というか仕掛中）。

google colaboratoryを使うとpythonが動かせるのでそっちでやれば一貫してるかなーと思うんだけど、
GAS接続がめんどくさい（authやって接続して…）ので、
それならCSVローカルに落として普通に加工したほうが早くない？ってなってるところ。

  1. (参考)共有シートにアイコンがない場合：iPhoneでシェアシートを編集する方法──共有アイコンの追加/削除/並び替えでストレスフリーに | アプリオ↩

  2. そもそも職場で関係のないURL巻き込んでやるなよ、って書いてて思ったけどまあ職場だとその判別もつかないし…いいか！↩

  3. 適当なテキストエディタで余分なタグを一括置換したり、パーサー使うなり。↩