- Create a new Google Sheets file
- Go to Extensions → Apps Script
- Remove all the code and copy-paste the following instead
- Sign-Up to Open AI https://beta.openai.com/signup
- Once you have an account, get your API key.
- Click on your name in the top-right corner
- Click on view API Keys
- Create new secret key
- Copy the key
- Paste your key inside your Apps Script project.
- Make sure it looks like "sk-2decdcdvfedf"
- Save your project and go back to your sheet
- Use =runOpenAI(), like classic Google Sheets Formula and insert your prompt
The new formula in Google Sheets:
Here's the prompt to classify the job titles by seniority in Google Sheets
=runOpenAI("Categorize job title by seniority.
CEO:C-Level
Head of Sales:Senior
designer:Entry
", H2)
The code to paste in Apps Script inside Google Sheets:
/**
* Generates text using OpenAI's GPT-3 model
* @param {string} prompt The prompt to feed to the GPT-3 model
* @param {string} cell The cell to append to the prompt
* @param {number} [maxWords=10] The maximum number of words to generate
* @return {string} The generated text
* @customfunction
*/
function runOpenAI(prompt, cell, maxWords) {
const API_KEY = "YOUR API KEY";
maxTokens = 10
if (maxWords){maxTokens = maxWords * 0.75}
model = "text-davinci-003"
prompt = prompt+cell+":",
temperature= 0
// Set up the request body with the given parameters
const requestBody = {
"model": model,
"prompt": prompt,
"temperature": temperature,
"max_tokens": maxTokens
};
console.log(requestBody)
// Set up the request options with the required headers
const requestOptions = {
"method": "POST",
"headers": {
"Content-Type": "application/json",
"Authorization": "Bearer "+API_KEY
},
"payload": JSON.stringify(requestBody)
};
// Send the request to the GPT-3 API endpoint for completions
const response = UrlFetchApp.fetch("https://api.openai.com/v1/completions", requestOptions);
console.log(response.getContentText())
// Get the response body as a JSON object
const responseBody = JSON.parse(response.getContentText());
let answer= responseBody.choices[0].text
// Return the generated text from the response
return answer
}
