1

Nowadays there are a lot of posts using the word ChatGPT but many of them use it as a common noun to refer to GPT-3 and some chatbots, not to specically make refence to https://chat.openai.com/chat / ChatGPT, the chatbot released by OpenAI in November 2022, or make a brief mention of it but center all the discussion in other things.

I.E. Some posts mentions that for using ChatGPT in code we should use Open AI API. For the specific case of Google Sheets we could use it by using the UrlFetch Service of Google Apps Script to create a custom function among other.

Here is a sample custom function that uses OpenAI API to send a prompt and get response:

/**
 * @param {string} prompt Prompt. Official guide for prompt design https://beta.openai.com/docs/guides/completion/prompt-design
 * @param {string} apiKey OpenAI API key. Get yours from https://beta.openai.com/account/api-keys
 * @customformula
 */
function getCompletion(prompt,apiKey){
  const base = 'https://api.openai.com/v1/';
  const maxTokens = 200;
  const temperature = 0;
  const url = base + 'completions';
  const options = {
    method: 'POST',
    headers: {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + apiKey
      },
    payload: JSON.stringify({
     model: 'text-davinci-003',
     prompt: prompt,
     max_tokens: maxTokens,
    temperature: temperature
    }),
  
  };
  const response = UrlFetchApp.fetch(url, options);
  const content = response.getContentText();
  const code = response.getResponseCode();
  if(code >= 200 && code < 300) {
    /**
     * TODO: Improve response handling
     */
    const output = JSON.parse(content).choices[0].text;
    return output.slice(0,50000);
  } else {
    console.error(content);
    return new Error(content.slice(0,300));
  }
}

Usage

A
1 =getCompletion("What is the capital of France","put here your OpenAI API key")

One of the problems with this script is that it's only calling one end-point, https://api.openai.com/v1/completions. Looking at the Chrome Developers Tools using https://chat.openai.com/chat it looks that there several end-points called. How to adapt this script use the end-points used by ChatGPT and make it work closer as a chat?

Let say that the converstion will be logged in a single column use one cell for the prompt and put the ChatGPT response inmediately below, then the next prompt will be added below and so on.

Related

Resources

1 Answer 1

1

From ChatGPT FAQ (emphasis mine)

How can I implement this? Is there any implementation guide for this?

ChatGPT is being made available as a research preview so we can learn about its strengths and weaknesses. It is not available in the API.

ChatGPT, the chatbot / web app launched by OpenAI a couple of months ago, is no just a front end for calling an end-point, it uses a set of models that OpenAI refer to them as GPT-3.5 that are not available to developers yet. Some developers offer a ChatGPT API have used reverse engineering or use tools like Puppeter which is beyond of what could be done by end-users and might violate the terms of use.

I.E. One of the features of ChatGPT is that it shows a list of the previous conversations that the user had with ChatGPT. There isn't and official end-point to retrieve this list of conversations.


Regarding adapting the above script to work similar as a chat, instead of usign a custom function, use a on edit trigger. In order to avoid the simple trigger limitations like 30 seconds maximum execution time it's better to use an installable trigger but for testing and simple prompts a simple trigger might work.

Additionally you might want to story the OpenAI API key using the Properties Services. If you plan to share the spreadsheet with someone else, use the a dialog to get the key from the user and store it using the User Properties store.

Also you might want to spend some time looking at the sample apps available at https://beta.openai.com/examples.

Resources

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.