Use ONLYOFFICE macro to import CSV and TXT data into your spreadsheet

18 October 2022 By Serge 0 280

Storing tabular data in a CSV format is very practical in many ways, which makes this type of file extremely popular. ONLYOFFICE Docs allow importing local CSV and TXT files. However, what if we want to import these files from the web without having to download them? This blog post will share a little trick on how it can be done.

Use ONLYOFFICE macro to import CSV and TXT data into your spreadsheet

In our previous posts, we talked about ONLYOFFICE macros. They are JavaScript based, and we can’t stress enough how versatile they are. This feature will help us tackle importing remote CSV and TXT files. So, as you might have already guessed, we are going to create another macro.

Note! This macro is only operational with the desktop version of ONLYOFFICE Docs.

Building a macro

First, we need to fetch the file. JavaScript is the cornerstone of web development, and it has all the necessary tools already built in. In our case, we are going to use AJAX request:

Now we have the data, but it’s raw, so we need to format it. First, we split it into lines:

Then we get the active spreadsheet:

We have already split the array data into lines, and now we split it into separate words:

Then we insert these values into cells. To do that, we will use the GetCells and SetValue methods. The first one selects the range, and the latter inserts the value into the selected cell. The GetCells method takes two parameters: the number of rows and the number of columns. To indicate the number of rows and columns, we will use i and j variables and increment them at every for-loop iteration:

And finally, in order to display the new values, we need to recalculate the entire document. We will do it by implementing the Api.asc_calculate method. We used this method in our Air Quality macro. It allowed us to recalculate the incoming data with a certain interval. But this time, we will remove the interval parameter. This way, the document will be recalculated instantly:

The entire macro code is the following:

And now, let’s run our macro and see how it works!

Use ONLYOFFICE macro to import CSV and TXT data into your spreadsheet

This little macro is a perfect example of JavaScript versatility. And it’s just one of the many possible implementations of our API methods. We kindly encourage you to experiment and create your own scripts. Don’t hesitate to share your ideas or your macros with us. We are always open to discussion and cooperation. Best of luck in your exploratory endeavors!