- 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:
-
- A few examples of prompts:
=runOpenAI("Normalize company names. Remove legal entity names.
Apple 😎:Apple
Google LLC 🔥:Google
Facebook corp.:Facebook
", E2)
=runOpenAI("Categorize job title by seniority.
CEO:C-Level
Head of Sales:Senior
designer:Entry
", H2)
=runOpenAI("Classify the email response. Can be either 'interested', 'not interested'
I'd love to know more about it:interested
No thank you:not interested
",E19)
=runOpenAI("extract domain from email.Answer 'true' if domain is an email provider or 'false' if not. Don't return domain.
name@gmail.com:true
",K3)
=runOpenAI("Give Country from Address.Return a country code.
4 Priory Close, Birmingham, United Kingdom: GB
12 San Francisco, California, USA: US
24 Rue Notre-Dame, Montreal, Canada: CA
22 Piazza del Popolo, Rome: IT
",A2)
-
- 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
}
-
-
-
