仮想通貨の価格データ一覧を自動取得する – Google スプレッドシート

Googleスプレッドシート x 仮想通貨 シリーズ第 2 弾です。シリーズの一覧はカテゴリ [Googleスプレッドシート x 仮想通貨] から見れます。

CoinMarketCapのAPIとGoogle App Scriptを用いて、全仮想通貨の価格データを取得する関数を作成しました。

関数をスケジュール実行すれば、毎日決まった時間にデータを更新することもできます。

1. 実装にいたるまでの背景

うんちくはいいからとにかく動けばいいんです、という方は 使い方 まで進んでください。

草コインの現在価格を取得する方法として、前に草コインの現在価格をAPIで取得するという記事を書きました。この記事では、CoinMarketCapのAPIを用いて、指定した銘柄の現在価格を入手する関数を紹介しました。

例えば、DOGEという仮想通貨の現在価格を取得するには、スプレッドシートに次のように入力する設計でした。

=crypto(“DOGE”)

実行すると、APIを用いてDOGEの価格データを取得し、ドルで表示します。しかし、上の関数 crypto は使い方によっては問題が生じることがあります。

1. 1つの銘柄につき1回APIを呼ぶので、複数の銘柄を管理する場合はAPI呼び出し (クレジット) の利用効率が悪い
例えば20種類の仮想通貨の現在価格を取得するには20回APIを呼ぶので、1時間に1回実行しても1日で480回API呼び出しを行う。Basic Plan (無料アカウント) の1日あたりの上限は333回なので、途中で呼べなくなる。

2. API呼び出しが簡単すぎるため、無駄にクレジットを消費する
=crypto(“DOGE”) とセルに記入しただけでAPIのクレジットを消費するので、シートを作っているうちに知らぬ間にクレジットを消費している。

3. そもそも呼び出し方が良くない
CoinMarketCapのAPIで公開されている仮想通貨には、必ずID、名前、銘柄の情報がある。例えばビットコインなら、ID: 1、名前: Bitcoin、銘柄: BTC という具合。名前や銘柄は他の仮想通貨と被ることがあるけど、IDは一意に振られている。そのため、仮想通貨を特定するときは銘柄ではなくIDで指定するのがよい。

結局、欲しい仮想通貨のIDを調べ、それをもとに10種類欲しかったら10種類まとめてAPIを呼び出す、という実装がいいんだろう。でもいちいち調べてシートに書いておくのはめんどくさいしなあ。

じゃあ全ての仮想通貨の情報を取ってしまえ、ということでCoinMarketCapでAPIを公開している仮想通貨全て (2021年10月現在 約6,900個) の価格データを取得する関数を作りました。

無料アカウントでもAPI呼び出し上限に引っかからないようにしてあります。また、Google スプレッドシートのトリガー機能を使えば、毎日決まった時間に現在価格の一覧を取得することもできます。

2. ソースコードと動作説明

const apiKey = "YOUR_API_KEY";

//
// return: map of crypto info available in CoinMarketCap
//         {id1: [name1, symbol1], id2: [name2, symbol2], ...}
//
function getCryptoMap() {
  let cryptoMap = {}; // {id1: [name1, symbol1], id2: [name2, symbol2], ...}
  let url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/map";

  let requestOptions = {
    method: 'GET',
    headers: {
      'X-CMC_PRO_API_KEY': apiKey
    },
  };

  let result = UrlFetchApp.fetch(url, requestOptions);
  let responseCode = result.getResponseCode();

  if (responseCode == 200) {

    let txt = result.getContentText(); // JSON as a text
    let jsonData = JSON.parse(txt);

    for (let i=0; i<jsonData['data'].length; i++) {
      let id = jsonData['data'][i]['id'];
      let name = jsonData['data'][i]['name'];
      let smbl =jsonData['data'][i]['symbol'];
      cryptoMap[id] = [name, smbl];
    }

    return cryptoMap;

  } else {

    console.error("API response is not 200");
    return null;

  }

}

//
// ids: list of cryptocurrency CoinMarketCap IDs
//      [id1, id2, ... ]
//
// return: map of input ID and price
//         {id1: [price1], id2: [price2], ...}
//
function getCryptoPrice(ids) {
  let priceMap = {}; // {id1: [price1], id2: [price2], ...}
  let strIds = ''; // e.g. "1,2,3,4,5"

  if (!(ids.length > 0)) {
    console.error('id list is empty');
    return null;
  }

  for (let i=0; i < ids.length; i++) {
    if (typeof(ids[i]) != typeof(0)) {
      console.error('id must be number');
      return null;
    }
  }

  for (let i=0; i < ids.length; i++) {
    strIds += ids[i].toString();
    if (i < ids.length-1) {
      strIds += ',';
    }
  }

  let url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/quotes/latest?id=" + strIds;
  let requestOptions = {
    method: 'GET',
    headers: {
      'X-CMC_PRO_API_KEY': apiKey
    },
  };
  let result = UrlFetchApp.fetch(url, requestOptions);
  let responseCode = result.getResponseCode();

  if (responseCode == 200) {
    let txt = result.getContentText(); // JSON as a text
    let jsonData = JSON.parse(txt);

    for (let i=0; i<ids.length; i++) {
      let price = jsonData['data'][ids[i]]['quote']['USD']['price'];
      priceMap[ids[i]] = price;
    }

    return priceMap;

  } else {
    console.error("API response is not 200");
    return null;
  }

}

function main() {
  let mapsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("crypto-map");
  let columnName = [["id", "name", "symbol", "price"]];
  let lastRow = mapsheet.getLastRow();
  let map = getCryptoMap();
  let mapArray = []; // array of variable "map" [[id1, name1, symbol1, price1], [id2, name2, symbol2, price2], ...]
  let ids = [];
  let idsArray = []; // array of variable "ids" [[id1, id2, ... , id100], [id101, id102, ... , id200], ...]
  let count = 0;
  let numIdsPerRequest = 100;

  if (map == null) {
    console.error("map is null");
    return null;
  }

  // divide ids every 100
  for (i in map) {
    ids.push(Number(i));
    count++;
    if (count % numIdsPerRequest == 0 || count == Object.keys(map).length) {
      idsArray.push(ids);
      ids = [];
    }
  }

  let totalRequests = 0;
  // call API every devided ids
  for (let i=0; i < idsArray.length; i++) {
    console.log("call getCryptoPrice() " + (totalRequests + 1) + "-" + (totalRequests + idsArray[i].length));
    totalRequests += idsArray[i].length;
    let prices = getCryptoPrice(idsArray[i]);
    for (j in prices) {
      map[j].push(prices[j]); // {id1: [name1, symbol1, price1], id2: [name2, symbol2, price2], ...}
    }
    Utilities.sleep(3000); // prevent exceeding API call rate limit
  }

  for (i in map) {
    mapArray.push([i, map[i][0], map[i][1], map[i][2]]);
  }

  // row, column, numRow, numColumn
  if (lastRow > 0) {
    mapsheet.getRange(1, 1, lastRow, mapArray[0].length).clear();
  }
  mapsheet.getRange(1, 1, 1, columnName[0].length).setValues(columnName);
  mapsheet.getRange(2, 1, mapArray.length, mapArray[0].length).setValues(mapArray);

  return;

}

main() について
getCryptoMap() を呼び出し、仮想通貨のID、名前、シンボル (銘柄) の一覧を取得します。
取得したIDを元に getCryptoPrice(ids) を呼び出し、価格データを取得します。全てのIDをまとめて呼び出すことはできないので、100個ごとに getCryptoPrice(ids) を呼び出します。例えばIDが6,950個あったら、70回呼び出します。この場合クレジットの消費は、getCryptoMap() の分を足して計71回になります。各呼び出しは、Basic Plan の API call rate limit: 30 requests a minute に引っかからないないよう、4秒おきに実行します。
全てのIDについて価格を入手したら、シートに記入します。

getCryptoMap() について
/v1/cryptocurrency/map エンドポイントを呼び出します。このエンドポイントからは、公開しているすべての仮想通貨それぞれの、IDや名前などの基本的な情報が返ってきます。関数は、id, name, symbol の3つの情報を、 id をキーとするハッシュテーブルとして返します。これですべての仮想通貨のIDが手に入ります。

getCryptoPrice(ids) について
/v1/cryptocurrency/quotes/latest?id= エンドポイントを呼び出します。仮想通貨IDのリストを受け取り、それらをまとめてAPIを呼びます。返ってきたデータから価格 (USD) を取り出し、id, price のデータを、 id をキーとするハッシュテーブルとして返します。各仮想通貨IDとその価格の情報が手に入ります。

一応、実装した際に自分が考慮した Basic Plan の仕様を載せておきます。詳細はCoinMarketCapにログインして確認してください。

• Daily credit limit: 333 (soft cap)
• Monthly credit limit: 10,000 (hard cap)
• API call rate limit: 30 requests a minute

3. 使い方

1. CoinMarketCapのアカウントを作成し、APIキーを取得します。プランは Basic Plan で構いません。

2. “crypto-map” という名前のシートを作成します。この名前はソースコードに書いてあるので、別な名前にしたい場合はソースコードも変えてください。

3. [ツール] -> [スクリプトエディタ] をクリックします。

4. 適当な名前のスクリプトを作成します。デフォルトでは “コード.gs” というスクリプトがあるのでそれを上書きしてもいいですし、新しいスクリプトを作成しても構いません。

5. 上に記載したソースコードを貼り付けます。1行目にある YOUR_API_KEY を自分のCoinMarketCap APIキーに置き換えます。

6. 実行する関数を main にして、[実行] をクリックします。”call getCryptoPrice() 1-100″ というような実行ログが出力されていれば動作しています。2021年10月現在 CoinMarketCap のAPIには 約6,900個のIDが存在し、実行完了に5分ほどかかります。

実行が完了すると、以下の図のように仮想通貨の id, name, symbol, price のリストがシートに書き込まれます。

ここまでで動作確認は完了です。あとは皆さんのお好きなようにシートを作ってください。

↓例えば、毎日価格のデータを更新したい場合は、トリガーを設定します。この図のように設定すると、毎日朝5時～6時の間にスクリプトが実行され、仮想通貨の情報が更新されます。

以下、注意点を挙げておきます。

crypto-map シートの A, B, C, D 4列は、スクリプトが実行されると一度内容がクリアされるようになっています。あまりないと思いますが、この4列には何も書き加えない方が安全です。E列以降は自由に編集することができます。

このスクリプトを実行すると、約70クレジットを消費します。Basic Plan の1日あたりAPI呼び出しの上限が333回なので、高頻度に呼び出すことはできません。リアルタイムに価格を取得したい場合は、冒頭で紹介した crypto のような関数を使用するか、個別にスクリプトを作る必要があります。しかし、それをしても Google スプレッドシートの性質上10分くらいの遅れは出ると思います。CoinMarketCap API と Google スプレッドシートの組み合わせは、あくまで長期的な資産管理として利用することをオススメします。

まとめ

この記事では、CoinMarketCapのAPIをGoogle スプレッドシートから呼び出し、すべての仮想通貨の価格データを取得する方法を紹介しました。リアルタイムに価格データを参照できるわけではありませんが、1日に1回の実行ならば無料アカウントでもクレジットに余裕が出る実装です。

皆さんの仮想通貨運用に役立つことを願っています。読んでくださり、ありがとうございました。