Update cell value from a URL real tme or once per spread sheet open

Docs
1

I can use GetData from the document editor UI and read in a value from https://aaa.bbb/ddd.txt

How can I make this run every 10 seconds or once every time the spreadsheet opens as a macro?

I tried HYPERLINK but it has no “refresh” ability

I see no GetData call in the API to write my own macro

thanks
Ivan

PS LATEST Community server and documet editor.

2

Hello @ivan
We are working on example of macro for described scenario at the moment. We will placed it soon here: ONLYOFFICE Api Documentation - Macro samples
I will notify you when example is ready.

1 Like
3

thanks Alexandre

4

Hi Alexandre, any update?

5

Hello @ivan
We haven’t updated Macro samples at the moment. But we placed similar macro example in our blog, please check it out: Monitor air quality data with ONLYOFFICE macro | ONLYOFFICE Blog
We are going to update Macro examples a little bit later (probably we will review macro from blog and will make simpler)

6

Hi Alexandre.

Heres my Macro to grab currency ONCE PER TIME spread sheet loads… the source API site only updates rates once per day… but PERFECT for cost-of-living tracker…

I’m looking at converting 1 Chilean Peso to 1 USA Dollar per spread sheet load.

See
[https://exchangerate.host/#/#docs]

>>You can add this macro to the collection?

const oWorksheet = Api.GetActiveSheet();
oWorksheet.GetRange(“A1:A1”).SetValue(“CLPtoUSD:”);
oWorksheet.GetRange(“A1:A1”).AutoFit(false, true);

const d = new Date();
d.setDate(15);

const xhr = new XMLHttpRequest();
var xhrURL = ‘https://api.exchangerate.host/convert?from=CLP&to=USD’;

xhr.open(“GET”, xhrURL, true);

xhr.onload = (e) => {
if (xhr.readyState === 4) {
if (xhr.status === 200) {
//console.log(xhr.responseText);
var response = xhr.responseText;
var array = response.split(’,’);
var rate = array[7].split(’:’);
var rateis = Number(rate[2].replace(/[^0-9.]/, ‘’));
console.log(rateis);
oWorksheet.GetRangeByNumber(0, 1).SetValue(rateis);
oWorksheet.GetRangeByNumber(0, 2).SetValue(d);

} else {
  console.error(xhr.statusText);
}

}
};
xhr.onerror = (e) => {
console.error(xhr.statusText);
};
xhr.send(null);

7

Hello @ivan
I have tried to copy-paste your macro, but I faced few errors in Macro tab.
Should I use mentioned macro with empty file? Also please make screenshot, provide us with whole scenario of this macro usage.

8

OPen an empty spreadsheet

Go to plugins

Goto macros

paste in macro
change your two currencies
I used CLP and USD
and CLF (UF) and USD

set to run on sheet open

Site REST call docs are here https://exchangerate.host/

=================================================

const oWorksheet = Api.GetActiveSheet();
oWorksheet.GetRange(“A1:A1”).SetValue(“CLP to USD:”);
oWorksheet.GetRange(“A1:A1”).AutoFit(false, true);
oWorksheet.GetRange(“A2:A2”).SetValue(“UF to USD:”);

const d = new Date();

const xhr = new XMLHttpRequest();
var xhrURL = ‘https://api.exchangerate.host/convert?from=CLP&to=USD’;

xhr.open(“GET”, xhrURL, true);

xhr.onload = (e) => {
if (xhr.readyState === 4) {
if (xhr.status === 200) {
//console.log(xhr.responseText);
var response = xhr.responseText;
var array = response.split(’,’);
var rate = array[7].split(’:’);
var rateis = Number(rate[2].replace(/[^0-9.]/, ‘’));
console.log(rateis);
oWorksheet.GetRangeByNumber(0, 1).SetValue(rateis);
oWorksheet.GetRangeByNumber(0, 2).SetValue(d);

} else {
  console.error(xhr.statusText);
}

}
};
xhr.onerror = (e) => {
console.error(xhr.statusText);
};
xhr.send(null);

const xhr2 = new XMLHttpRequest();
var xhrURL2 = ‘https://api.exchangerate.host/convert?from=CLF&to=USD’;

xhr2.open(“GET”, xhrURL2, true);

xhr2.onload = (e) => {
if (xhr2.readyState === 4) {
if (xhr2.status === 200) {
//console.log(xhr.responseText);
var response2 = xhr2.responseText;
var array2 = response2.split(’,’);
var rate2 = array2[7].split(’:’);
var rateis2 = Number(rate2[2].replace(/[^0-9.]/, ‘’));
console.log(rateis2);
oWorksheet.GetRangeByNumber(1, 1).SetValue(rateis2);

} else {
  console.error(xhr2.statusText);
}

}
};
xhr2.onerror = (e) => {
console.error(xhr2.statusText);
};
xhr2.send(null);

9

Thank you for provided data. We are checking it. I will update this thread a little bit later (we need some time to discuss it internally).

10

Hello @ivan
We have compared your provided macro with existing one: ONLYOFFICE Api Documentation - Macro samples

It looks similar, both of them achieve similar goals. This way, I’m not sure that we will add your macro example to Macro samples title on our website.
Anyway, we are glad that community creates new ways to use ONLYOFFICE. Please feel free to contact us if you face any difficulties.

11

Thats fine Alexandre.

Onlyoffice continues to rock and I promote it widely!!!

Thanks for the product. Seriously couldnt get by without it.!

1 Like