Skip to content

Instantly share code, notes, and snippets.

@eshelman
Last active April 1, 2023 15:18
Show Gist options
  • Save eshelman/e4220bc3dee09c5abc01f5f8a983774f to your computer and use it in GitHub Desktop.
Save eshelman/e4220bc3dee09c5abc01f5f8a983774f to your computer and use it in GitHub Desktop.
Function for Google's AppScripts inside of Sheets - call OpenAI ChatGPT API
//
// 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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment