Skip to content

Instantly share code, notes, and snippets.

@abi
Last active March 28, 2024 05:00
Show Gist options
  • Save abi/94ffd06cf654038c75f56ff59ef2f086 to your computer and use it in GitHub Desktop.
Save abi/94ffd06cf654038c75f56ff59ef2f086 to your computer and use it in GitHub Desktop.
const SECRET_KEY = ENTER YOUR SECRET KEY HERE;
const MAX_TOKENS = 200;
// For more cool AI snippets and demos, follow me on Twitter: https://twitter.com/_abi_
/**
* 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-003".
* @return Completion returned by GPT-3
* @customfunction
*/
function AI(prompt, temperature = 0.4, model = "text-davinci-003") {
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-003");
// Replace "s and .s at the start and end of the string
return completion.replace(/^"/g, '').replace(/["|.]{0,2}$/, '');
}
// For more cool AI snippets and demos, follow me on Twitter: https://twitter.com/_abi_
@fernandolangaro
Copy link

Awesome!

@yanwangds1
Copy link

awesome!

@charanquartz
Copy link

Great!

@rapprou
Copy link

rapprou commented Jan 19, 2023

Awesome!

@doxaclast
Copy link

Thank you

@laurentbroering
Copy link

Hi @abi , do you know how we could adapt this code to allow something like this seen in this video? The user seems to add an example array in the first parameter and then a second array to be completed in the second parameter. Thank you so much!

@eiko64
Copy link

eiko64 commented Jan 27, 2023

Thank you so much !

@A2CProductions
Copy link

Very cool!

@KevinlbSEO
Copy link

Merci !!!

@abi
Copy link
Author

abi commented Jan 30, 2023

@laurentbroering yup, shouldn't be hard at all. Just need to modify the prompt we are sending to OpenAI.

@Alireza-Naji
Copy link

thanks abi, very useful

@karimjaved1988
Copy link

Getting some sort of error in running this script if someone can please help.

Reference Error: sk is not defined
(anonynous) @ Code:gs 1

@mattprice1
Copy link

Getting some sort of error in running this script if someone can please help.

Reference Error: sk is not defined (anonynous) @ Code:gs 1

Same

@atj503
Copy link

atj503 commented Feb 1, 2023

This is really awesome, nice work

@tualder
Copy link

tualder commented Feb 4, 2023

Getting some sort of error in running this script if someone can please help.

Reference Error: sk is not defined (anonynous) @ Code:gs 1

Thank sir @abi, but I have the same issue after type the command on GG Spreadsheets. Please check it.
2023-02-04-104900.png

@rupam2234
Copy link

For those getting sk is not defined error, try putting it as a string, like "sk-*******************************"

@Patrykchylinski
Copy link

brilliant project! I have one problem with categorizing. When I type =CATEGORIZE($A$38:$A$41;A2) I get TypeError: rules.join is not a function (line 41). What can I do?

@tualder
Copy link

tualder commented Feb 13, 2023

For those getting sk is not defined error, try putting it as a string, like "sk-*******************************"

Thank you sir

@djmystica
Copy link

amazing!
is there a way to get more than 10 rows when getting the results?
thanks!

@camilus667
Copy link

add a new ChatGPT account and it comes out like this

15:45:48 Error
Exception: Request failed for https://api.openai.com returned code 429. Truncated server response: {
"error": {
"message": "You exceeded your current quota, please check your plan and billing details.",
"type": "insufficient_q... (use muteHttpExceptions option to examine full response)
AI @ Código.gs:28`

@mkritz
Copy link

mkritz commented Mar 8, 2023

Using your script with the new model gpt-3.5-turbo throws the following exception. Also, the completion URL needs to be changed to /v1/chat/completions from /v1/completions

Any plans to update /extend your code? Thanks!

Exception: Request failed for https://api.openai.com returned code 400. Truncated server response: { "error": { "message": "'messages' is a required property", "type": "invalid_request_error", "param": null, "code": null } } (use muteHttpExceptions option to examine full response) (line 32).

@akylaaa
Copy link

akylaaa commented Mar 11, 2023

why doesn't this work with the curie model?

@malkasun
Copy link

It's Great. Thank you vary much

@vascosommernunes
Copy link

Thank you!

@malkasun
Copy link

@abi , can you explain about CATEGORIZE function?

@ianlloydawid
Copy link

@abi, I modified the prompt parameter inside function AI by manually inputting a string value but somehow gpt's responses does not take this into account. How should I modify the prompt to be sent to openAI correctly?

@georg-che
Copy link

add a new ChatGPT account and it comes out like this

15:45:48 Error Exception: Request failed for https://api.openai.com returned code 429. Truncated server response: { "error": { "message": "You exceeded your current quota, please check your plan and billing details.", "type": "insufficient_q... (use muteHttpExceptions option to examine full response) AI @ Código.gs:28`

Hello!
I have the same error.
How did you fix this error?
Could you please tell me?

Exception: Request failed for https://api.openai.com returned code 429. Truncated server response: { "error": { "message": "Rate limit reached for text-davinci-003 in organization org-2QaMd1zCxnHUJbSJOvatW139 on requests per min. Limi... (use muteHttpExceptions option to examine full response) (строка 28).

@Shnw202020
Copy link

Will this still work in January, or will it be discontinued? Will you upgrade it? Thanks.

@abi
Copy link
Author

abi commented Dec 6, 2023

Sorry I'm not updating this script any longer.

@Shnw202020
Copy link

Sorry I'm not updating this script any longer.

Will it still work if we just change the model name to a new one? Are you doing freelance work, how can I reach out to if you are?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment