Use ONLYOFFICE macro to populate cells with OpenAI data

22 February 2023 By Serge 0 169

AI technology has made significant advancements and become a valuable tool that can automate our workflow. In our previous post, we talked about the ONLYOFFICE macro that can generate essays by using the OpenAI API. In this blog post, we will show you a macro that populates a spreadsheet with data obtained by OpenAI.

Use ONLYOFFICE macro to populate cells with OpenAI data

Using OpenAI API

Last time, we connected to the OpenAI API through the RapidAPI platform. It’s a quick and convenient way to mediate fetch requests. However, the basic plan offers only 100 requests a month. So, this time, we are going to take it up a notch and create our own Node.js Express server. It will utilize the OpenAI library to serve the API requests directly to the appropriate language model. It will also allow us to bypass CORS errors when making a fetch request from a browser.

Note! Making requests to OpenAI directly is available only upon registering an OpenAI account and acquiring a personal API key.

Setting up a server

First, we create a new folder for our server and set up a new Node.js project by executing the following command in the terminal:

Then we install the following dependencies:

  • express package – is an essential framework that facilitates server functionality.
  • cors  package – provides middleware to enable CORS.
  • openai package – provides convenient access to the OpenAI API from Node.js.
  • dotenv package – loads environment variables from a .env file into process.env.

To install them run the npm instal command in the terminal:

After that we create the .env  file. We will store our API keys there:

The OPEN_AI_API_KEY parameter contains your API key obtained from OpenAI, and the SECOND_API_KEY parameter is the API key for our server. We will incorporate it in the incoming fetch request for security measures.

Then we initialize the installed packages in the index.js file:

A successful request to OpenAI must include the OpenAI API key. To do that, we extract this API key from the .env file and store it in the configuration variable:

Then we add a post route from which we will send a request to OpenAI:

We also want to implement our API key for security measures. This second API key is supposed to be included in a fetch request. In that regard, our server needs to check the incoming fetch request and validate the included API key. To attain that, we add the following code to our route:

If the API key is correct, we send a request to OpenAI:

  • model parameter specifies the name of the language model to be used for generating text. In this case, it’s text-davinci-003, which is the most advanced model for text generation.
  • prompt parameter specifies text or context for the language model to generate text from. We will obtain this value from the fetch request body.
  • temperature parameter controls the randomness of the generated text. A value of 0 means the text is completely deterministic, while higher values will result in more varied and unexpected text.
  • max-tokens parameter indicates the maximum length of the generated text in tokens (i.e. words or sub-words).

Lastly, we set up our server to listen on port 3000:

The entire index.js code is the following:

Building the macro

First, we make a fetch request to our server:

We specified the prompt and apiKey values in the request body. Note that these parameters will differ depending on the data you want to receive and your server API key.

Next, we parse the response as JSON data:

We split the response text by line breaks into an array of strings and target the active spreadsheet:

After that, we create a nested loop that iterates over the rows and columns of the spreadsheet, using the split() function. We use the SetValue method to populate each cell with the data from the response and trim it of whitespaces:

The entire macro code is the following:

Now, let’s open the spreadsheet editor and run our macro!

We hope that you will utilize the concepts outlined in this blog post and make your work flow more efficient. We kindly encourage you to take the advantage of our various API methods and incorporate them into your daily routine tasks.

If you have any questions or ideas to share, feel free to leave comments or contact us. We are open to hearing your ideas and are eager to collaborate with you. Best of luck in your exploratory endeavors!