Google Sheets

Examples

Setup

1. 1

   Open Apps Script

   In your spreadsheet, go to Extensions → Apps Script. A code editor opens in a new tab.

2. 2

   Paste the script

   Select everything in the default Code.gs, replace it with the code below, and click Save.

   var RATE_API_URL = 'https://rate-api.com/api/v1';
   
   function onOpen() {
     SpreadsheetApp.getUi().createMenu('Rate-API').addItem('Set API key', 'rateApiSetKey').addToUi();
   }
   
   function rateApiSetKey() {
     var ui = SpreadsheetApp.getUi();
     var r = ui.prompt('Rate-API', 'Paste your API key (rate-api.com dashboard):', ui.ButtonSet.OK_CANCEL);
     if (r.getSelectedButton() === ui.Button.OK) {
       PropertiesService.getDocumentProperties().setProperty('RATE_API_KEY', r.getResponseText().trim());
       ui.alert('Saved. Try  =RATEAPI("USD","EUR")');
     }
   }
   
   function rateApiLatest_(base) {
     base = String(base || 'USD').toUpperCase().trim();
     var cache = CacheService.getScriptCache(), ck = 'ra_' + base, hit = cache.get(ck);
     if (hit) return JSON.parse(hit);
     var key = PropertiesService.getDocumentProperties().getProperty('RATE_API_KEY');
     if (!key) throw new Error('Set your API key: Rate-API menu > Set API key.');
     var resp = UrlFetchApp.fetch(RATE_API_URL + '/' + encodeURIComponent(key) + '/latest?base=' + base,
       { muteHttpExceptions: true, headers: { Accept: 'application/json' } });
     var data = JSON.parse(resp.getContentText() || '{}');
     if (resp.getResponseCode() !== 200 || !data.success) {
       throw new Error((data.error && data.error.message) || ('Rate-API error ' + resp.getResponseCode()));
     }
     cache.put(ck, JSON.stringify(data), 600);
     return data;
   }
   
   function rateApiOf_(data, to) {
     to = String(to).toUpperCase().trim();
     if (to === data.base) return 1;
     var rate = (data.rates || {})[to];
     if (rate === undefined) throw new Error('No rate for ' + to + ' from ' + data.base + '.');
     return rate;
   }
   
   /** @customfunction */
   function RATEAPI(from, to) {
     var data = rateApiLatest_(from || 'USD');
     if (to == null || to === '') {
       var rates = data.rates || {}, out = [['Currency', 'Rate']];
       Object.keys(rates).sort().forEach(function (c) { out.push([c, rates[c]]); });
       return out;
     }
     return rateApiOf_(data, to);
   }
   
   /** @customfunction */
   function RATECONVERT(amount, from, to) {
     return Number(amount) * rateApiOf_(rateApiLatest_(from || 'USD'), to);
   }

3. 3

   Set your API key

   Reload the spreadsheet. A new Rate-API menu appears - click Rate-API → Set API key and paste your key (the first run asks you to approve permissions). Get a free key from your dashboard.

4. 4

   Use it in any cell

   Type a formula and the rate appears. =RATEAPI("USD") with no second argument spills the full table of every currency.

   =RATEAPI("USD","EUR")
   =RATECONVERT(100,"USD","AMD")