In this blog post, we'll create an ONLYOFFICE macro that utilizes OpenAI to analyze datasheets.

While ChatGPT 4 now provides enhanced features, some users still prefer ChatGPT 3.5 due to its lower token cost. However, ChatGPT 3.5 lacks file uploading capabilities, preventing users from analyzing data sheets. In this blog post, we&#8217;ll demonstrate how to create ONLYOFFICE macro that overcomes this limitation, enabling you to analyze spreadsheets using the OpenAI API.
<img decoding="async" loading="lazy" class="aligncenter wp-image-68672" src="https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06160757/ChatGpt_analyze.png" alt="Use ONLYOFFICE macro to analyze spreadsheet data" width="736" height="372" srcset="https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06160757/ChatGpt_analyze.png 1024w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06160757/ChatGpt_analyze-300x151.png 300w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06160757/ChatGpt_analyze-768x388.png 768w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06160757/ChatGpt_analyze-634x320.png 634w, https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06160757/ChatGpt_analyze-353x178.png 353w" sizes="(max-width: 736px) 100vw, 736px" />

<h2>About the macro</h2>
To work around this limitation, our macro follows these steps:
<ol>
<li>It collects selected cell values from the spreadsheet.</li>
<li>Compiles these values into an array.</li>
<li>Converts this array into a string.</li>
<li>Sends it using a fetch request to a Node.js proxy server.</li>
<li>The server retrieves the array from the request body.</li>
<li>It then utilizes the OpenAI library to send an API request to OpenAI.</li>
<li>Once the response is received, the server sends it back to the macro within a response object.</li>
</ol>
For detailed instructions on setting up the proxy server, including the complete code, check out our <a href="https://www.onlyoffice.com/blog/2023/02/chatgpt_spreadsheet?utm_source=blog&amp;utm_medium=article+&amp;utm_id=ChatGPT_analyze_macro" target="_blank" rel="noopener">blog post</a> that demonstrates how to create a macro for populating spreadsheets with data from OpenAI.
Note! Please note that this macro is best suited for mid-sized tables, typically around 50 rows, due to the ChatGPT 3.5 model&#8217;s token limitation of 4096 tokens.
<h2>Building the macro</h2>
First we retrieve the currently selected range in a spreadsheet:
<pre class="theme:github font:monospace lang:js decode:true ">// Get the selected range using ONLYOFFICE API
var selection = Api.GetSelection();
</pre>
Then we create an empty array called rowData to store data that will be collected from the selected cells:
<pre class="theme:github font:monospace lang:js decode:true ">  // Initialize an array to store all data
  var rowData = [];
</pre>
We iterate through each cell in the selected range using a ForEach loop. For each cell, we retrieve its value using the GetValue method then add that value to the rowData array:
<pre class="theme:github font:monospace lang:js decode:true ">// Use ForEach to iterate through the selected range
  selection.ForEach(function (cell) {
    // Retrieve the cell value using ONLYOFFICE API
  var cellValue = cell.GetValue();
    // Add cell value to the rowData array
    rowData.push(cellValue);
  });
</pre>
After that we convert the values collected in the rowData array into a single string where the values are separated by commas:
<pre class="theme:github font:monospace lang:js decode:true "> // Merge the values in rowData and separate them by commas
  var rowDataAsString = rowData.join(',');
</pre>
We create  an object called requestData:
<pre class="theme:github font:monospace lang:js decode:true "> // Prepare the data to send in the POST request
  var requestData = {
    prompt: `analyze this data ${rowDataAsString}`, // Use the merged string here
    apiKey: '&lt;APIkey&gt;', // Replace with your API key
  };
</pre>
<ul>
<li>The prompt field includes the merged string of cell values for analysis.</li>
<li>The apiKey field contains the API key used by your Node.js server to authenticate the incoming fetch requests.</li>
</ul>
Then we use the fetch function to send a POST request to the specified URL:
<pre class="theme:github font:monospace lang:js decode:true "> fetch('http://localhost:3000/completion', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
    },
    body: JSON.stringify(requestData),
  })
</pre>
We handle the API response by first converting it to JSON format, logging the data to the console, and also implementing error handling in case there are any issues with the POST request:
<pre class="theme:github font:monospace lang:js decode:true ">.then(function (response) {
      return response.json();
    })
    .then(function (data) {
      // Log the API response
      console.log(data);
    })
    .catch(function (error) {
      // Handle any errors that occur during the fetch
      console.error('Error:', error);
    });
</pre>
The entire macro code is the following:
<pre class="theme:github font:monospace lang:js decode:true">(function()
{
  // Get the selected range using ONLYOFFICE API
  var selection = Api.GetSelection();
  // Initialize an array to store all data
  var rowData = [];
  // Use ForEach to iterate through the selected range
  selection.ForEach(function (cell) {
    // Retrieve the cell value using ONLYOFFICE API
  var cellValue = cell.GetValue();
    // Add cell value to the rowData array
    rowData.push(cellValue);
  });
  // Merge the values in rowData and separate them by commas
  var rowDataAsString = rowData.join(',');
  // Prepare the data to send in the POST request
  var requestData = {
    prompt: `analyze this data ${rowDataAsString}`, // Use the merged string here
    apiKey: '&lt;APIkey&gt;', // Replace with your API key
  };
  // Send the data to the API (replace the URL with your OpenAI API endpoint)
  fetch('http://localhost:3000/completion', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
    },
    body: JSON.stringify(requestData),
  })
    .then(function (response) {
      return response.json();
    })
    .then(function (data) {
      // Log the API response
      console.log(data);
    })
    .catch(function (error) {
      // Handle any errors that occur during the fetch
      console.error('Error:', error);
    });
})();
</pre>
Now, let&#8217;s run our macro and see how it works!
Financial datasheet example:
<div style="width: 640px;" class="wp-video">
<video class="wp-video-shortcode" id="video-68626-1" width="640" height="234" preload="metadata" controls="controls"><source type="video/webm" src="https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06104551/financialChatGPt.webm?_=1" /><a href="https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06104551/financialChatGPt.webm">https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06104551/financialChatGPt.webm</a></video></div>
Employee datasheet example:
<div style="width: 640px;" class="wp-video"><video class="wp-video-shortcode" id="video-68626-2" width="640" height="233" preload="metadata" controls="controls"><source type="video/webm" src="https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06104627/EmploeeChatGPT.webm?_=2" /><a href="https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06104627/EmploeeChatGPT.webm">https://static-blog.onlyoffice.com/wp-content/uploads/2023/09/06104627/EmploeeChatGPT.webm</a></video></div>
We hope that the insights shared in this blog post can contribute to greater work efficiency. We encourage you to explore and implement our various API methods into your daily work.
If you have any inquiries or creative ideas, please feel free to <a href="https://www.onlyoffice.com/en/contacts.aspx?utm_source=blog&amp;utm_medium=article+&amp;utm_id=ChatGPT_analyze_macro" target="_blank" rel="noopener">share them with us</a>. We&#8217;re open and enthusiastic about the possibility of collaboration. Best of luck in your exploratory endeavors!
<div class="useful-links">
<h3>Useful links</h3>
<a href="https://api.onlyoffice.com/docbuilder/spreadsheetapi?utm_source=blog&amp;utm_medium=article+&amp;utm_id=ChatGPT_analyze_macro" target="_blank" rel="noopener">ONLYOFFICE spreadsheet API</a>
<a href="https://openai.com/?utm_source=blog&amp;utm_medium=article+&amp;utm_id=ChatGPT_analyze_macro" target="_blank" rel="noopener">OpenAI website</a>
<a href="https://platform.openai.com/docs/api-reference/introduction?utm_source=blog&amp;utm_medium=article+&amp;utm_id=ChatGPT_analyze_macro" 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_analyze_macro" target="_blank" rel="noopener">Use ONLYOFFICE macro to generate an essay with ChatGPT</a>
<a href="https://www.onlyoffice.com/blog/2023/02/chatgpt_spreadsheet?utm_source=blog&amp;utm_medium=article+&amp;utm_id=ChatGPT_analyze_macro" target="_blank" rel="noopener">Use ONLYOFFICE macro to populate cells with OpenAI data</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_analyze_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 analyze spreadsheet data

Recent posts

Use ONLYOFFICE macro to analyze spreadsheet data

Back to school 2023 with ONLYOFFICE: discounts for education

Best AI writers 2023