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

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.
<img decoding="async" loading="lazy" class="alignnone size-full wp-image-24459" src="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes.jpg" alt="Assigning macros to graphic objects in ONLYOFFICE spreadsheets" width="736" height="371" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes.jpg 736w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes-300x151.jpg 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes-634x320.jpg 634w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-assign-macros-to-shapes-353x178.jpg 353w" sizes="(max-width: 736px) 100vw, 736px" />

<h3>Why assign macros to graphics</h3>
In <a href="https://wpblogpost.teamlab.info//2021/08/onlyoffice-docs-v6-4-with-conditional-formatting/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">version 6.4</a> 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.
<h3>Macro example: automatically diagramming currency exchange rates</h3>
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
<a href="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur.png"><img decoding="async" loading="lazy" class="alignnone wp-image-24460" src="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur.png" alt="Assigning macros to graphic objects in ONLYOFFICE spreadsheets" width="736" height="418" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur.png 1114w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur-300x170.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur-1024x581.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur-768x436.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-usd-eur-314x178.png 314w" sizes="(max-width: 736px) 100vw, 736px" /></a>
Macro code is the following:
<pre class="theme:github font:monospace lang:js decode:true">(function()
{
 var sCurPair = "USD_EUR";
 
 function formatDate(d) {
 var month = '' + (d.getMonth() + 1),
 day = '' + d.getDate(),
 year = d.getFullYear();
 
 if (month.length &lt; 2) 
 month = '0' + month;
 if (day.length &lt; 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 + '&amp;amp;amp;amp;compact=ultra' + '&amp;amp;amp;amp;date=' + sDate + "&amp;amp;amp;amp;endDate=" + sEndDate + '&amp;amp;amp;amp;apiKey=e5ed9f0b2b3aa6f4158f';
 var xmlHttp = new XMLHttpRequest();
 xmlHttp.open("GET", sUrl, false);
 xmlHttp.send();
 if (xmlHttp.readyState == 4 &amp;amp;amp;amp;&amp;amp;amp;amp; 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++;
 }
 }
 }
})();</pre>
Currency pair EUR – USD 
<a href="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd.png"><img decoding="async" loading="lazy" class="alignnone wp-image-24464" src="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd.png" alt="Assigning macros to graphic objects in ONLYOFFICE spreadsheets" width="736" height="418" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd.png 1116w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd-300x170.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd-1024x582.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd-768x436.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-eur-usd-313x178.png 313w" sizes="(max-width: 736px) 100vw, 736px" /></a>
Macro code is the following:
<pre class="theme:github font:monospace lang:js decode:true">(function()
{
 var sCurPair = "EUR_USD";
 
 function formatDate(d) {
 var month = '' + (d.getMonth() + 1),
 day = '' + d.getDate(),
 year = d.getFullYear();
 
 if (month.length &lt; 2) 
 month = '0' + month;
 if (day.length &lt; 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 + '&amp;amp;compact=ultra' + '&amp;amp;date=' + sDate + "&amp;amp;endDate=" + sEndDate + '&amp;amp;apiKey=e5ed9f0b2b3aa6f4158f';
 var xmlHttp = new XMLHttpRequest();
 xmlHttp.open("GET", sUrl, false);
 xmlHttp.send();
 if (xmlHttp.readyState == 4 &amp;amp;&amp;amp; 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++;
 }
 }
 }
})();</pre>
Currency pair BTC – USD 
<a href="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd.png"><img decoding="async" loading="lazy" class="alignnone wp-image-24466" src="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd.png" alt="Assigning macros to graphic objects in ONLYOFFICE spreadsheets" width="736" height="417" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd.png 1115w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd-300x170.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd-1024x580.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd-768x435.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2021/09/onlyoffice-macros-btc-usd-315x178.png 315w" sizes="(max-width: 736px) 100vw, 736px" /></a>
Macro code is the following:
<pre class="theme:github font:monospace lang:js decode:true">(function()
{
 var sCurPair = "BTC_USD";
 
 function formatDate(d) {
 var month = '' + (d.getMonth() + 1),
 day = '' + d.getDate(),
 year = d.getFullYear();
 
 if (month.length &lt; 2) 
 month = '0' + month;
 if (day.length &lt; 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 + '&amp;amp;compact=ultra' + '&amp;amp;date=' + sDate + "&amp;amp;endDate=" + sEndDate + '&amp;amp;apiKey=e5ed9f0b2b3aa6f4158f';
 var xmlHttp = new XMLHttpRequest();
 xmlHttp.open("GET", sUrl, false);
 xmlHttp.send();
 if (xmlHttp.readyState == 4 &amp;amp;&amp;amp; 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++;
 }
 }
 }
})();</pre>
*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.
<div class="useful-links">
<h3>Useful links</h3>
<a href="https://wpblogpost.teamlab.info//2020/06/two-onlyoffice-macros-highlight-duplicates-and-go-to-the-next-empty-row/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">More info about macros in ONLYOFFICE</a>
Get the latest editors version: <a href="https://www.onlyoffice.com/download-docs.aspx?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape#docs-community" target="_blank" rel="noopener">community</a> / <a href="https://www.onlyoffice.com/download-docs.aspx?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape#docs-enterprise" target="_blank" rel="noopener">enterprise</a> / <a href="https://www.onlyoffice.com/download-docs.aspx?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape#docs-developer" target="_blank" rel="noopener">developer</a>
<a href="https://wpblogpost.teamlab.info//2021/08/onlyoffice-docs-v6-4-with-conditional-formatting/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">All new features in ONLYOFFICE Docs v.6.4</a>
<a href="https://wpblogpost.teamlab.info//2021/08/languagetool-and-grammalecte/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">New grammar checker plugins: LanguageTool and Grammalecte</a>
<a href="https://wpblogpost.teamlab.info//2021/08/how-to-create-a-gantt-chart-in-excel-sheets/?utm_source=blog&amp;utm_medium=social&amp;utm_campaign=macro_case_autoshape" target="_blank" rel="noopener">How to create a Gantt chart in spreadsheets</a>
<a href="https://github.com/ONLYOFFICE/" target="_blank" rel="noopener">ONLYOFFICE on GitHub</a>
</div>

Assigning macros to graphic objects in ONLYOFFICE spreadsheets

Recent posts

7 best tools to share files securely

How the Stiftung der Deutschen Wirtschaft manages its funding processes with ONLYOFFICE and Nextcloud

Practice opportunities: ONLYOFFICE Campus Ambassador Program for students