Whether you work in banking, finance, trade currencies, or just work for a global company, chances are you’ve had to perform a currency calculation in your spreadsheet.

While it’s easy enough to go to XE.com, OANDA, or Google to perform a single currency exchange lookup for the day, that doesn’t scale nor does it update the next time you open your Google Sheet.

If you use Google Sheets, you’re in luck. You can write a little bit of javascript code to pull from an online API each time the sheet opens. Here are step-by-step instructions on how you can automate the currency exchange lookups each time you open your spreadsheet.

Creating your currency converter

1. Create a new Google Sheet
2. Change the name of the default tab from ‘Sheet1’ to ‘Conversion’
3. Copy the contents of my Conversion Template into your Google Sheet and onto the Conversion tab (this code works by referring to the name of the tab so don’t rename it unless you plan to update the code)
   1. You’re also welcome to make your own tables of currencies and add more currencies or remove ones you don’t need.
4. Navigate to Open Exchange Rates to sign up for a free account. Once you sign up it should automatically log you into your account. If it doesn’t make sure to login.
5. Click App IDs in the left-hand navigation bar. An App ID should be displayed. Copy this App ID to your clipboard.
   1. You are welcome to click the pencil icon to edit the name of the App ID to “Google Sheets” or whatever you prefer but this is not necessary.
6. Back in your Google Sheet click on Tools and then Script editor
7. Delete all the default code that appears and replace it with the code block below, ensuring that you replace YOURAPPID on line 6 with the App ID you copied from Open Exchange Rates.

function onOpen() {
  exchangeRates();
}

function exchangeRates() {
  var response = UrlFetchApp.fetch("https://openexchangerates.org/api/latest.json?app_id=YOURAPPID");

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Conversion");

  var object = JSON.parse(response);
  var objRates = object["rates"];

  for (var i in objRates) {
     if (i="CAD") {
      var conValA = 1 / objRates[i];
      var conValB = objRates[i];
      sheet.getRange(2, 2).setValue(conValA);
      sheet.getRange(2, 5).setValue(conValB);
    }   
    if (i="GBP") {
      var conValA = 1 / objRates[i];
      var conValB = objRates[i];
      sheet.getRange(3, 2).setValue(conValA);
      sheet.getRange(3, 5).setValue(conValB);
    }
    if (i="EUR") {
      var conValA = 1 / objRates[i];
      var conValB = objRates[i];
      sheet.getRange(4, 2).setValue(conValA);
      sheet.getRange(4, 5).setValue(conValB);
    }
    if (i="AUD") {
      var conValA = 1 / objRates[i];
      var conValB = objRates[i];
      sheet.getRange(5, 2).setValue(conValA);
      sheet.getRange(5, 5).setValue(conValB);
    }
    if (i="INR") {
      var conValA = 1 / objRates[i];
      var conValB = objRates[i];
      sheet.getRange(6, 2).setValue(conValA);
      sheet.getRange(6, 5).setValue(conValB);
    }
    if (i="HUF") {
      var conValA = 1 / objRates[i];
      var conValB = objRates[i];
      sheet.getRange(7, 2).setValue(conValA);
      sheet.getRange(7, 5).setValue(conValB);
    }
    if (i="CHF") {
      var conValA = 1 / objRates[i];
      var conValB = objRates[i];
      sheet.getRange(8, 2).setValue(conValA);
      sheet.getRange(8, 5).setValue(conValB);
    }
    if (i="NZD") {
      var conValA = 1 / objRates[i];
      var conValB = objRates[i];
      sheet.getRange(9, 2).setValue(conValA);
      sheet.getRange(9, 5).setValue(conValB);
    }
  }
}

8. Click the Save Project button and name your Project “Conversion” or whatever you prefer. The name of this is irrelevant. Your project should look like this.

9. Click the Run button to test the code. This will update all the data in the Conversion tab.
10. The last step is to make sure that this code runs each time the spreadsheet is open. To do that click the clock icon on the left below the code icon you are currently on. This is the Triggers section.
11. There will be no results found on the Triggers section as none have been created yet. Click the “create a new trigger” link to create a new one.

12. On the popup window that appears choose the defaults and click Save. This will open a new window asking you to confirm that you are allowing your Google account access to this project.

That’s it! Feel free to play around with adding and removing different currencies. Copy this tab to any other Google Sheet in your Google Workspace so that you always have the most recent exchange rates. Keep in mind that each time you copy the Conversion tab you created to another Google Sheet you will also have to re-create and re-authorize the trigger. You may also have to copy the code over too as the code is part of the Google Sheet and not the tab itself.

Let me know how it works!

Note: Technically this will also work for Microsoft Excel Online but you may need to modify the code slightly. I will work on another post that shows how to do this in Excel. I’ll also work on a video for this.