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.

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:

$ npm init -y

1	$ npm init -y

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:

$ npm install express
$ npm install openai
$ npm install dotenv --save
$ npm install cors

$ npm install express

$ npm install openai

$ npm install dotenv --save

$ npm install cors

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

OPEN_AI_API_KEY="<YourOpenAIkey"
SECOND_API_KEY="YourAPIkey"

1 2	OPEN_AI_API_KEY="<YourOpenAIkey" SECOND_API_KEY="YourAPIkey"

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:

const express = require('express');
const cors = require('cors');
const { Configuration, OpenAIApi } = require('openai');
require('dotenv').config();
const app = express();

const openai = new OpenAIApi(configuration);
app.use(cors());
app.use(express.json());

const express = require('express');

const cors = require('cors');

const { Configuration, OpenAIApi } = require('openai');

require('dotenv').config();

const app = express();

const openai = new OpenAIApi(configuration);

app.use(cors());

app.use(express.json());

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:

const configuration = new Configuration({
  apiKey: process.env.OPEN_AI_API_KEY,
});

const configuration = new Configuration({

apiKey: process.env.OPEN_AI_API_KEY,

});

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

app.post('/completion', async (req, res) => {
}

1 2	app.post('/completion', async (req, res) => { }

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:

app.post('/completion', async (req, res) => {
  const requestApiKey = req.body.apiKey;
  
  if (!requestApiKey) {
    res.status(400).send({ error: "Error: API Key is missing." });
    return;
  }
  if (requestApiKey !== process.env.SECOND_API_KEY) {
    res.status(401).send({ error: "Error: API Key is incorrect." });
    return;
  }

app.post('/completion', async (req, res) => {

const requestApiKey = req.body.apiKey;

if (!requestApiKey) {

res.status(400).send({ error: "Error: API Key is missing." });

return;

}

if (requestApiKey !== process.env.SECOND_API_KEY) {

res.status(401).send({ error: "Error: API Key is incorrect." });

return;

}

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

const completion = await openai.createCompletion({
    model: 'text-davinci-003',
    prompt: req.body.prompt,
    temperature: 0,
    max_tokens: 300,
  });
  res.json(completion.data.choices[0].text);

const completion = await openai.createCompletion({

model: 'text-davinci-003',

prompt: req.body.prompt,

temperature: 0,

max_tokens: 300,

});

res.json(completion.data.choices[0].text);

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:

app.listen(3000, () => {
  console.log('Server running on port 3000');
});

app.listen(3000, () => {

console.log('Server running on port 3000');

});

The entire index.js code is the following:

const express = require('express');
const cors = require('cors');
const { Configuration, OpenAIApi } = require('openai');
require('dotenv').config();
const app = express();
const configuration = new Configuration({
  apiKey: process.env.OPEN_AI_API_KEY,
});
const openai = new OpenAIApi(configuration);
app.use(cors());
app.use(express.json());
app.post('/completion', async (req, res) => {
  const requestApiKey = req.body.apiKey;
  
  if (!requestApiKey) {
    res.status(400).send({ error: "Error: API Key is missing." });
    return;
  }
  if (requestApiKey !== process.env.SECOND_API_KEY) {
    res.status(401).send({ error: "Error: API Key is incorrect." });
    return;
  }
  const completion = await openai.createCompletion({
    model: 'text-davinci-003',
    prompt: req.body.prompt,
    temperature: 0,
    max_tokens: 300,
  });
  res.json(completion.data.choices[0].text);
});
app.listen(3000, () => {
  console.log('Server running on port 3000');
});

const express = require('express');

const cors = require('cors');

const { Configuration, OpenAIApi } = require('openai');

require('dotenv').config();

const app = express();

const configuration = new Configuration({

apiKey: process.env.OPEN_AI_API_KEY,

});

const openai = new OpenAIApi(configuration);

app.use(cors());

app.use(express.json());

app.post('/completion', async (req, res) => {

const requestApiKey = req.body.apiKey;

if (!requestApiKey) {

res.status(400).send({ error: "Error: API Key is missing." });

return;

}

if (requestApiKey !== process.env.SECOND_API_KEY) {

res.status(401).send({ error: "Error: API Key is incorrect." });

return;

}

const completion = await openai.createCompletion({

model: 'text-davinci-003',

prompt: req.body.prompt,

temperature: 0,

max_tokens: 300,

});

res.json(completion.data.choices[0].text);

});

app.listen(3000, () => {

console.log('Server running on port 3000');

});

Building the macro

First, we make a fetch request to our server:

fetch('http://<your_server_address>/completion', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({
prompt: 'List the 10 wealthiest countries in the world. Provide GPT figures',
apiKey: '<YourAPIkey>'
})
})

fetch('http://<your_server_address>/completion', {

method: 'POST',

headers: {

'Content-Type': 'application/json'

body: JSON.stringify({

prompt: 'List the 10 wealthiest countries in the world. Provide GPT figures',

apiKey: '<YourAPIkey>'

})

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:

.then(response => response.json())

1	.then(response => response.json())

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

.then(data => {
var arrAllRows = data.split(/\r?\n|\r/);
var wSheet = Api.GetActiveSheet()

.then(data => {

var arrAllRows = data.split(/\r?\n|\r/);

var wSheet = Api.GetActiveSheet()

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:

var i = 0;
var j = 0;
  for (var singleRow = 0; singleRow < arrAllRows.length; singleRow++) {
            var rowCells = arrAllRows[singleRow].split('-');
            for (var rowCell = 0; rowCell < rowCells.length; rowCell++) {
                wSheet.GetCells(i,j).SetValue(rowCells[rowCell].trim());
                j = j + 1;
            } 
            i = i + 1;
            j = 1;
        }

var i = 0;

var j = 0;

for (var singleRow = 0; singleRow < arrAllRows.length; singleRow++) {

var rowCells = arrAllRows[singleRow].split('-');

for (var rowCell = 0; rowCell < rowCells.length; rowCell++) {

wSheet.GetCells(i,j).SetValue(rowCells[rowCell].trim());

j = j + 1;

}

i = i + 1;

j = 1;

}

The entire macro code is the following:

(function()
{
   fetch('http://<your_server_address>/completion', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({
prompt: 'List the 10 wealthiest countries in the world. Provide GPT figures',
apiKey: 'dfhgsd63456efgdfhjikhfzf435332fdhd'
})
})
.then(response => response.json())
.then(data => {
var arrAllRows = data.split(/\r?\n|\r/);
var wSheet = Api.GetActiveSheet();
var i = 0;
var j = 0;
  for (var singleRow = 0; singleRow < arrAllRows.length; singleRow++) {
            var rowCells = arrAllRows[singleRow].split('-');
            for (var rowCell = 0; rowCell < rowCells.length; rowCell++) {
                wSheet.GetCells(i,j).SetValue(rowCells[rowCell].trim());
                j = j + 1;
            } 
            i = i + 1;
            j = 1;
        }
});
})();

(function()

{

fetch('http://<your_server_address>/completion', {

method: 'POST',

headers: {

'Content-Type': 'application/json'

body: JSON.stringify({

prompt: 'List the 10 wealthiest countries in the world. Provide GPT figures',

apiKey: 'dfhgsd63456efgdfhjikhfzf435332fdhd'

})

.then(response => response.json())

.then(data => {

var arrAllRows = data.split(/\r?\n|\r/);

var wSheet = Api.GetActiveSheet();

var i = 0;

var j = 0;

for (var singleRow = 0; singleRow < arrAllRows.length; singleRow++) {

var rowCells = arrAllRows[singleRow].split('-');

for (var rowCell = 0; rowCell < rowCells.length; rowCell++) {

wSheet.GetCells(i,j).SetValue(rowCells[rowCell].trim());

j = j + 1;

}

i = i + 1;

j = 1;

}

});

})();

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!