Using Assistant threads in AppScript for a Google Sheet

API
1

Hi
I have a Open AI Assistant that i have trained and wish to call via AppScript in Google Sheets. I can’t seem to get in touch with the assistant and I am unsure of the correct endpoint for the API and wether or not to use threads.

The assistant has fine tuning setup as well and the functionality for calling this seems to work fine, but i want to use a specific thread for the assistant/the specific assistant with the instructions.

I have tried using several different endpoints with no luck and changing the script around, debugging etc. I feel like its a simple thing i’m missing but i’m unable to make it work as intended.

This is the latest code for the assistant call that does not work.

I wish the script to output data to a column based off of data from two other columns and do this dynamically for new rows added to the Google Sheet. I have a thread setup in the Playground that is outputting 100% the correct response.

const OPENAI_API_KEY = 'API KEY';
const ASSISTANT_ID = 'ASSISTANT ID';  // Your specific assistant ID
const THREAD_ID = 'THREAD ID';  // The thread ID you want to interact with

// Main function to process new rows and call the OpenAI Assistant
function checkForNewRowAndProcess() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();  // Get the last row that has data
  for (let row = 2; row <= lastRow; row++) {
    const topic = sheet.getRange(row, 2).getValue();  // Column B: Topic
    const keywords = sheet.getRange(row, 3).getValue();  // Column C: Keywords
    const outputCell = sheet.getRange(row, 4);  // Column D: Output (Tekst)
    
    // Check if both Topic and Keywords are present and output cell is empty
    if (!outputCell.getValue() && topic && keywords) {
      try {
        Logger.log("Processing row " + row);
        
        // Call the OpenAI Assistant and get the generated text
        const response = callOpenAIAssistant(topic, keywords);

        // Write the generated response to column D (Tekst)
        outputCell.setValue(response);
        Logger.log("Successfully updated row " + row + " with AI-generated text.");
      } catch (error) {
        Logger.log("Error processing row " + row + ": " + error.message);
      }
    }
  }
}

// Helper function to call the OpenAI Assistant API using the assistant ID
function callOpenAIAssistant(topic, keywords) {
  const url = `https://api.openai.com/v1/assistants/{assistant_id}/threads/{thread_id}/completions`;
  
  // Example instructions to simulate assistant behavior
  const instructions = "Please address the user as Jane Doe. The user has a premium account.";
  
  const payload = {
    instructions: instructions,
    messages: [
      {"role": "user", "content": `Generate a text for the topic: ${topic}, using these keywords: ${keywords}`}
    ],
    max_tokens: 1000
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + OPENAI_API_KEY
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true  // Capture full response even in case of errors
  };

  const response = UrlFetchApp.fetch(url, options);
  
  Logger.log("Response from OpenAI: " + response.getContentText());  // Log the full response

  // Parse the response and handle errors
  let jsonResponse;
  try {
    jsonResponse = JSON.parse(response.getContentText());
  } catch (error) {
    Logger.log("Error parsing JSON response: " + error.message);
    throw new Error("Failed to parse JSON response from OpenAI.");
  }

  if (jsonResponse.error) {
    Logger.log("API error: " + jsonResponse.error.message);
    throw new Error(jsonResponse.error.message);  // Handle API errors
  }

  // Extract the generated content from the response
  const generatedText = jsonResponse.choices[0].message.content;
  Logger.log("Generated text: " + generatedText);

  return generatedText;
}

No matter which endpoint i change to i get:
Invalid argument: (https and Domain removed)/v1/assistants/{assistant_id}/threads/{thread_id}/completions

This is the fine tuning one that works.

const OPENAI_API_KEY = 'API KEY';
const FINETUNED_MODEL_ID = 'ft:gpt-4o-2024-08-06:xxxxxxxxxxx';  // fine-tuned model ID

// Main function to process new rows and call OpenAI API
function checkForNewRowAndProcess() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  const lastRow = sheet.getLastRow();  // Get the last row that has data
  for (let row = 2; row <= lastRow; row++) {
    const topic = sheet.getRange(row, 2).getValue();  // Column B: Topic
    const keywords = sheet.getRange(row, 3).getValue();  // Column C: Keywords
    const outputCell = sheet.getRange(row, 4);  // Column D: Output (Tekst)
    
    // Check if both Topic and subtopics are present and output cell is empty
    if (!outputCell.getValue() && topic && keywords) {
      try {
        Logger.log("Processing row " + row);

        // Call OpenAI API to generate content
        const response = callOpenAIAPI(topic, keywords);

        // Write the generated response to column D (text)
        outputCell.setValue(response);
        Logger.log("Successfully updated row " + row + " with AI-generated text.");
      } catch (error) {
        Logger.log("Error processing row " + row + ": " + error.message);
      }
    }
  }
}

// Helper function to call OpenAI API using a fine-tuned model
function callOpenAIAPI(topic, keywords) {
  const url = 'https://api.openai.com/v1/chat/completions';  // Correct endpoint
  
  const prompt = `Topic: ${topic}\nKeywords: ${keywords}\nGenerate a text based on these inputs.`;
  
  const payload = {
    model: FINETUNED_MODEL_ID,  // Use the fine-tuned model ID here
    messages: [
      {"role": "system", "content": "You are an assistant trained to generate texts."},
      {"role": "user", "content": prompt}
    ],
    max_tokens: 1000  // You can adjust this based on your requirements
  };

  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + OPENAI_API_KEY
    },
    payload: JSON.stringify(payload),
    muteHttpExceptions: true  // Capture full response even in case of errors
  };

  const response = UrlFetchApp.fetch(url, options);
  
  Logger.log("Response from OpenAI: " + response.getContentText());  // Log full response for debugging

  // Parse the response and handle errors
  let jsonResponse;
  try {
    jsonResponse = JSON.parse(response.getContentText());  // Safely parse the JSON
  } catch (error) {
    Logger.log("Error parsing JSON response: " + error.message);
    throw new Error("Failed to parse JSON response from OpenAI.");
  }

  if (jsonResponse.error) {
    Logger.log("API error: " + jsonResponse.error.message);
    throw new Error(jsonResponse.error.message);  // Handle API errors
  }

  const generatedText = jsonResponse.choices[0].message.content;

  Logger.log("Generated text: " + generatedText);

  return generatedText;
}

Disclaimer: I am not a developer and i use Replit / ChatGPT / Claude for all coding purposes. :slight_smile:
Hope somebody can help me out!

2 Likes