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) {

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");
  const values = range.getValues();
  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
      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.


  • 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.