Google SpreadsheetのGOOGLEFINANCEとGoogle Apps Scriptを使って定期的に米ドル/円（USD/JPY）の為替レートの推移を記録する #GoogleAppsScript

概要

この投稿では、Google SpreadsheetのGOOGLEFINANCEとGoogle Apps Scriptを使って定期的に米ドル/円（USD/JPY）の為替レートの推移を記録する方法について紹介させていただきます。オプションとして、指定した値に到達した際にメールで知らせます。

内容

これまでは、Google SpreadsheetのGOOGLEFINANCEで取得した値はGoogle Apps Scriptで読み込むことができない仕様でしたが、最近、これが変更されたようで、GASで値を取得できるようになりました。Ref これにより、例えば、Google SpreadsheetのGOOGLEFINANCEとGoogle Apps Scriptを使ってUSD/JPYの為替レートの推移を記録することが可能になりました。この投稿では、サンプルスクリプトを使ってこの方法を紹介させていただきます。

サンプルスクリプト

下記のスクリプトを使用する際は、スクリプトをGoogle Spreadsheetのスクリプトエディタに貼り付けてください。そして、thresholdとemailを設定してください。その後、スクリプトエディタからsetTriggerを実行してください。これにより、時間トリガーにより10分間隔でcheckCurrencyが実行されます。そして、値がスプレッドシートの最初のページの列A, Bに記録されます。

function clearProperty() {
  PropertiesService.getScriptProperties().deleteProperty("key");
}

// When this script is run, a trigger for executing "checkCurrency" function is installed.
function setTrigger() {
  const functionName = "checkCurrency";
  const trigger = ScriptApp.getScriptTriggers().find(t => t.getHandlerFunction() == functionName);
  if (trigger) {
    ScriptApp.deleteTrigger(trigger);
  }
  ScriptApp.newTrigger(functionName).timeBased().everyMinutes(10).create();
}

function checkCurrency() {
  const threshold = ###; // Please set the the threshold value you want to check.
  const ticker = "CURRENCY:USDJPY"; // Please set ticker you want to check.
  const email = "###"; // Please set the email address you want to notice.

  const p = PropertiesService.getScriptProperties();
  const key = p.getProperty("key") || "";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const range = sheet.getRange(sheet.getLastRow() + 1, 1, 1, 2);
  range.setFormulas([["=NOW()", `=GOOGLEFINANCE("${ticker}")`]]);
  range.offset(0, 0, 1, 1).setNumberFormat("yyyy/MM/dd H:mm:ss");
  SpreadsheetApp.flush();
  const values = range.getValues();
  range.setValues(values);
  if (values[0][1] > threshold && key != "sent") { // Or if you want to check when the current value is less than the threshold, please modify this to values[0][1] < threshold
    MailApp.sendEmail({
      to: email,
      subject: `Report: Value is over the threshold (${threshold})`,
      htmlBody: `<p>Current value is ${values[0][1].toFixed(2)}.</p><p><a href="${ss.getUrl()}">Open Spreadsheet</a>`,
    });
    p.setProperty("key", "sent");
  }
}

このスクリプトでは、値がthresholdに到達すると、メールが送信されます。このとき、values[0][1] > thresholdの場合は、現在の値が指定した値よりも大きくなった場合にメールが送信されます。また、values[0][1] < thresholdのように設定すると、現在の値が指定した値よりも小さくなった場合にメールが送信されます。これはシンプルなサンプルのため、さらに細かく指定したい場合はスクリプトを変更する必要があります。
このスクリプトでは、一度メールが送られると、その後はメールは送られませんので、これをリセットする場合はclearProperty()を実行してください。
取得したデータは、日付、値が列A, Bへアペンドされます。これをグラフ化したサンプルは下記のようになります。

Note

この場合、const ticker = "CURRENCY:USDJPY";を変更することで他のデータも取得することができます。
今はまだGoogle SpreadsheetのGOOGLEFINANCEで取得した値をGoogle Apps Scriptを使って読み取ることができますが、この状況が今後も継続されるかどうかは不明です。突然の仕様変更で再び読み取りができなくなる可能性もありますのでご注意ください。

この記事の英語版はこちらで御覧いただけます。