In this blog post, we will show you how to set up your OpenAI server and build a macro that populates a spreadsheet with data obtained from it.

AI technology has made significant advancements and become a valuable tool that can automate our workflow. <a href="https://wpblog.onlyoffice.com/2023/02/chatgpt-essay/?preview=true&#038;utm_source=blog&#038;utm_medium=article+&#038;utm_id=chatgpt_spreadsheet_macro" target="_blank" rel="noopener">In our previous post</a>, 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.

<img decoding="async" loading="lazy" class="aligncenter wp-image-54442" src="https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/Illustration_blog-macro-chatgpt-in-spreadsheet-editor.png" alt="Use ONLYOFFICE macro to populate cells with OpenAI data" width="736" height="371" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/Illustration_blog-macro-chatgpt-in-spreadsheet-editor.png 1472w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/Illustration_blog-macro-chatgpt-in-spreadsheet-editor-300x151.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/Illustration_blog-macro-chatgpt-in-spreadsheet-editor-1024x516.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/Illustration_blog-macro-chatgpt-in-spreadsheet-editor-768x387.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/Illustration_blog-macro-chatgpt-in-spreadsheet-editor-634x320.png 634w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/Illustration_blog-macro-chatgpt-in-spreadsheet-editor-353x178.png 353w" sizes="(max-width: 736px) 100vw, 736px" />
<h2>Using OpenAI API</h2>
Last time, we connected to the <a href="https://openai.com/" target="_blank" rel="noopener">OpenAI</a> 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.
<h2>Setting up a server</h2>
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:
<pre class="theme:github font:monospace lang:js decode:true ">$ npm init -y
</pre>
Then we install the following dependencies:
<ul>
<li>express package &#8211; is an essential framework that facilitates server functionality.</li>
<li>cors  package &#8211; provides middleware to enable CORS.</li>
<li>openai package &#8211; provides convenient access to the OpenAI API from Node.js.</li>
<li>dotenv package &#8211; loads environment variables from a .env file into process.env.</li>
</ul>
To install them run the npm instal command in the terminal:
<pre class="theme:github font:monospace lang:js decode:true ">$ npm install express
$ npm install openai
$ npm install dotenv --save
$ npm install cors
</pre>
After that we create the .env  file. We will store our API keys there:
<pre class="theme:github font:monospace lang:js decode:true ">OPEN_AI_API_KEY="&lt;YourOpenAIkey"
SECOND_API_KEY="YourAPIkey"
</pre>
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:
<pre class="theme:github font:monospace lang:js decode:true ">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());
</pre>
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:
<pre class="theme:github font:monospace lang:js decode:true ">const configuration = new Configuration({
  apiKey: process.env.OPEN_AI_API_KEY,
});
</pre>
Then we add a post route from which we will send a request to OpenAI:
<pre class="theme:github font:monospace lang:js decode:true ">app.post('/completion', async (req, res) =&gt; {
}
</pre>
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:
<pre class="theme:github font:monospace lang:js decode:true ">app.post('/completion', async (req, res) =&gt; {
  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;
  }
</pre>
If the API key is correct, we send a request to OpenAI:
<pre class="theme:github font:monospace lang:js decode:true ">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);
</pre>
<ul>
<li>model parameter specifies the name of the language model to be used for generating text. In this case, it&#8217;s text-davinci-003, which is the most advanced model for text generation.</li>
<li>prompt parameter specifies text or context for the language model to generate text from. We will obtain this value from the fetch request body.</li>
<li>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.</li>
<li>max-tokens parameter indicates the maximum length of the generated text in tokens (i.e. words or sub-words).</li>
</ul>
Lastly, we set up our server to listen on port 3000:
<pre class="theme:github font:monospace lang:js decode:true">app.listen(3000, () =&gt; {
  console.log('Server running on port 3000');
});</pre>
The entire index.js code is the following:
<pre class="theme:github font:monospace lang:js decode:true">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) =&gt; {
  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, () =&gt; {
  console.log('Server running on port 3000');
});</pre>
<h2>Building the macro</h2>
First, we make a fetch request to our server:
<pre class="theme:github font:monospace lang:js decode:true">fetch('http://&lt;your_server_address&gt;/completion', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({
prompt: 'List the 10 wealthiest countries in the world. Provide GPT figures',
apiKey: '&lt;YourAPIkey&gt;'
})
})
</pre>
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:
<pre class="theme:github font:monospace lang:js decode:true">.then(response =&gt; response.json())
</pre>
We split the response text by line breaks into an array of strings and target the active spreadsheet:
<pre class="theme:github font:monospace lang:js decode:true">.then(data =&gt; {
var arrAllRows = data.split(/\r?\n|\r/);
var wSheet = Api.GetActiveSheet()</pre>
Next, we set up a nested loop to go through rows and columns in the spreadsheet. We use the split() function to break down the data as needed. We use the SetValue method to insert the response data into each cell while making sure to get rid of any extra spaces. It&#8217;s important to mention that we start with the singleRow variable set to 2 to focus on the particular data we want from the arrAllRows array. Additionally, we use the AutoFit function to adjust the cell width for better display:
<pre class="theme:github font:monospace lang:js decode:true">var i = 1;
var j = 1;
 for (var singleRow = 2; singleRow &lt; arrAllRows.length; singleRow++) {
 var rowCells = arrAllRows[singleRow].split('-');
 for (var rowCell = 0; rowCell &lt; rowCells.length; rowCell++) {
 var oRange = wSheet.GetCells(i,j); 
 oRange.SetValue(rowCells[rowCell].trim());
 oRange.AutoFit(false, true)
 j = j + 1;
 } 
 i = i + 1;
 j = 1;
 }
});</pre>
Lastly, we execute the setInterval function to reload the spreadsheet:
<pre class="theme:github font:monospace lang:js decode:true">let reload = setInterval(function(){
 Api.asc_calculate(Asc.c_oAscCalculateType.All);
 });
})();</pre>
The entire macro code is the following:
<pre class="theme:github font:monospace lang:js decode:true ">(function()
{
 fetch('http://localhost:3000/completion', {
method: 'POST',
headers: {
'Content-Type': 'application/json'
},
body: JSON.stringify({
prompt: 'List the top 10 wealthiest countries and provide their GDP figures',
apiKey: '&lt;YourAPIkey&gt;'
})
})
.then(response =&gt; response.json())
.then(data =&gt; {
var arrAllRows = data.split(/\r?\n|\r/);
var wSheet = Api.GetActiveSheet();
var i = 1;
var j = 1;
 for (var singleRow = 2; singleRow &lt; arrAllRows.length; singleRow++) {
 var rowCells = arrAllRows[singleRow].split('-');
 for (var rowCell = 0; rowCell &lt; rowCells.length; rowCell++) {
 var oRange = wSheet.GetCells(i,j); 
 oRange.SetValue(rowCells[rowCell].trim());
 oRange.AutoFit(false, true)
 j = j + 1;
 } 
 i = i + 1;
 j = 1;
 }
});

let reload = setInterval(function(){
 Api.asc_calculate(Asc.c_oAscCalculateType.All);
 });
})();</pre>
Now, let’s open the spreadsheet editor and run our macro!
<div style="width: 640px;" class="wp-video">
<video class="wp-video-shortcode" id="video-54346-1" width="640" height="271" preload="metadata" controls="controls"><source type="video/webm" src="https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/06180819/chatGPT_poulateV2.webm?_=1" /><a href="https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/06180819/chatGPT_poulateV2.webm">https://static-blog.onlyoffice.com/wp-content/uploads/2023/02/06180819/chatGPT_poulateV2.webm</a></video></div>
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 <a href="https://www.onlyoffice.com/contacts.aspx" target="_blank" rel="noopener">contact us</a>. We are open to hearing your ideas and are eager to collaborate with you. Best of luck in your exploratory endeavors!
<div class="useful-links">
<h3>Useful links</h3>
<a href="https://api.onlyoffice.com/docbuilder/spreadsheetapi" target="_blank" rel="noopener">ONLYOFFICE spreadsheet API</a>
<a href="https://openai.com/" target="_blank" rel="noopener">OpenAI website</a>
<a href="https://platform.openai.com/docs/api-reference/introduction" target="_blank" rel="noopener">OpenAI API documentation page</a>
<a href="https://www.onlyoffice.com/blog/2023/02/chatgpt-essay?utm_source=blog&amp;utm_medium=article+&amp;utm_id=chatgpt_spreadsheet_macro" target="_blank" rel="noopener">Use ONLYOFFICE macro to generate an essay with ChatGPT</a>
<a href="https://www.onlyoffice.com/blog/2023/06/updated-chatgpt-plugin-for-onlyoffice-docs/?utm_source=blog&amp;utm_medium=article+&amp;utm_id=chatgpt_spreadsheet_macro" target="_blank" rel="noopener">Introducing the ChatGPT plugin in ONLYOFFICE Docs</a>
<a href="https://www.youtube.com/playlist?list=PLCF48HEKMOYNBygGjr9lSw3PLpVXQ6s_t" target="_blank" rel="noopener">Macros in ONLYOFFICE Docs (Free Course)</a>
</div>

Use ONLYOFFICE macro to populate cells with OpenAI data

Recent posts

Use ONLYOFFICE macro to analyze spreadsheet data

Back to school 2023 with ONLYOFFICE: discounts for education

Best AI writers 2023