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

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

## 内容
これまでは、Google SpreadsheetのGOOGLEFINANCEで取得した値はGoogle Apps Scriptで読み込むことができない仕様でしたが、最近、これが変更されたようで、GASで値を取得できるようになりました。[Ref](https://tanaikech.github.io/2022/05/18/report-obtaining-values-from-googlefinance-using-google-apps-script/) これにより、例えば、Google SpreadsheetのGOOGLEFINANCEとGoogle Apps Scriptを使ってUSD/JPYの為替レートの推移を記録することが可能になりました。この投稿では、サンプルスクリプトを使ってこの方法を紹介させていただきます。

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

```javascript
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へアペンドされます。これをグラフ化したサンプルは下記のようになります。

![fig1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/133383/0b283e91-d450-daec-72a7-acabb4670ed8.png)


## Note
- この場合、`const ticker = "CURRENCY:USDJPY";`を変更することで他のデータも取得することができます。

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


この記事の英語版は[こちら](https://tanaikech.github.io/2022/07/28/checking-exchange-rate-using-googlefinance-with-google-apps-script/)で御覧いただけます。