Import real time exchange rates currency into GOOGLE APPS spreadsheet

If you are doing business analysis or calculations in your Google Apps spreadsheets then you probably need some kind of currencies converter for doing all sorts of tasks…

• Convert your revenue from Dollars to Euros
• Currency Risk analysis
• Managing your expenses in one or multiple exchange rates
• ….

One might guess that Google supports Exchange rates in their spreadsheets “AS-IS” but this is not the case. Also, their labs service Google Finance is deprecated so we must get our hands dirty and take the JavaScript approach for populating our spreadsheet with REAL-TIME currency quotes.

The first thing is to find the source of our data and for this use-case, we decided to give OpenSource Exchange rates JSON data a green light. This service returns JSON formatted dates for all currencies and please note that the base currency is the US Dollar. If you click here you will see the example of JSON data…

Next, we need to create a script for our spreadsheet and we can do this task if we click on “Tools” -> “Script Manager”. FYI, scripting in Google spreadsheet is quite powerful and you can scan through entire documentation on official Google apps help portal

Next, we need to set triggers on our new script to fire up when we open the spreadsheet! This step is performed through the user interface and the full documentation can also be found on Google help portal

When you’re done setting up triggers, navigate to the script editor section and write up JavaScript that is exactly the same as this one… :))

function myFunction()
{
  var response = UrlFetchApp.fetch("http://openexchangerates.org/latest.json");

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

  var object = Utilities.jsonParse(response.getContentText());

  var objRates = object["rates"];

  for (var i in objRates)
  {
    if(i="HRK")
    {
      sheet.getRange(2, 1).setValue(objRates[i]);
    }

    if(i="EUR")
    {
      sheet.getRange(2, 2).setValue(objRates[i]);
    }
  }
}

Just change the “SheetName” to the name of the sheet where you want your data to show. Note that this script takes two currencies (Euro and Croatian Kuna) and places them in a given row/column layout. If you need some other currencies just add another IF statement and set some different range of the cell!

Blog update (14th July 2012): As the user WittZi stated … it is very easy right now to import currencies into the spreadsheet. Just use =GoogleFinance(“CURRENCY:EURUSD”) to get Euros to USD or something similar.