Making a custom Google Sheets function for currency conversion

Updated: 26/01/2025 Tags: #finance #LLM

I use google sheets for my personal finance work and require buying rates for foreign currencies. At first I just had a constant set, then used the Google Finance function but that value is way off.

Image 1: Post thumbnail showing the function in use

Making our own function

Google Apps Script is what we need to make this work. Using Apps Script it’s easy to write a custom function for Google Sheets.

Task 1: Data Source

This was easy, After browsing the official websites currency convert page, I saw this API call heading out

https://www.combank.lk/api/exchange-rates/

and the schema was simple and clear, this is all we need to make this happen.

[
  {
    "id": 1,
    "excode": "USD",
    "description": "US DOLLARS",
    "code": "1",
    "cheque_buying_rate": 291.89164,
    "cheque_selling_rate": 302.5,
    "currency_buying_rate": 292.9534,
    "currency_selling_rate": 302.5,
    "telegraphic_transfers_buying_rate": 294.5,
    "telegraphic_transfers_selling_rate": 302.5,
    "sync_at": "2025-01-24",
    "created_at": "2020-12-16 11:37:31",
    "updated_at": "2025-01-26 08:30:03"
  },
  ...
]

Task 2: Writing the function

To make a custom function on Google sheets, Do the following,

Go/Make to the Google Sheet.
In the menu bar click Extensions then App Script
Write the code in the given code.gs file.
Click the Save icon on the tool bar.

Afterwards the function is available on the Sheet.

The code was mostly generated using an LLM, it was functional. That was easy! I added some tweaks to make it a bit nicer (caching, docs, etc.)

/**
 * Get the latest currency buying value for Commercial Bank
 * @param {currencyCode} ISO currency code
 * @return converted value
 * @customfunction
*/
function COMBUYING(currencyCode) {

  const cacheKey = "exchangeRates";
  const apiUrl = "https://www.combank.lk/api/exchange-rates/";
  const cache = CacheService.getScriptCache();

  var exchangeRates = {};
  try {
    // Check if data is already cached
    let data = cache.get(cacheKey);
    if (!data) {
      // Fetch the API response if not cached
      const response = UrlFetchApp.fetch(apiUrl, { method: "get", muteHttpExceptions: true });
      data = response.getContentText();

      // Cache the data for 10 minutes
      cache.put(cacheKey, data, 600);
    }
    exchangeRates = JSON.parse(data);
  } catch (e) {
    throw Error(`Unable to fetch exhcange rates ${e}`)
  }

  // Find the currency by its code
  const currency = exchangeRates.find((item) => item.excode === currencyCode.toUpperCase());

  if (!currency) {
    throw SyntaxError(`Currency "${currencyCode}" not found`)
  }
  
  return currency.currency_buying_rate;
}

Task 3: Use it!

Now with the function name, we’re able to fetch the latest buying rate for any currency.

=COMBUYING("EUR")

Conclusion

Thanks to a prior run in with Google App Script, I knew of this possibility. ChatGPT is great for these kind of quick hacks to get something started.

It’s convenient as the rates given by Google Finance and actual buying rates differ. Comparison of outputs on the day of writing,

Function	Result
`GOOGLEFINANCE("CURRENCY:EURLKR")`	312.75
`COMBUYING("EUR")`	303.56077

Table 1