google-sheet-chatgpt-api.js

// Add ChatGPT Menu

const onOpen = () => {
const ui = SpreadsheetApp.getUi();
ui.createMenu("ChatGPT")
  .addItem("💾 Save Responses as Text", "saveAsText")
  .addItem("ChatGPT API Sheet by Sarah Tamsin 💜", "openUrl")
  .addToUi();
};

const openUrl = () => {
  const url = "https://sarahtamsin.com";
  const html = "<script>window.open('" + url + "', '_blank');google.script.host.close();</script>";
  const ui = HtmlService.createHtmlOutput(html);
  SpreadsheetApp.getUi().showModalDialog(ui, "Redirecting...");
};

function saveAsText(){

// Saves formulas as plain text

var ss = SpreadsheetApp.getActiveSheet()
var data = ss.getDataRange().getValues()
ss.getRange(1,1,data.length,data[0].length).setValues(data)

}

function CHAT(val) {
// main function to generate the custom formula (=CHAT)
var ss = SpreadsheetApp.getActiveSheet()

// EITHER get API key from settings sheet
// var setsh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Settings");
// var apiKey = setsh.getRange(3,1).getValue() //Cell A3

// OR set API key
var apiKey = YOUR_API_KEY_HERE

// configure the API request to OpenAI
var data = {
  "messages": [
    {"role": "system", "content": "You are an experienced content writer with high levels of expertise and authority. Your job is to write content that will be published online on websites. [Your writing style is friendly, conversational using informal grammar and sometimes non-standard English - as if you're talking to a friend, while incorporating rhetorical questions, storytelling, metaphors and analogies.] I will provide you with a topic or series of topics and you will come up with an engaging and educational copy for this topic."},

    {"role": "user", "content": "Here is your prompt:" +val +"\n"}],
  "model": "gpt-3.5-turbo",
};

   var options = {
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(data),
    'headers': {
      Authorization: 'Bearer ' + apiKey,
    },
  };

var response = UrlFetchApp.fetch(
  'https://api.openai.com/v1/chat/completions',
  options
);

Logger.log(response.getContentText());

 // Send the API request 
 var result = JSON.parse(response.getContentText())['choices'][0]['message']['content'];
 Logger.log(result)

   return result

}