仮想通貨の価格データを日ごとに記録する – 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 を書くだけです。

セル	セルの値	備考
A1	ID	ただのカラム名
A2, A3, …	欲しい仮想通貨を crypto-map から探し ID を書く
B1	Symbol	ただのカラム名
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 弾として仮想通貨の価格を日ごとに記録するスクリプトを紹介しました。トリガー機能を使えば、毎日自動で価格を記録してくれます。

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