GASでSubscan(DOT)のステーキング履歴(CSV)からクリプタクトのカスタムファイル形式でシートに書き出す

背景

私は暗号資産の取引履歴をクリプタクトの損益計算サービスで管理しています。
クリプタクトでは取引所が作成する取引履歴ファイルをアップロードすることで自動で損益計算をすることが可能ですが、Walletを介したステーキングは取引履歴のアップロードに対応していません。(一部を除く)¹
この場合、個別にカスタムファイル(CSV)を作成することで対応可能ですが、指定のフォーマットで作成する必要があり手間がかかります。
これまでカスタムファイルはスプレッドシートから手動で作成していましたが、作業を効率化するためGASで作成することを考えました。

作成したもの

Polkadot(DOT)のエクスプローラーであるSubscanから取得したDOTのステーキング報酬データ(CSV)を読み込み、クリプタクトのカスタムファイル形式に整形し、指定のシートに書き出すGASを作成しました。

仕様

• スプレッドシートのメニューに「ファイル読込み」と「データ書出し」を追加します。

• 「ファイル読込み」から「CSVファイル読込み」を選択するとファイル読込みフォームを表示します。ローカルPCのCSVファイルを選択して読込むとスプレッドシートに表示されます。

• CSVファイルはSubscan(DOT)の「Reward&Slash」からDLするファイルとし、文字コードは「UTF-8」とします。

• 「データ書出し」から「カスタムシート書出し」を選択すると、スプレッドシート上に入力フォームを表示して書出したい件数を入力します。入力する値は正の数とし、0、負の数、CSVファイルの行数より大きい値の場合はエラーを表示し、処理を中止します。

• 書き出しデータはクリプタクトのフォーマット仕様に合わせます。
  カスタムファイルの作成方法

• その他、個別の処理として「文字列の結合」、日時データを「UTC日本時間」に調整します。

• 使い方はソースコードのREADME.mdを参照してください。

注意事項

• 対象通貨はDOTですが、Subscanに対応した通貨であれば同様に対応可能かと思いますが未確認です。

• Subscanやクリプタクトのデータフォーマットは変わることがありますので、利用する際は自己責任でお願いします。

ソースコード

以下、処理する上でのポイントを記載します。

• ファイル読込み処理は以下の記事を参考にさせていただきました。
  Googl Apps Scriptを使ってファイル読み込みからスプレッドシートに表示

• ファイル読込みはFileReaderオブジェクトからファイルのテキストを取得し、FileOpenAction.gsのreadTextGASFileOpen()に渡します。

• readTextGASFileOpen.gsではparseCsv()で配列に変換して読み込みます。
  HTML上でファイルのテキストを表示するためreadAsTextに文字コードを指定しています。

• 文字コード変換はencode.jsを使用することで変換可能ですが、うまく変換できなかったためUTF-8固定としています。

FileOpen.html

  <script type="text/javascript">
    function OnButtonClick() {
      var fileRef = document.getElementById("File1");
      var outFrame = document.getElementById("output");

      if (1 <= fileRef.files.length) {
        // FileReaderオブジェクト作成
        var reader = new FileReader();
        // obloadイベントハンドラ(読み込み操作が正常に完了時に実行)
        reader.onload = function (theFile) {
          // イベントオブジェクトからファイルのテキストを取得
          var outhtml = theFile.target.result;
          // 文字コードを変換する際はencoding.jsを使用すればよい
          // encoding.jsはcdnjs.comで指定可能
          // gs側にcsvの配列データを渡しスプレッドシートに書き込む
          google.script.run.readTextGASFileOpen(outhtml);
          // ダイアログの処理なので重要性は低い
          outhtml = outhtml.replace(/\r\n/g, "<br/>");
          outFrame.innerHTML = outhtml;
        }
        // 変換結果表示
        reader.readAsText(fileRef.files[0], "utf-8");
      }
     
    }
  </script>

FileOpenAction.gs

/**
 * ファイル読込み用HTML作成
 * @param {number} data - 読込みデータ
 * 
 * FileOpen.html内のJSに指定して呼び出す
 */
function readTextGASFileOpen(data){
  // 書き出し前にシートを全てクリアする
  tradingHistSheetClear();
  var thsh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("tradingHist");
  var csv = Utilities.parseCsv(data);
  // セルA1からCSVの内容を書き込んでいく
  thsh.getRange(1,1,csv.length,csv[0].length).setValues(csv);
  var lastRow = thsh.getLastRow();
  // 項目行を除くため行数調整する(lastRow - 1 ;だとNaNになるためデクリメントすること)
  lastRow--;
  // Volume列では小数点以下のデータを含む場合、数値フォーマットが「指数」に設定されることがあるため、
  // 書き込み後に数値フォーマットを「自動」に設定する
  thsh.getRange(5,2,lastRow-1).setNumberFormat("general");
  // Uiクラスを使用して処理終了メッセージダイアログ(タイトルとOKボタン）を表示
  var ui = SpreadsheetApp.getUi();
  // ダイアログタイトル、メッセージと「OK」ボタンを表示(改行するときは「\n」を追加する)
  var title = "読み込み成功"
  var message = "tradingHistシートに" + lastRow + "件読み込みました。"
  ui.alert(title, message, ui.ButtonSet.OK);
}

• 書き出し処理はテキストボックスで入力された値をWriteDataAction.gsのcheckWriteNum()に渡して処理します。
• WriteDataAction.gsではDateオブジェクトでUTC日本時間(+9:00)に調整し、Utilities.formatDate()で指定の文字列に変換しています。時間調整が不要の場合はsetHours()を「0」にしてください。

WriteData.html

  <script type="text/javascript">
    function OnButtonClick() {
      var textbox = document.getElementById("rowParam");
      var inputValue = textbox.value;
      google.script.run.checkWriteNum(inputValue);
    }
  </script>

WriteDataAction.gs

  // lastRowまで繰り返す
  for(var i = 2; i <= lastRow ; i++){
    // Date取得
    var rawDate = thsh.getRange(i, 3).getValue();
    // UTC日本時間(+9:00)に調整
    var addDate = new Date(rawDate.setHours(rawDate.getHours() + 9));
    // フォーマット変更
    var formatedDate = Utilities.formatDate(addDate, "JST","yyyy/MM/dd HH:mm:ss");
    // Timestamp列に追加(フォーマットに合わせるため文字列指定)
    cdsh.getRange(i,1).setValue("'"+formatedDate);

    // Valueを取得
    var stkValue = thsh.getRange(i, 6).getValue()
    // Volume列に追加
    cdsh.getRange(i, 5).setValue(stkValue);

    // Event Indexを取得
    var eventIndex = thsh.getRange(i, 1).getValue()
    // Eraを取得
    var era = thsh.getRange(i, 2).getValue()
    // 履歴を区別するためeventIndexとeraを結合した値をcomment列に追加
    cdsh.getRange(i, 10).setValue(eventIndex + " " + era);
  }
  // customDataシートの最終行取得
  var cdshLastRow = cdsh.getLastRow();
  // シート内でソートしたいセル範囲を指定
  var sortData = cdsh.getRange(2, 1, cdshLastRow - 1, 10);
  // 列Aを基準に昇順でソートする
  sortData.sort({column: 1, ascending: true});

最後に

• Subscan(DOT)のステーキング報酬データ(CSV)を読み込み、クリプタクトのカスタムファイル形式に整形し、指定のシートに書き出すGASを紹介しました。
• コードは個人用のため固定処理が多いですが、集計作業は数が増えると時間もかかるので少しでも作業の効率化に繋がればと思います。
• GASはトリガーの設定やアプリとしてデプロイも可能なためファイル取得処理も自動化できるとより効率化につながると思います。この辺りはSubscan APIを使用すればできないかと考えています。

参考

• Googl Apps Scriptを使ってファイル読み込みからスプレッドシートに表示

1. 2021年末にDeFi向け取引にも対応されました。(DeFi連携システムのプレスリリース) ↩