Script for adding a chatGPT formula in Google Sheet

gistfile1.txt

// Constants
const API_KEY = "YOUR API KEY";
const MODEL_TYPE = "gpt-3.5-turbo"; //chatGPT model
const MAX_TOKENS = 4096;

function CHATGPT(promptCellRef, contentRangeRef) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Get the prompt from the specified cell
  const promptCell = sheet.getRange(promptCellRef);
  const prompt = promptCell.getValue();

  // Append additional content from the specified range
  const contentCells = sheet.getRange(contentRangeRef);
  const contentValues = contentCells.getValues().flat().filter(value => value !== '');
  const appendedPrompt = prompt + " " + contentValues.join(" ");

  // Check if the total tokens are within the limit
  const tokenCount = appendedPrompt.length; // A simple character count can be used as a rough estimation of token count

  if (tokenCount > MAX_TOKENS) {
    const truncatedPrompt = appendedPrompt.substring(0, MAX_TOKENS);
    return callGPT(truncatedPrompt);
  } else {
    return callGPT(appendedPrompt);
  }
}

function callGPT(prompt) {
  const temperature = 0;
  const maxTokens = 2050;

  const requestBody = {
    model: MODEL_TYPE,
    messages: [{role: "user", content: prompt}],
    temperature,
    max_tokens: maxTokens,
  };

  const requestOptions = {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: "Bearer " + API_KEY,
    },
    payload: JSON.stringify(requestBody),
  };

  const response = UrlFetchApp.fetch("https://api.openai.com/v1/chat/completions", requestOptions);
  const responseText = response.getContentText();
  const json = JSON.parse(responseText);
  const generatedText = json['choices'][0]['message']['content'];
  
  return generatedText;
}