Macro: load json and paste data into table cells?

Plugins
1

Hi! Is it possible? I want to write a macro to access json data by URL and have that data parsed and placed in my spreadsheet table cells. Can not find a suitable APi for that…

2

Hello @Postscripter
Please describe the usage scenario as detailed as possible. Do I understand it right that you want to run macro from an editor > get access to external data > get back to the editor and paste data into it?

3

Hello, @Alexandre ! That’s right, I need to access external data. Actually, I have already accomplished what I wanted, but had to use google spreadsheets. I really wish OnlyOffice had such a powerful scripting.

As detailed as possible? Okay, the scenario is: I doubleclick my .xlsx document, firing up the editor, then use a hotkey or click a dedicated button / hot image to run my macro. The macro obtains initial data from cells in a loop, row by row: A1, A2, A3… And send it to external API: https://example.com/data?param=cellValue. Then, get back JSON data, parse it and write back to the table: B1, B2, B3…

var cellValue = sheet.getRange(i, 1).getValue();
var response = UrlFetchApp.fetch(`https://example.com/data?param1=${cellValue}`);
var data = JSON.parse(response.getContentText());
sheet.getRange(i, 2).setValue(data?.someData || '');
4

For the sake of simplicity, imagine getting weather forecasts for a list of cities or updating today’s exchange rates used in formulae converting dollars to euros, etc.

5

Probably you can use this sample as a guide: ONLYOFFICE Api Documentation - Macro samples
As far as i understand there’s the same scenario as you described.

1 Like
6

Wow, how did I miss that page? Thank you, Alexandre

1 Like