Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active July 17, 2024 20:24
Show Gist options
  • Save tanaikech/67e0cd1f5ba4946aa050963a0720ed5f to your computer and use it in GitHub Desktop.
Save tanaikech/67e0cd1f5ba4946aa050963a0720ed5f to your computer and use it in GitHub Desktop.
Checking Exchange Rate using GOOGLEFINANCE with Google Apps Script

Checking Exchange Rate using GOOGLEFINANCE with Google Apps Script

This is a sample script for checking the exchange rate using GOOGLEFINANCE with Google Apps Script.

Recently, I have published a report of "Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script". Ref In this post, I would like to introduce a sample script for checking the exchange rate using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Spreadsheet.

// 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 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) { // 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>`,
    });
  }
}
  • In this script, when the function of checkCurrency is run, the current value of exchange rate is retrieved as a fixed value without using the formula. When the value is over threshold, an email is sent.

  • When this script is used, the top chart can be created in Google Spreadsheet.

Note

  • This sample script uses this situation. So, when this specification is changed by Google side, this script might not be able to be used. Please be careful about this.

Reference

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment