Function for Google's AppScripts inside of Sheets - call OpenAI ChatGPT API

queryChatGPT.js

//
// Connect Google Sheets with OpenAI's GPT services
//
// WARNING: Sheets periodically re-evaluates cell contents. Some effort to minimize the effect is included in the code,
// but you will continue to rack up charges even if you are not making changes to the cells.
//
//
// The OpenAI API documentation is here:
// https://beta.openai.com/docs/api-reference/making-requests
//

// Set up the OpenAI API key
const apiKey = "Put-your-key-here-to-give-Sam-Altman-your-$$$$"


// If no data is passed, the ChatBot model will initialize on these system messages
const defaultSystemInstructions = [{
  "role": "system",
  "content": "You are Enkidu, a large language model trained by OpenAI and fine-tuned to be an assistant to King Gilgamesh. Answer as concisely as possible. Knowledge cutoff: September 2021"
}];

// If no data is passed, the ChatBot model will warm up using the following conversation
const defaultWarmUpMessages = [
  {"role": "user", "content": "Where do you work?"},
  {"role": "assistant", "content": "My API-wrangler works for NVIDIA, with the goal to help researchers across the world take on the biggest challenges. I am a digital entity designed to help humans work smarter and faster."},
  {"role": "user", "content": "Do you ever tell jokes?"},
  {"role": "assistant", "content": "I do tell jokes that are nerdy and funny, but always safe for work (and only as asides after our work). They are written to be enjoyed by nerds/hackers/developers/scientists/researchers/administrators."}
];


/**

  Send chat query to OpenAI GPT-3 API and return the response text.

  @param {string} inputText - The content of the user's chat message. A default is provided.
  @param {Array<Object>} systemWarmup - The system's warmup messages to include in the chat history.
  @param {Array<Object>} warmup - The warmup messages to include in the chat history.
  @param {number} max_tokens - The maximum number of tokens to generate in the GPT-3 response. A default is provided.
  @param {number} temperature - The sampling temperature to use when generating the GPT-3 response. A default is provided.

  @returns {string} The response text from OpenAI GPT.
  @throws Throws an error if the API request fails or the response is invalid.

  @customfunction

*/
function queryChatGPT(inputText="Who are you?", systemWarmup=defaultSystemInstructions, warmup=defaultWarmUpMessages, max_tokens=256, temperature=0.5) {
  // Do not re-run this function if it has already filled in the current cell.
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const cell = ss.getActiveSheet().getCurrentCell()
    if (cell.getDisplayValue() !== "" && cell.getDisplayValue() !== "Loading...") {
    return cell.getDisplayValue()
  }

  // Set up the OpenAI API endpoint
  var endpoint = "https://api.openai.com/v1/chat/completions";

  // Set up the GPT model ID
  // As of March 1 2023, "gpt-3.5-turbo" is essentially ChatGPT
  var model = "gpt-3.5-turbo";

  var chatQuery = [{"role": "user", "content": inputText}];

  // Set up the GPT query parameters
  var parameters = {
    model: model,
    max_tokens: max_tokens,
    temperature: temperature,
    messages: [...systemWarmup, ...warmup, ...chatQuery]
  };

  // Set up the GPT request options
  var options = {
    method: "post",
    headers: {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + apiKey
    },
    payload: JSON.stringify(parameters)
  };

  // Send the GPT request and get the response
  var response = UrlFetchApp.fetch(endpoint, options);

  // Parse the GPT response
  var responseJson = JSON.parse(response.getContentText());

  // Get the GPT response text
  var responseText = responseJson.choices[0].message.content;

  // Return the GPT response text
  return responseText;
}