【簡単】グーグルスプレッドシートで自動価格取得（資産記録向け）

1.はじめに

　こんにちは、Edyです。グーグルスプレッドシートを使って簡単に仮想通貨の価格取得する方法を紹介します。記事は無料で最後まで読むことができます。役になったと思ったら投げ銭いただけたらうれしいです。

　仮想通貨（暗号資産）を触っていると、最初はビットコインやイーサリアムを日本の取引所で買っていたのが、いつの間にか取引所とウォレット、草コインだらけで収集がつかなくなると思います。

　取引所やウォレットをまたいだ資産管理のできるAppもありますが、対応しない取引所やチェーンがある、バグでうまく取得できない、など、なかなかピタッとハマらない。また経過記録もしづらい。

　スプレッドシートでの記録は経過記録にはいいですが、集計が手間なばかりか、価格採取が面倒で更新サボりがちになります。そこで自動で取得できたらいいのに、これを解決したいと思います。

　細かい説明は置いておいて、まずは自動取得するためのシートの作り方を紹介していきます。ちょっと長いですが、順を追って書いていますので、1つずつ進めていけばできると思います。
　後半は対応取引所を増やす際の参考情報などを載せましたので必要な方は参考にしてください。

2.グーグルスプレッドシートを使う理由

　アカウントを無料で作成でき、スマートフォンからも簡単にアクセスできる点が気にいっています。またGoogle Apps Script(GAS)というJavascriptとほぼ互換のプログラミング言語を使うことができます。MS OfficeのVisual Basicに相当するのですが、その気になればWebサービスを作ることもできるほど強力です。Webと相性のよいJavascriptベースなので、便利なコードを流用しやすいです。こうした利点から選択しました。

　ただ、グーグルに個人資産を知られたくない、という方は使わないか、有料契約をした方がいいかもしれません。

3.準備

※グーグルのアカウントはすでにあるものとします。また作成時はPCが必要になります。

(1) スプレッドシートを用意する

ここでは＋ボタンを押して新たにスプレッドシートを作成します。既存のファイルを用いても構いません。

(2) Apps Scriptを開く

メニューから　[拡張機能]⇒[Apps Script]　をクリックすると、Apps Scriptが開きます。
なお、現在のGoogleアカウントとデフォルトのGoogleアカウントが異なる場合、アドレスを確認してください、とエラーが出ます。デフォルトのアカウントを変更する、すべてのアカウントをログアウトする、などの対処が必要です（ちょっと面倒くさいですね）

うまくいくと以下のような画面になるはずです。Google Apps Script(通称GAS)と呼ばれるスクリプトのエディタです。といっても今回の仕組みにプログラミングの知識は不要なので、安心してください。

(3) ImportJSON.gsをコピーする

取引所から価格データをもらうためのおまじないを準備します。GASに標準ではないため、別途用意します。
下記のサイトにアクセスし、634行分のコードすべてをコピーします。コピーボタンを押すと簡単にコピーできます。
https://github.com/bradjasper/ImportJSON/blob/master/ImportJSON.gs

(4) ImportJSON.gsを貼り付ける

既存のコードはすべて消してください（残っているとエラーが出ます）。
次に(3)でコピーしたコードを貼り付けます。なお、1行目の最初に「*」が一つ不足していますので、「*」を1つ足します。勝手に「 */」と改行が追加される場合は削除し、1行に収まるように整形してください（下図参照）。

後でわかりやすくするため、名前は「ImportJSON」などに変更してもよいです（必須ではない）

最後にフロッピーボタンで保存したら準備完了、Apps Scriptタブは閉じてOKです。

4.シートの作成 Binance編

ここからいよいよ自動取得シートを作成していきます。ここではまず、binanceで作成します。

(1) 簡単な原理説明

一応、簡単な原理の説明をします。面倒な人は読み飛ばし(2)に進んでください。

APIと呼ばれる取引所が用意したWEB上で動くプログラムがあり、この中には通貨ペアに対して価格を教えてくれる機能が含まれています。APIはアカウントごとに発行した専用のキーを使うことで資産や注文をすることもできますが、今回は価格取得だけなので専用のキーは不要です（セキュリティ的に安心です）。

価格情報を教えてくれるAPIに、取得した通貨ペアを与えると、価格情報が返ってきます。

これだけです。が、APIには取引所ごとに呼び出し方が定められていて、それに合わせて調整する必要があります。また戻ってきた価格データはJSONと呼ばれる文字列形式で、そのままではスプレッドシードに書き込めません。書き込めるように整形し、数字化します。

(2) APIと価格情報、整形用の文字の準備 Binance編

セル A1～C4 を下図のように記載します。重要なのは C2～C4になります（ほかは空白でも動きます）。貼り付けやすいようにテキストも書いておきます。このままコピーしてペーストしてください。
※色は何でもいいです。見やすくするため色づけしています。

https://api3.binance.com/api/v3/ticker/price?symbol=
/price
=trim(SUBSTITUTE(C3,"/"," "))

なお、C4については、手打ちで「price」としても大丈夫です。式を入れているのは、C3の内容を崩した形で後で使うためです。

次に価格取得のため、APIを実行します。セル B6、C6 に次の文字と式を貼り付けてください。

BTCUSDT
=value((SUBSTITUTE(lower(trim(CONCATENATE(ImportJSON(C$2&B6,C$3,"noInherit")))),C$4,"")))

すると上図のように、BTC/USDTの価格が取得できました！

では調子に乗って、いろんな通貨ペアを取得してみましょう。BinanceのAPIは、通貨ペアを続けて記載します。B7～に通貨ペアを記入し、C6のC7～にコピー&ペーストします。こうしている間にもBTCの価格が変わっていますね！

基準通貨はUSDT、BUSDはいけますが、BNBはダメでした。またそもそも上場されていないペアも取得できません。そうしたペアは#ERROR!と表示されます。

5.シートの作成 kucoin, gate.io編

(1) kucoin編

以降は、APIの仕様を調べそれに合わせてAPI、queryのセルを修正すれば、いろいろな取引所の価格を取得することができます。手はじめてにkucoinでやってみます。

B～C列で1セットなので、B～C列をD～E列にコピーしてください。このままだとBinanceのままなので、E2～E3を以下の通りに書き換えます（コピー&ペーストでOK）。

https://api.kucoin.com/api/v1/market/orderbook/level1?symbol=
/data/price

次に2の通貨ペアの間に「-」を入れます。KucoinのAPIの仕様のためです。すると下図のように取得できます。
※USDT建てしか見ないのであればBinanceのペアのUSDTを「-USDT」に置換してもいいですし、別途通貨ペアを2列で作って取引所ごとにAPI用のペアを作成すれば、いちいち書き換える手間が省けますね。

なお、Kucoinは取得できない場合は0になるようです。

(2) gate.io編

最後はgate.ioです。大概のペアはgate.ioがあれば取得できるイメージあります。

B～C列をF～G列にコピーし、G2～G3を以下の通りに書き換えます（コピー&ペーストでOK）。

https://api.gateio.ws/api/v4/spot/tickers?currency_pair=
/last

gate.ioは通貨ペアは「_」(アンダースコア)でつなぎます。

BinanceとKucoinにない銘柄として、KISHUを追加してみました。

以上で完成です。お疲れさまでした！

___________________________________________________________________
以下、詳細な内容になりますので必要な方はお読みください。

6.APIの調べ方とシートへの追加の仕方

Binanceを例に調べ方を記載していきます。なお取引所ごとにドキュメントの書き方は微妙に異なるので、がんばって読み解いてください。

(1) APIサーバー名を調べる

Binance API Documentation

APIドキュメントを見ていきます。「General Info」のところに、endpointというAPIの接続サーバーが記載されています。

ちなみにGoogle経由だと、api3しかつながりませんでした。

(2) 価格情報APIを調べる

次に価格を教えてくれるAPIを探します。Binanceの場合、Market Data Endpointsの中の、Symbol Price Tickerが使えます。

API名はタイトル直下の「GET /～」のところの、「GET 」を除いた部分です。symbolという引数で通貨ペアを指定すると言う内容が書かれています。symbolは別の場所に書き方が書いてあるはずですが、右側のResponseに記載されている通り、LTCBTCなのでLTC/BTC建てですね。つまり

/api/v3/ticker/price?symbol=LTCBTC

として実行すればよいということになります。

どこの取引所も、Responseとして応答内容を記載してくれています。

{
"symbol": "LTCBTC",
"price": "4.00000200"
}

ここでは"price" : "4.00000200"が価格情報と分かります。

(3) シートに追記する

ここまでで調査は終了です。調べた情報を整理すると、

サーバー：https://api3.binance.com
API：/api/v3/ticker/price?symbol=LTCBTC
価格：price

でした。価格情報はシートの別セルに書いてあるものを使いたいので、LTCBTCは削除して、サーバーとAPIを結合します。

https://api3.binance.com/api/v3/ticker/price?symbol=

これがセルC2に記載した内容です。そして、価格情報はpriceですが、ImportJSON関数の仕様でpriceの前に「/」を付けます。

/price

なお、kucoinはResponseの価格が入れ子になっているため、data→priceとなり、/data/priceと記載します。

7.JSONの処理方法

　最後にJSONの処理方法について簡単に補足します。改めてセルC6に記載した数式を再掲します。

=value((SUBSTITUTE(lower(trim(CONCATENATE(ImportJSON(C$2&B6,C$3,"noInherit")))),C$4,"")))

ImportJSONは、queryで抽出した結果を、「項目[改行]値」の形で返してきます。具体的には上記binanceの例では

price
4.00000200

となります。このまま実行すると2行に渡って結果がシートに記入されてしまいます。

　これでは困るので、まずはCONCATENATE関数で1行にまとめます。すると

 price4.00000200

となります。
次に前後の余計な空白をTRIM関数で除去し（これはやらなくてもいいか・・・）、LOWER関数ですべて小文字にしておきます。

その上でSUBSTITUTE関数で4行目「replace string」に記載したpriceを「」(文字なし)に置換します。この関数は大文字小文字を識別するので事前に小文字に統一しました。

最後にVALUE関数で文字列を数値に変換し、完了となります。