仮想通貨の価格データを日ごとに記録する – Googleスプレッドシート

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

本ブログにいただいた要望を受けて、CoinMarketCap の API と Google App Script を用いて任意の仮想通貨の価格を日ごとに記録していくスクリプトを作成しました。

1. 経緯

シリーズ第 1 弾で、仮想通貨のシンボル (“BTC”など) を引数に取り現在価格を返す関数を作成しました。その記事で、「毎日現在価格を自動取得し記録することはできないか?」というコメントをいただきました。

イメージとしては、毎日決まった時間にスクリプトをトリガー機能で実行し、実行するたびに日付と価格のデータが1行ずつ追加されるというものです。あったら便利そうだと思ったので、作ってみました。

こんな感じ。

2. ソースコード

ソースコードは以下の3つのファイルに分かれています。

  1. CoinMarketCap.gs
    • CoinMarketCap の API を呼び出すユーティリティ関数群
  2. writeCryptoMap.gs
    • CoinMarketCap で管理されている仮想通貨の ID 一覧を取得しシートに書き込む
    • storeCryptoPrice を実行する際に必要な ID を確認するために使用する
  3. storeCryptoPrice.gs
    • 今回のメインとなるスクリプト
    • 実行すると指定された ID の仮想通貨の価格を取得しシートに書き込む
    • CURRENCY 変数を変更することで取得する通貨を選択可能 (デフォルトで USD, JPY を取得)

CoinMarketCap.gs

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, ... ]
//
// currency: currency code such as USD, JPY
//
// return: map of input ID and price
//         {id1: [price1], id2: [price2], ...}
//
function getCryptoPrice(ids, currency) {
  let priceMap = {}; // {id1: [price1], id2: [price2], ...}
  let strIds = ''; // e.g. "1,2,3,4,5"

  if (!currency) {
    console.error('currency is null');
    return null;
  }
  if (!(ids.length > 0)) {
    console.error('id list is empty');
    return null;
  }
  for (let i=0; i < ids.length; i++) {
    if (!Number.isInteger(ids[i])) {
      console.error('id must be integer');
      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 + "&convert=" + currency;
  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'][currency]['price'];
      priceMap[ids[i]] = price;
    }

    return priceMap;

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

}

writeCryptoMap.gs

//
// This function writes id, name, and symbol for all
// available cryptocurrency in CoinMarketCap
//

function writeCryptoMap() {
  const SHEET_NAME = "crypto-map"; // sheet name to write data

  let mapsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  let columnName = [["ID", "Name", "Symbol"]];
  let lastRow = mapsheet.getLastRow();
  let map = getCryptoMap();
  let mapArray = []; // array of variable "map" [[id1, name1, symbol1], [id2, name2, symbol2], ...]
  let mapLength = 0;

  if (map == null) {
    console.error("map is null");
    return null;
  }
  mapLength = Object.keys(map).length; // number of cryptocurrency
  console.log("Number of cryptocurrency: " + mapLength);

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

  // 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;

}

storeCryptoPrice.gs

//
// How to use
// 1. Specify currency code in CURRENCY variable
// 2. Create a sheet named "crypto-price-XXX" where XXX is currency code declared in CURRENCY
// 3. Write CoinMarketCap id on B1, C1, D1, ...
// 4. Write corresponding symbols on B2, C2, D2, ... (optional)
// 5. Run storeCryptoPrice()
//

function storeCryptoPrice() {
  const CURRENCY = ["USD", "JPY"]; // list of currency code
  const NUM_CRYPTO = 100; // maximum number of crypto to fetch
  const ID_ROW = 1; // row number for id
  const ID_COLUMN = 2; // start column for id

  for (let i = 0; i < CURRENCY.length; i++) {
    let currency = CURRENCY[i];
    let sheetName = "crypto-price-" + currency;
    let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    let row = sheet.getRange(ID_ROW, ID_COLUMN, ID_ROW, NUM_CRYPTO).getValues();
    row = row[0];
    let ids = [];
    let map = {};
    let values = []; // list of values to write on the sheet

    // Extract only integer
    for (let i = 0; i < row.length; i++) {
      if (Number.isInteger(row[i])) {
        ids.push(row[i]);
      } else {
        break;
      }
    }

    map = getCryptoPrice(ids, currency);
    if (map == null) {
      console.error("map is null");
      return null;
    }
    //console.log(map);

    for (let i = 0; i < row.length; i++) {
      let price = map[row[i]];
      if (price != null) {
        values.push(price);
      } else {
        values.push('');
      }
    }

    const now = new Date();
    const timeZone = 'Asia/Tokyo';
    const date = Utilities.formatDate(now, timeZone, 'yyyy/MM/dd')
    values.unshift(date);

    sheet.appendRow(values);
  }

  return;

}

3. 使い方

1. CoinMarketCap の API キーを取得します。取得の簡単な手順はシリーズ第 1 弾に記載しています。

2. Googleスプレッドシートで、以下の名前のシートを作成します。もし名前を変更する場合は、ソースコードも変更してください。

  • crypto-map
  • config
  • crypto-price-JPY
  • crypto-price-USD

3. [拡張機能] > [Apps Script] をクリック

4. 以下の3つのファイルを作成し、それぞれ上のソースコードの章にあるコードをコピーします。

  • CoinMarketCap.gs
  • writeCryptoMap.gs
  • storeCryptoPrice.gs

5. CoinMarketCap.gs 1行目の YOUR_API_KEY を自分のAPIキーに置き換えます。

6. すべての仮想通貨のID、名前、シンボルを取得します。
writeCryptoMap.gs を選択 > [実行] をクリック

7. 実行が完了したら crypto-map シートに仮想通貨の ID、名前、シンボルが書かれていることを確認します。

8. 以下のように config シートを作ります。基本的にA列に欲しい仮想通貨の ID を書くだけです。

セルセルの値備考
A1IDただのカラム名
A2, A3, …欲しい仮想通貨を crypto-map から探し ID を書く
B1Symbolただのカラム名
B2=arrayformula(if(isblank(A2:A), "", vlookup(A2:A,'crypto-map'!$A:$C,3)))ID に相当するシンボルを取得する

9. crypto-price-JPY, crypto-price-USD シートそれぞれに以下の式を代入します。

セルセルの値備考
B1=transpose(if(counta(config!A1:A)>1, indirect(concat("config!A2:B",counta(config!A1:A))), ""))config から ID とシンボルを参照

10. 設定した ID を元に価格が取得できるかどうか試します。
[Apps Script] の画面に戻り、storeCryptoPrice.gs を選択 > [実行] をクリック

11. crypto-price-JPY, crypto-price-USD シートに日付と価格が書き込まれていれば成功です。

12. 日ごとに実行させるため、 [Apps Script] 画面にて [トリガー] > [トリガーを追加] をクリックし
下図のように storeCryptoPrice 関数を時間主導型でトリガーするように設定します。

設定は以上で終わり。これで毎日決まった時間に価格を記録してくれます。

以下、注意点などです。

  • 仮想通貨の指定は config シートの ID 列を編集するだけでよく、他は特に触る必要はありません。
  • storeCryptoPrice 関数が価格データを取得する際に参照する値は config の1列目に書かれた ID であり、2列目のシンボルは見ていません。同じシンボルを持つ仮想通貨は複数存在するので、常に ID で指定する必要があるからです。
  • config 1列目から参照する ID の数は 100 としています。つまり最大 100 個の仮想通貨を設定することができます。
  • storeCryptoPrice 1 回の呼び出しで crypto-price-XXX × 1クレジットを消費します。

4. まとめ

この記事では、Googleスプレッドシート x 仮想通貨 シリーズ第 3 弾として仮想通貨の価格を日ごとに記録するスクリプトを紹介しました。トリガー機能を使えば、毎日自動で価格を記録してくれます。

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

12件のコメント

  1. お世話になっております。
    こちらを要望したアルトです。
    お忙しい中、本当にありがとうございます。

    質問があります。

    価格シートに、別のデータを入力してしまうと、エラー「id must be number」「map is null」と2つ出てしまいます。

    こちらエラーを出さずに「ID」だけ抽出する方法はありますでしょうか?

    最初は価格シートから「=IMPORTRANGE(“価格シートURL”,”crypto-price!B3″)」で既存のデータ用シートに引っ張れば良いと考えたのですが「IMPORTRANGE」だと連続コピーした際、セル番号が固定されてしまい、そこだけ手直しする必要がありました。

    既存データの右端にでもこちらの価格シートを入力できれば、円換算するときも「GOOGLEFINANCE(“CURRENCY:USDJPY”)*セル番号」などが使えて便利かなと素人ながらに考えてみたのですが、そもそもそういったことは可能なのか?の判断をできかねるため、森島様のご意見を伺いたいです。

    また今回のお礼として、Amazonギフト券をお送りしたいと思っています。
    こちらのブログに記載されているメールアドレスに送っても大丈夫でしょうか?

    以上2点、お時間あるときにご確認いただけますと幸いです。

    1. Author

      ご連絡いただきありがとうございます。

      > こちらエラーを出さずに「ID」だけ抽出する方法はありますでしょうか?
      はい、可能です。シートの1行目に書かれたデータのうち、整数だけ ID とみなす処理を実装すれば解決すると思います。
      ただ、後述しますがスクリプトを修正しようと思うので、この問題はひとまずなくなる見込みです。

      > 「IMPORTRANGE」だと連続コピーした際、セル番号が固定されてしまい、そこだけ手直しする必要がありました。
      これは、arrayformula を使うことで解決すると考えられます。
      =arrayformula(IMPORTRANGE(“URL”,”crypto-price!B3:B”))
      とすることで、crypto-price B列 の 3行目 以降を参照することができ、crypto-price にデータが追加されると別シートも自動で更新されます。
      arrayformula 式の下のセルには、何も書かないでください。
      式の「B3:B 」は 「B3, B4, B5, …」を表しています。( 1列 x N行 )

      =arrayformula(IMPORTRANGE(“URL”,”crypto-price!A1:E”))
      などとすると、A – E 列の行をすべて取得します。( 5列 x N行 )

      > 円換算するときも「GOOGLEFINANCE(“CURRENCY:USDJPY”)*セル番号」などが使えて便利かなと
      はい、円にするときはGOOGLEFINANCEを使ってドル円を取得するのが良いと思います。
      ただ、現在のドル円を使って過去の価格を換算すると、価格が正しくならないので本当は価格を取得する際に
      ドル円も取得する必要があると気付きました。
      つまり今の実装では、正確な円価格を記録していくことはできないと思います。
      そこで、crypto-price-USD, crypto-price-JPY のように2つのシートを作り、ドルと円を取得するように
      修正しようと思うのですが、どうでしょうか。
      こうすれば、例えば円価格が欲しい場合は crypto-price-JPY に対して arrayformula を使えば他のシートから参照できると考えます。

      > また今回のお礼として、Amazonギフト券をお送りしたいと思っています。
      > こちらのブログに記載されているメールアドレスに送っても大丈夫でしょうか?
      本当ですか、ご親切にありがとうございます。
      自分は報酬を期待していたわけではないのでタダでも全く問題ないのですが、ご厚意に甘えさせていただきます。
      はい、ブログ記載の ryo.morishima@outlook.com で大丈夫です。

      1. 森島様
        いつもお世話になっております。

        arrayformulaは知らない関数でした。
        これはめちゃめちゃ使えますね。
        ありがとうございます。

        ただ現状、=IMPORTRANGE(“URL”,”crypto-price!B3″)*GOOGLEFINANCE(“CURRENCY:USDJPY”)で円換算していたのですが、
        arrayformulaを入れるとERRORになってしまいました。

        円換算の件、なるほど確かにその通りですね。
        これはcrypto-price-USD、crypto-price-JPYも現在のシート同様に1列目に「日付」1行目に「ID」2行目に「シンボル」その下に「価格」といった実装でしょうか?

        過去の価格について、2022年1月1日〜取得する実装とかもあれば便利かもです。

        とりあえず新しいスクリプトが完成するまでは、予めセルに=IMPORTRANGE(“URL”,”crypto-price!B3”)*GOOGLEFINANCE(“CURRENCY:USDJPY”)を入力しておき、対応しようと思っています。

        ちなみに、この場合の円換算なら「トリガーに設定した時刻の時価」という認識で合っていますでしょうか?

        またこちらも素人考えですが、ドル円も一つのシートにまとめることができれば理想です。

        例えば

        セルA列に「日付」
        セルB1〜D1に同じ「仮想通貨ID」
        セルB2に「シンボル」
        セルC2に「ドル換算」
        セルD2に「円換算」
        セルB3に「仮想通貨の時価」
        セルC3に「ドルの時価」
        セルD3に「円の時価」
        ※B3〜D3はトリガーで設定した時刻の時価

        のようなものは可能でしょうか。

        不可なら森島様の案で全然大丈夫ですので、ドル円も取得できますと助かります。

        さきほどお礼メールも送らせていただきました。
        ご確認よろしくお願い致します。

        1. Author

          > これはcrypto-price-USD、crypto-price-JPYも現在のシート同様に1列目に「日付」1行目に「ID」2行目に「シンボル」その下に「価格」といった実装でしょうか?
          はい、そのつもりです。

          > 過去の価格について、2022年1月1日〜取得する実装とかもあれば便利かもです。
          過去の価格の取得はできるのですが、これはスプレッドシートでの実装が大変なので今のところ考えていません。

          > この場合の円換算なら「トリガーに設定した時刻の時価」という認識で合っていますでしょうか?
          おそらく違うと思います。googlefinance() は常に現在のドル円を取得するので、crypto-price に書かれた
          価格と乗算しても、あまり意味のある数字にはなりません。ドル円が大きくは変動しないので、大まかには真の円価格と一致します。

          > ドル円も一つのシートにまとめることができれば理想です。
          書いていただいた通りのシートを作成することはできます。
          ただ、経験上いろんな情報を一つのシートにまとめると後で参照するときに
          列が飛び飛びになって面倒くさいことが多いので、申し訳ありませんがシートを複数作成する方針です。
          もしいろんな情報を一つのシートにまとめたい場合は、複数のシートから参照して自分好みのシートを作ります。
          crypto-price シートはあくまでデータ用という位置づけであり、このシートは加工するのではなく
          他のシートから参照されるものというイメージです。

          > さきほどお礼メールも送らせていただきました。
          ありがとうございます。メールを返信させていただきました。

          新しいスクリプトが完成したら、記事を更新して連絡いたします。

          1. Author

            先ほど記事を更新しました。

            新しいスクリプトになっているので、お手数ですが新しいスプレッドシートファイルを
            作成するなどして手順を最初から実行していただけますか。

  2. 森島様
    いつもお世話になっております。
    記事の更新ありがとうございます。

    先ほどテストしたところ、うまくいきました。
    あとは明日設定したトリガーが実行されればバッチリです。

    本当に助かりました。
    テスト後思わず「この人マジ天才マジ天才」と連呼しておりました。笑

    1点だけ=transpose(INDIRECT(concat(“config!A2:B”,COUNTA(config!A1:A))))のみ、以下のエラーが出てしまいました。

    「関数 INDIRECT のパラメータ 1 の値が「config!A2:B1」です。これは無効なセル/範囲の参照です。」

    よくわからなかったので、IDは手打ちにしましたが、もし原因がわかるようでしたら、ご教授いただけますと幸いです。

    1. Author

      いえいえ、そんな大したことではありません笑。

      =transpose の式ですが、同じエラーは確認できませんでしたがおそらく
      config に ID を1つも書いてない場合に起きると考えられます。

      以下の式で試してもらえないでしょうか。1,2行目には他に何も書かないでください。
      また、コピーして貼り付けるとダブルクォーテーションがおかしくなるかもしれないので、
      その場合はダブルクォーテーションだけ入力しなおしてください。「”」は4つあります。
      B1の式
      =transpose(if(counta(config!A1:A)>1, indirect(concat(“config!A2:B”,counta(config!A1:A))), “”))

      1. 森島様

        トリガー設定も問題なくイケました。

        まだまだ日本だとクリプトやNFT関連(特にBCG)は、マイノリティですが、株価が今年は悲惨なので、株オンリー族が6月あたりから徐々にこちらに流れてくると予想しています。

        また来年度から税制も整い始め、さらに盛り上がりそうです。

        その時こそ、この記事の素晴らしさが世に広まり、検索需要爆増→SEO無双になれば良いな〜なんてことを思っています。

        冗談抜きで、自分の中では今年度MVPコンテンツ間違いなしです。

        はい「ダブルクォーテーション」などは気をつけているのですが、新たな関数を入れても何も表示されませんでした。

        前回はERRORでしたが、今回は空欄のままです。
        手打ちでも対応できているため、十分満足しているといえばそうなのですが、ここまできたら「ID・シンボル」の自動化も味わいたい所存です。笑

        しかし、森島様のスプシで確認できないとなると、私の方の何かが原因なんでしょうね。
        もしなにか分かりましたら、その時はよろしくお願いします。

        今回は本当にありがとうございました。

        1. Author

          > 冗談抜きで、自分の中では今年度MVPコンテンツ間違いなしです。
          ありがとうございます!そう言っていただけるとやる気が出ますね。

          > はい「ダブルクォーテーション」などは気をつけているのですが、
          > 新たな関数を入れても何も表示されませんでした。
          適当なセルに以下の式を入力した場合、何が返ってきますか?
          =counta(config!A1:A)
          config シートA列の内、何かしら値が入っているセルの数が返ってくるので
          入力した ID の数 + 1 が表示されるはずです。

          1. 森島様

            入力してみたところ「1」のみ表示されます。

            私のconfigシート内の状況は

            A1「ID」テキスト
            B1「銘柄」テキスト
            A2以降の列「ID番号」
            B2「=arrayformula(if(isblank(A2:A), “”, vlookup(A2:A,’crypto-map’!$A:$C,3)))」←によるシンボル取得はできています。

            ご確認よろしくお願い致します。

          2. Author

            A2以降に何らかの値があるにもかかわらず counta 式が1になるのは不思議ですね。

            シートの名前は “config” で一致していますか?
            counta に存在しないシートを指定すると 1 が返ってきたので、その可能性があります。

          3. 森島様

            なぜか今もう一度やってみたらできました。
            お手数おかけして申し訳ありません。

            これで理想のシートになり、大変満足しております。
            この度は本当にありがとうございました。

            陰ながら森島様のご活躍を応援しております。

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です