Returns information about the exchange rates for the last several days and fills in the table with the received values. A macro for the USD-EUR currency pair is presented here but you can get information about other exchange rates by modifying the sCurPair variable value ("EUR_USD", "BTC_USD", etc).
You can assign this macro to the autoshape. When you click on it, a macro is executed, the table is filled in with the relevant data and the corresponding chart is rebuilt.
(function() { var sCurPair = "USD_EUR"; function formatDate(d) { var month = '' + (d.getMonth() + 1), day = '' + d.getDate(), year = d.getFullYear(); if (month.length < 2) month = '0' + month; if (day.length < 2) day = '0' + day; return [year, month, day].join('-'); } function previousWeek(){ var today = new Date(); var prevweek = new Date(today.getFullYear(), today.getMonth(), today.getDate()-7); return prevweek; } var sDate = formatDate(previousWeek()); var sEndDate = formatDate(new Date()); var apiKey = 'e5ed9f0b2b3aa6f4158f'; var sUrl = 'https://free.currconv.com/api/v7/convert?q=' + sCurPair + '&compact=ultra' + '&date=' + sDate + "&endDate=" + sEndDate + '&apiKey=e5ed9f0b2b3aa6f4158f'; var xmlHttp = new XMLHttpRequest(); xmlHttp.open("GET", sUrl, false); xmlHttp.send(); if (xmlHttp.readyState == 4 && xmlHttp.status == 200) { var oData = JSON.parse(xmlHttp.responseText); for(var key in oData) { var sheet = Api.GetSheet("Sheet1"); var oRange = sheet.GetRangeByNumber(0, 1); oRange.SetValue(key); var oDates = oData[key]; var nRow = 1; for(var date in oDates) { oRange = sheet.GetRangeByNumber(nRow, 0); oRange.SetValue(date); oRange = sheet.GetRangeByNumber(nRow, 1); oRange.SetValue(oDates[date]); nRow++; } } } })();
Methods used: GetSheet, GetRangeByNumber, SetValue