curs.md google function

README.md

Google Spreadsheet function that shows rates from curs.md

Script was made for learning purposes
Please read terms curs.md: https://www.curs.md/ro/terms

How to add the function: https://developers.google.com/apps-script/guides/sheets
Rates are taken from: https://www.curs.md/ro/json_convertor_provider/default

Function signature

CURSMD(date: Date, bank?: String, currency?: String, debug?: Boolean)

Examples

curs-md.js

/**
 * Get curs.md rates
 * Script was made only for learning purposes
 * Please read terms https://www.curs.md/ro/terms
 * @param {Date|Date[][]} date The date of the rate
 * @param {string} [bank="bnm"] The desired bank
 * @param {string} [currency="EUR"] The desired currency
 * @param {boolean} [debug=false] Show some debug messages
 * @return The currency rate from bank at that date
 * @customfunction
*/
function CURSMD(date = new Date(), bank = 'bnm', currency = 'EUR', debug = false) {
	// Arguments validation
	let invalids = ['#NAME?', '']
	if (invalids.includes(date)) date = new Date()
	if (invalids.includes(bank)) bank = 'bnm'
	if (invalids.includes(currency)) currency = 'EUR'
	if (typeof debug !== 'boolean') debug = false

	if (!Array.isArray(date) && !isDate_(date)) throw new Error('Invalid date')
	if (!Array.isArray(date) && isTomorrow_(date)) throw new Error('Future date')
	if (typeof bank !== 'string') throw new Error('Invalid bank')
	if (typeof currency !== 'string') throw new Error('Invalid currency')

	bank = bank.toLowerCase()
	currency = currency.toUpperCase()

	if (!Array.isArray(date)) {
		let result = getRateResult_(date, bank, currency, debug)

		if (result.status === 'rejected') {
			throw new Error(result.reason)
		} else {
			return result.value
		}
	}

	return date.map((datesRow, rowIndex) => datesRow.map((date, columnIndex) => {
		let result = getRateResult_(date, bank, currency, debug)

		if (result.status === 'rejected') {
			let cellIndex = `[${rowIndex + 1}:${columnIndex + 1}]`
			return `${result.reason} ${cellIndex}`
		} else {
			return result.value
		}
	}))
}

let isDate_ = theDate => theDate instanceof Date && !isNaN(theDate)
let isTomorrow_ = date => {
	let tomorrow = new Date()
	tomorrow.setDate(tomorrow.getDate() + 1)
	return date.getTime() > tomorrow.getTime()
}
let reject_ = reason => ({ status: 'rejected', reason })
let fulfill_ = value => ({ status: 'fulfilled', value })
let getCacheKey_ = dateString => `curs.md[${dateString}]`
let getDateString_ = date => {
	let year = String(date.getFullYear()).padStart(4, '0')
	let month = String(date.getMonth() + 1).padStart(2, '0')
	let day = String(date.getDate()).padStart(2, '0')
	return `${year}-${month}-${day}`
}

/** @param {Date} date */
function getData_(date) {
	if (!isDate_(date)) return reject_('Invalid date')
	if (isTomorrow_(date)) return reject_('Future date')

	let scriptCache = CacheService.getScriptCache()
	let dateString = getDateString_(date)
	let cacheKey = getCacheKey_(dateString)
	let cacheResult = scriptCache.get(cacheKey)

	if (cacheResult) {
		Logger.log(`[${dateString}] Cache hit`)
		try {
			let data = JSON.parse(cacheResult)
			return fulfill_({ data, cache: true })
		} catch (e) {
			return reject_('Failed to fetch')
		}
	} else {
		Logger.log(`[${dateString}] Cache miss`)
	}

	let url = 'https://www.curs.md/ro/json_convertor_provider/default'
	let finalUrl = `${url}?ConvDate=${dateString}`
	console.time(`[${dateString}] Fetching ${finalUrl}`)
	let response = UrlFetchApp.fetch(finalUrl, { muteHttpExceptions: true });
	console.timeEnd(`[${dateString}] Fetching ${finalUrl}`)

	if (response.getResponseCode() !== 200) {
		return reject_('Failed to fetch')
	}

	try {
		let text = response.getContentText()
		let data = JSON.parse(text)
		scriptCache.put(cacheKey, text)
		return fulfill_({ data, cache: false })
	} catch (e) {
		return reject_('Failed to fetch')
	}
}

function getRateResult_(date, bank, currency, debug = false) {
	let lock = LockService.getScriptLock()

	lock.tryLock(10000)
	let dataResult = getData_(date)
	lock.releaseLock()

	if (dataResult.status === 'rejected') {
		return dataResult
	} else {
		let { data, cache } = dataResult.value
		let theBank = data?.[bank]
		if (theBank === undefined) return reject_('Unknown bank')
		let theCurrency = theBank?.rates?.[currency]
		if (theCurrency === undefined) return reject_('Unknown currency')
		let rate = theCurrency.sell
		if (rate === undefined) return reject_('Unknown rate')
		return fulfill_(cache && debug ? `[CACHE]: ${rate}` : rate)
	}
}

function TEST() {
	let datesRows = [[
		new Date(2023, 2, 1),
		new Date(2022, 7, 16),
		"invalid",
		new Date(2022, 7, 19),
		new Date(2022, 7, 20),
	]]

	let scriptCache = CacheService.getScriptCache()
	datesRows.forEach(datesRow => datesRow.map(date => {
		if (!isDate_(date)) return
		let dateString = getDateString_(date)
		let cacheKey = getCacheKey_(dateString)
		scriptCache.remove(cacheKey)
	}))

	Logger.log('- Results 1 -------------------')
	let results1 = CURSMD(datesRows)
	Logger.log('- Reults 2 ------------------')
	let results2 = CURSMD(datesRows)
	Logger.log('-------------------')
	Logger.log(results1)
	Logger.log(results2)
	Logger.log(`Equal: ${JSON.stringify(results1) === JSON.stringify(results2)}`)
}