Skip to content

Instantly share code, notes, and snippets.

@denblackstache
Created January 9, 2023 12:53
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save denblackstache/a88934518f4a2df33f2c949f167ee9a4 to your computer and use it in GitHub Desktop.
Save denblackstache/a88934518f4a2df33f2c949f167ee9a4 to your computer and use it in GitHub Desktop.
NBG Fetch Currency AppScript
/**
* Fetch currency rate from
* @see https://nbg.gov.ge/en/monetary-policy/currency
* @param date Date
* @param currency USD, EUR or GBP
*/
function fetchGelCurrencyRate(date, currency) {
Logger.log(`fetchGelCurrencyRate with date: ${date}, currency: ${currency}`);
const toISODateString = date => `${date.getFullYear()}-${(date.getMonth() + 1).toString().padStart(2, "0")}-${date.getDate().toString().padStart(2, "0")}`;
const url = `https://nbg.gov.ge/gw/api/ct/monetarypolicy/currencies/en/json/?currencies=${currency.toUpperCase()}&date=${toISODateString(date)}`;
Logger.log(`Making request to: ${url}`);
const response = UrlFetchApp.fetch(url);
const json = JSON.parse(response.getContentText());
Logger.log(`Result: ${response.getContentText()}`);
return { rate: json[0].currencies[0].rate, validFrom: json[0].currencies[0].validFromDate };
}
function testFetch() {
fetchGelCurrencyRate(new Date(), 'USD');
}
/**
* The event handler triggered when editing the spreadsheet
* @param {Event} e The onEdit event.
* @see https://developers.google.com/apps-script/guides/triggers#onedite
* @see https://developers.google.com/apps-script/guides/triggers/installable#g_suite_application_triggers
* @see https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events
* @see https://developers.google.com/apps-script/reference/spreadsheet
*/
function onCellEdit(e) {
const debugEvent = {
authMode: e.authMode,
range: e.range.getA1Notation(),
source: e.source.getId(),
user: e.user,
value: e.value,
oldValue: e. oldValue
}
console.log({message: 'onCellEdit() Event Object', eventObject: debugEvent});
const spreadsheet = e.source;
const sheet = spreadsheet.getSheets()[0];
const columnLetter = e.range.getA1Notation()[0];
if (!e.value || !['B', 'C'].includes(columnLetter)) {
return;
}
const rowPosition = e.range.getRow();
const dateRange = sheet.getRange("B" + rowPosition);
const currencyRange = sheet.getRange("C" + rowPosition);
console.log('rowPosition', rowPosition);
console.log('dateRange', dateRange.getA1Notation());
console.log('currencyRange', currencyRange.getA1Notation());
const currencyRate = fetchGelCurrencyRate(dateRange.getValue(), currencyRange.getValue());
const validFromRange = sheet.getRange("D" + rowPosition);
const rateRange = sheet.getRange("E" + rowPosition);
Logger.log(`Currency Rate: ${currencyRate.rate}, from ${currencyRate.validFrom}`);
validFromRange.setValue(new Date(currencyRate.validFrom));
rateRange.setValue(currencyRate.rate);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment