Assigning macros to graphic objects in ONLYOFFICE spreadsheets

Use macros to automate routine tasks for your calculations? In this post, we’ll discover one more macro example that can be useful for you.

Why assign macros to graphics

In version 6.4 of ONLYOFFICE Docs, we added a new feature in the spreadsheet editor — assigning a macro to be executed when you click on a graphic object. It allows you to turn such objects, for example autoshapes, into a button and set any macro as a handler when pressing it.

This way users can click on the button with the assigned macro to perform some action. It’s more convenient since they don’t need to open the list with macros each time they want to run one of them.

Macro example: automatically diagramming currency exchange rates

To show how it works, we created 3 macros for different currency pairs* (EUR-USD, USD-EUR, and BTC-USD) that get information about the exchange rates for the last several days and fill in the table with the received values.

These macros are assigned to 3 autoshapes. When you click on each autoshape, a macro is executed, the table is filled in with the relevant data and the corresponding chart is rebuilt.

Currency pair USD – EUR

Macro code is the following:

(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++;
            }
        }
    }
})();

Currency pair EUR – USD

Macro code is the following:

(function()
{
    var sCurPair = "EUR_USD";
    
     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++;
            }
        }
    }
})();

Currency pair BTC – USD

Macro code is the following:

(function()
{
    var sCurPair = "BTC_USD";
    
 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++;
            }
        }
    }
})();

*Please note: In these macros, we used the third-party service CurrencyConverterApi.com to get information about the exchange rates. There is a limit on the number of requests per hour. If this limit is exceeded, the macro will not work. If you want to use this macro, it’s better to register on the service website and use your own key in the macro code.