function getDate(decrease) { var date = new Date(); if(decrease && decrease > 0) { date = new Date(date); date.setDate(date.getDate() - decrease); } var yyyy = date.getFullYear().toString(), dd = date.getDate().toString(), mm = (date.getMonth() + 1).toString(); if(dd.length == 1) dd = '0' + dd; if(mm.length == 1) mm = '0' + mm; return yyyy + mm + dd; } var baseURL = 'https://spib.wooribank.com/pib/jcc?withyou=ENENG0358&__ID=c008822&BAS_DT='; function getExchangeHash() { var html = ''; var cache = CacheService.getPublicCache(); var cached = cache.get('exchange-rate-woori'); if (cached != null) { html = cached; } else { var decrease = -1; while(html.length == 0 || html.indexOf('Today\'s exchange rates are not notified') > -1) { decrease++; var url = baseURL + getDate(decrease); var html = UrlFetchApp.fetch(url).getContentText(); } cache.put('exchange-rate-woori', html, 3600); // cache for 1 hour } var hash = {}; var h = html.match(/<tbody[\s\S]*?<\/tr>/g).filter(function(tr) { return tr.indexOf('<th') == -1; }); h.map(function(tr) { var data = tr.match(/\<td[\s\S]*?>*?\<\/(td|th)\>/gm).map(function(td) { var datum = td.replace(/\<td[\s\S]*?\>|\<a[\s\S]*?\>|\<\/td\>|\<\/th\>|<\/a>|,/g, '').trim(); return (isNaN(parseFloat(datum)) ? datum : parseFloat(datum)); }); var key = data.shift(); hash[key] = { 'country': data[0], 'foreign-send': data[1], 'foreign-recv': data[2], 'cash-buy': data[3], 'cash-sell': data[4], 'buy-tc': data[5], 'standard': data[6], 'bank-of-korea': data[7], 'us-calc': data[8] }; }); return hash; } function getExchangeRate(currency, type) { if(!currency) currency = 'JPY'; var data = getExchangeHash(); if(!data.hasOwnProperty(currency)) { throw 'Unexcepted currency: ' + currency; } else if(!data[currency].hasOwnProperty(type)) { throw 'Unexcepted type: ' + type; } return data[currency][type]; } var cellConfig = { 'N3': ['JPY', 'standard'], 'O3': ['JPY', 'cash-buy'], 'N4': ['GBP', 'standard'], 'O4': ['GBP', 'cash-buy'], 'N5': ['EUR', 'standard'], 'O5': ['EUR', 'cash-buy'], }; function setCellValue(cell, value, apply) { SpreadsheetApp.getActiveSheet().getRange(cell).setValue(value); if(apply) SpreadsheetApp.flush(); } function reloadCell() { for(var cell in cellConfig) { if(cellConfig.hasOwnProperty(cell)) { setCellValue(cell, 'Loading..', true); var value = getExchangeRate.apply(null, cellConfig[cell]); setCellValue(cell, value, true); } } }