Building OpenAI Assistant API into Google Sheets

Hi @harry.obrien

Creating a custom function in Google Sheets that interacts with the OpenAI Assistant API (or any external API) directly from a cell formula isn’t directly possible due to Google Sheets’ limitations. Custom functions in Sheets can only perform operations on the data within the spreadsheet and cannot call external APIs or perform HTTP requests.

However, you can achieve this functionality using Google Apps Script to create a custom menu in Google Sheets that triggers a script to send the content of a selected cell to the OpenAI Assistant API and then updates another cell with the response. Here’s a basic example:

Step 1: Open Google Apps Script

  • Open your Google Sheet.
  • Click on “Extensions” > “Apps Script”.

Step 2: Replace the Code

Delete any code in the script editor and replace it with the following:

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Adds a custom menu to Google Sheets.
  ui.createMenu('OpenAI API')
      .addItem('Get Response', 'getResponse')
      .addToUi();
}

function getResponse() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getActiveCell();
  var promptText = cell.getValue();
  
  if (promptText) {
    // Replace YOUR_API_KEY with your actual OpenAI API key
    var apiKey = 'YOUR_API_KEY';
    var apiURL = 'https://api.openai.com/v1/completions';
    
    var payload = JSON.stringify({
      "model": "text-davinci-003", // Or another model
      "prompt": promptText,
      "temperature": 0.7,
      "max_tokens": 150,
      "top_p": 1,
      "frequency_penalty": 0,
      "presence_penalty": 0
    });
    
    var options = {
      "method": "post",
      "contentType": "application/json",
      "payload": payload,
      "headers": {
        "Authorization": "Bearer " + apiKey
      },
      "muteHttpExceptions": true
    };
    
    try {
      var response = UrlFetchApp.fetch(apiURL, options);
      var json = JSON.parse(response.getContentText());
      var resultText = json.choices[0].text.trim();
      
      // Update the next cell in the row with the response
      cell.offset(0, 1).setValue(resultText);
    } catch (e) {
      Logger.log(e.toString());
    }
  } else {
    SpreadsheetApp.getUi().alert('Please select a cell with content.');
  }
}

Step 3: Add Your API Key

Replace 'YOUR_API_KEY' with your actual OpenAI API key.

Step 4: Save and Close the Script Editor

After pasting and modifying the code, save the script and close the editor.

Step 5: Reload Your Spreadsheet

Reload your Google Sheets to see the custom menu (“OpenAI API”) appear.

Using the Script

  1. In your Google Sheet, select a cell that contains the text you want to send to the OpenAI API.
  2. Click on the “OpenAI API” menu and select “Get Response”.
  3. The script will send the cell’s content to the OpenAI Assistant API and populate the next cell in the same row with the response.

Note:

  • This script uses the UrlFetchApp service to make a POST request to the OpenAI API, which requires enabling billing on your Google Cloud Project associated with the Apps Script.
  • The script does not implement conversation or context features; each request is independent.
  • Remember to adhere to OpenAI’s API usage policies and guidelines.

Is that what you were looking for? Let me know if that helped.

Mart

4 Likes