gpt3-in-your-sheets.js

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_

Thank you

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!

Thank you so much !

Very cool!

Merci !!!

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

thanks abi, very useful

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

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

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

This is really awesome, nice work

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.

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

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?

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

Thank you sir

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

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`

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).

why doesn't this work with the curie model?

It's Great. Thank you vary much

Thank you!

@abi , can you explain about CATEGORIZE function?

@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?

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).

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

Sorry I'm not updating this script any longer.

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?

i try this with divinc 002 it not work