https://swfz.hatenablog.com/entry/2024/10/20/184853

ObsidianでDailyNoteやWeeklyNote、MonthlyNoteのTry項目などをBigQueryに集めて可視化している

Try項目などは一覧で見るのでそういえばあーこれやったなーみたいなのはLookerStudioからそのTryが記述されている場所に遷移してチェックしたい

よい方法ないかなーと色々調べてたら一応形にはできたので残しておく

仕組み

LookerStudioのHypperlink + URL Scheme + (リダイレクタ)

URL Scheme

アプリケーション側が用意をしていれば特定のURL形式で遷移すると、アプリケーション上での動作と同じような挙動をさせることができる

Obsidian URIの利用 - Obsidian 日本語ヘルプ - Obsidian Publish

publish.obsidian.md

調べてみたらObsidianにもいくつかあった

リンクをクリックして「特定のファイルに遷移する」ができればよいので今回はopenアクションを使う

遷移用のURL

とりあえず、適当なノートの適当な見出しに遷移してみる

ドキュメントを見ながら、URLを作る

保管庫sample-vaultのdaily_note/2024-05-23というファイルの## Tという見出しの箇所へ遷移する

obsidian://open?vault=sample-vault&file=daily_note%2F2024-05-23%23%23T

見出しの表現方法はObsidian内でのリンクの指定と同じだが、今回はURLなので

ドキュメントにも記載があるように/や#などURLに影響を与えるような文字列はURLエンコードした状態にする必要がある

%2Fは/

%23は#

作ったリンクを適当なページなりSlackなりに貼り付けてクリックすると対象ファイルの## Tの箇所に遷移してくれる

特定の見出しへの遷移

見出し指定での遷移がとても良いのを伝わりやすくするためにサンプルを使って紹介する

サンプルとして、markdown_sampleというファイルへ遷移する場合

obsidian://open?vault=sample-vault&file=markdown_sample%23%23%23hoge

遷移時のスクショ

fileパラメータの%23%23%23hogeの部分が### hoge

該当セクションが選択状態になっているのでわかりやすい、コンテンツが長い場合だとしっかり選択範囲が可視範囲に入るようにもしてくれるので遷移してすぐ編集できる

ファイルへの遷移だけでもよいかなと思っていたけど最後のこの一手間で体験がかなり違う

LookerStudioのレポートからのリンク

URLの作り方はわかったので、自動でデータに応じたURLを生成したい

素直にLookerStudio側でObsidian用のリンクを作ってやってみたら遷移できなかった…

タブは起動されるがURLが次のようになっていた

about:blank#blocked

LookerStudioでリンク文字列にカーソルを置いて遷移先を見てみても

about:invalid#zClosurez

となっていて、http以外のプロトコルはハイパーリンクで遷移できないようになっているよう

リダイレクタ

そこで、ローカルにリダイレクタを立ててそれに対してアクセスさせる

別にローカルでなくても良いけど、性質上公開して変な使われ方をしても困るのでいったんはローカルに立てた

どこかにホストして立てる場合は認証いれる、バリデーションをしっかりするなど変な使われ方をしないよう制限を掛ける必要がある

で、リダイレクタに対応したURLをLookerStudioで作り、レポートからObsidianへ遷移する

下記に遷移の流れの簡単なイメージを示す

実装例

今の時代リダイレクタくらいならLLMに聞いてさっと動かせるので、使い慣れた言語で実装してもらえば良い

一応Pythonの実装例を載せておく

redirector.py

from http.server import BaseHTTPRequestHandler, HTTPServer
from urllib.parse import urlparse, parse_qs

class RedirectHandler(BaseHTTPRequestHandler):
    def do_GET(self):
        # クエリパラメータを解析
        parsed_url = urlparse(self.path)
        query_params = parse_qs(parsed_url.query)

        # "redirect"パラメータがある場合は、そのURLにリダイレクト
        if 'redirect' in query_params:
            redirect_url = query_params['redirect'][0]
            self.send_response(302)  # 302 Found (一時的なリダイレクト)
            self.send_header('Location', redirect_url)  # リダイレクト先のURL
            print(f'{redirect_url}')
            self.end_headers()
        else:
            # 通常のレスポンス
            self.send_response(200)
            self.send_header('Content-type', 'text/html')
            self.end_headers()
            self.wfile.write(b"Hello, this is the default page!")


def run(server_class=HTTPServer, handler_class=RedirectHandler, port=8000):
    server_address = ('', port)
    httpd = server_class(server_address, handler_class)
    print(f'Starting http server on port {port}...')
    httpd.serve_forever()

if __name__ == "__main__":
    run()

実行

ローカルで起動させておく

pytyon redirector.py

BigQuery上でObsidianのファイルを開くためのURL作成

リダイレクタを挟めばLookerStudioのレポートから1クリックで直接Obsidianへ遷移できるようになった

が、そのためのURLの生成も現状のプロジェクト構成上少し面倒だった

BigQueryにURL Encode用の関数はない
- UDF(CREATE TMP FUNCTION)でJavaScript関数を定義すれば可能ではある
- LookerStudioのカスタムクエリではCREATE TMP FUNCTIONを使用できず断念
- Dataformのpre_operationでUDF作って実行しようとためしたが、設定がtype: viewのファイルでは実行不可
  - 対象プロジェクトのすべてのsqlxがtype: viewだったのでこのためにtype: tableへ変更しようとは思えなかった

どこでURL生成しようか迷ったが最終的にはBigQuery(Dataform)でbqutilを使うことにした

bqutil

bigquery-utils/udfs at master · GoogleCloudPlatform/bigquery-utils

github.com

URL Encode、Decodeや他にもよく使いそうな処理が最初から使えるように組み込まれているので自前実装をしなくて良い

これで賄えるなら乗っておいたほうが良いと思う

実際に利用可能な関数はbigquery-utils/udfs/community/README.md at master · GoogleCloudPlatform/bigquery-utilsにある

リストが乗っているのと、同ディレクトリにSQLXファイルがあり実装内容も記述されているのでどんな引数が必要かとかどんな実装しているのかなども読んで把握できる

各リージョンで使えるよう用意されているのでそれを指定するだけで使用できる

bqutil.fn_{regionのアンスコ区切り}.{function名}

でOK

これめちゃくちゃ良い

実際のSQL

bqutil.fn_asia_northeast1.url_encode(CONCAT('obsidian://open?vault=sample-vault&file=monthly_note/', month, '%23%23T'), 'encodeURIComponent') as obsidian_link

計算フィールド

LookerStudio側の計算フィールドは下記のようにした

HYPERLINK(CONCAT("http://localhost:8000?redirect=", obsidian_link), task)

obsidian_linkは前節で生成したObsidian上のファイルを開くためのURLが記載されたデータ

taskはタスク名

リダイレクタの実装はredirectパラメータのURLにリダイレクトさせるという単純なものなのでURLエンコードされた文字列をパラメータとして渡す

まとめ

LookerStudio上のハイパーリンクからObsidianの特定ファイルの特定箇所へ遷移できるようにしてみた

LookerStudioの制限でObsidianのURLスキーマをそのまま指定して直接遷移はできない
リダイレクタを挟んで適切なURLを渡すとリダイレクトできるようにすることでObsidianの特定ファイルの特定箇所へ遷移が可能
遷移用のURL生成のためにURLエンコードが必要だが、BigQuery上でもbqutilを使うことで自前実装しなくてすむ

所感としては

異なる環境（PCなど）間で差異を出せないためvault名(ディレクトリ名)を統一するなどの必要がある
リダイレクタ公開でやるなら制御しっかりしないと危険
Obsidianの特定ヘッダまで指定して遷移できると遷移してすぐ編集できるので体験が良い

などがある

最初レポートみてObsidianのファイルスイッチャーで遷移しても変わらんだろと思ってたけど実際に使ってみるとやはりこちらのほうが体験良い

しばらく使ってみたらまた使用感は変わるかもしれないが、今のところは快適