In this blog, we’ll be exploring how to add GPT-3 Open AI Script to Google Sheets, allowing you to quickly and easily incorporate AI into your data analysis. We’ll be covering topics such as setting up your environment, connecting GPT-3 Open AI Script to Google Sheets, and how to use the script in the best way possible. So buckle up and get ready to learn how to add AI to your spreadsheets!
Step By Step
1.
Double-click ‘Untitled spreadsheet’ and give your new blank spreadsheet a new name.
2.
Then select ‘Extensions’ from the menu
3.
and then click ‘Apps Script’
4.
Delete the boilerplate content which is there by default. We won’t be needing that.
5.
Next, we paste in the script provided below:
const SECRET_KEY = 'INSERT YOUR API KEY HERE';
const MAX_TOKENS = 200;
/**
* Completes your prompt with GPT-3
*
* @param {string} prompt Prompt
* @param {number} temperature (Optional) Temperature. 1 is super creative while 0 is very exact and precise. Defaults to 0.4.
* @param {string} model (Optional) GPT-3 Model to use. Defaults to "text-davinci-002".
* @return Completion returned by GPT-3
* @customfunction
*/
function AI(prompt, temperature = 0.4, model = "text-davinci-002") {
const url = "https://api.openai.com/v1/completions";
const payload = {
model: model,
prompt: prompt,
temperature: temperature,
max_tokens: MAX_TOKENS,
};
const options = {
contentType: "application/json",
headers: { Authorization: "Bearer " + SECRET_KEY },
payload: JSON.stringify(payload),
};
const res = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
return res.choices[0].text.trim();
}
/**
* Classifies an item into a fixed set of categories
* @param {range} categories Set of categories
* @param {string} item Item to classify
* @param {range} rules (Optional) Set of rules written in plain text
* @return Completion returned by GPT-3
* @customfunction
*/
function CATEGORIZE(categories, input, rules=[]) {
const prompt = "The available categories are " + categories.map((c) => `"${c}"`).join(", ") + ". " + rules.join(". ") + "The category for '" + input + "' is ";
console.log(prompt);
const completion = AI(prompt, 0, "text-davinci-002");
// Replace "s and .s at the start and end of the string
return completion.replace(/^"/g, '').replace(/["|.]{0,2}$/, '');
}
6.
Then locate the top section, and paste in your OPEN AI API key in the section which says ‘INSERT YOUR API KEY HERE’ (Note: the key should be inside the single quotes ‘ ‘ )
7.
Press SAVE
NOTE: If you do not put your API key between the quotes, it will not work – and a message as such will display when you try to save.
8.
Click the ‘Run the selected function’ button
9.
It will then request a bunch of permissions. Click review, and accept.
11.
Give permission to your profile.
12.
As this is a custom script, it will be flagged, but nothing sus here, promise. – You can view the script yourself. It is simply a direct connection to Open API. Click Advanced.
13.
Click goto project
14.
Click the ‘Allow’ button
15.
All done. You can now head back to the google sheet for some fun.
