GPT-3 and Google Sheets

ChatGPT + Sheets usage (advanced):

1. You’ll need an OpenAI account.
2. Create a new Google Sheet (you can use https://sheets.new).
3. Extensions > Apps Script.
4. Overwrite all text in the text box by pasting all the code below.
5. Retrieve your new secret key (sk-xxx) from https://platform.openai.com/account/api-keys
6. Paste your new secret key (sk-xxx) into the script as indicated.
7. Click Save and Run icons. Authorise the application if needed.
8. Close the script window. Back in your new sheet, as an example, type “France” into A1. Then type this formula into B1: =IF(ISBLANK(A1),,GPT("The capital of "&A1&" is (name only):"))

OR: Use my new Sheets template:
2023-ChatGPT-Sheets-Template-For-Workshops

/**
* ChatGPT and Google Sheets
* https://lifearchitect.ai/sheets/
* Revision 20230305 valid until 2024 subject to OpenAI API and model changes
*
* @param {string} prompt Prompt.
* @param {number} temperature (Optional) Temperature.
* @param {string} model (Optional) GPT-3 chat Model.
* @return Response returned by GPT-3 chat.
*/

const SECRET_KEY = "sk-paste-your-API-secret-key-here";
const MAX_TOKENS = 10; // around 40 characters, increase for more output, with increasing $
const MODEL_NAME = "gpt-3.5-turbo"; // ChatGPT
const MODEL_TEMP = 0.3;

function GPT(prompt, temperature = MODEL_TEMP, model = MODEL_NAME) {
  const url = "https://api.openai.com/v1/chat/completions";
  const payload = {
    model: model,
    messages: [
      { role: "system", content: "" }, // blank priming prompt for lower token count
      { role: "user", content: "" + 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].message.content.trim();
}

1/Mar/2023: Updated to use ChatGPT. As of 1/Mar/2023, the script above uses OpenAI’s new ChatGPT as gpt-3.5-turbo via API for this functionality using the new chat completion endpoint., with a cost 10x lower than standard text-davinci-003. The original script using the older model is below, and could be adapted to use GPT-4.

/**
* GPT-3 and Google Sheets
* https://lifearchitect.ai/sheets/
*
* @param {string} prompt Prompt.
* @param {number} temperature (Optional) Temperature.
* @param {string} model (Optional) GPT-3 Model.
* @return Response returned by GPT-3.
* @customfunction
*/

const SECRET_KEY = "sk-paste-your-API-secret-key-here";
const MAX_TOKENS = 10;
const MODEL_NAME = "text-davinci-003"; // more structured and deterministic: for data
//const MODEL_NAME = "davinci"; // more flexible and creative: for stories, chatbots
const MODEL_TEMP = 0.3;

function GPT(prompt) {
  const url = "https://api.openai.com/v1/completions";
  const payload = {
    model: MODEL_NAME,
    prompt: prompt,
    temperature: MODEL_TEMP,
    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();
}