Use ONLYOFFICE macro to analyze spreadsheet data

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’ll demonstrate how to create ONLYOFFICE macro that overcomes this limitation, enabling you to analyze spreadsheets using the OpenAI API.

About the macro

To work around this limitation, our macro follows these steps:

1. It collects selected cell values from the spreadsheet.
2. Compiles these values into an array.
3. Converts this array into a string.
4. Sends it using a fetch request to a Node.js proxy server.
5. The server retrieves the array from the request body.
6. It then utilizes the OpenAI library to send an API request to OpenAI.
7. Once the response is received, the server sends it back to the macro within a response object.

For detailed instructions on setting up the proxy server, including the complete code, check out our blog post 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’s token limitation of 4096 tokens.

Building the macro

First we retrieve the currently selected range in a spreadsheet:

// Get the selected range using ONLYOFFICE API
var selection = Api.GetSelection();

Then we create an empty array called rowData to store data that will be collected from the selected cells:

  // Initialize an array to store all data
  var rowData = [];

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:

// 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);
  });

After that we convert the values collected in the rowData array into a single string where the values are separated by commas:

 // Merge the values in rowData and separate them by commas
  var rowDataAsString = rowData.join(',');

We create  an object called requestData:

 // Prepare the data to send in the POST request
  var requestData = {
    prompt: `analyze this data ${rowDataAsString}`, // Use the merged string here
    apiKey: '<APIkey>', // Replace with your API key
  };

• The prompt field includes the merged string of cell values for analysis.
• The apiKey field contains the API key used by your Node.js server to authenticate the incoming fetch requests.

Then we use the fetch function to send a POST request to the specified URL:

 fetch('http://localhost:3000/completion', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
    },
    body: JSON.stringify(requestData),
  })

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:

.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);
    });

The entire macro code is the following:

(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: '<APIkey>', // 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);
    });
})();

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

Financial datasheet example:

Employee datasheet example:

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 share them with us. We’re open and enthusiastic about the possibility of collaboration. Best of luck in your exploratory endeavors!