For a basic crash course on ChatGPT, try: The ChatGPT Prompt Book
ChatGPT + Sheets usage (advanced):
- You’ll need an OpenAI account.
- Create a new Google Sheet (you can use https://sheets.new).
- Extensions > Apps Script.
- Overwrite all text in the text box by pasting all the code below.
- Retrieve your new secret key (sk-xxx) from https://platform.openai.com/account/api-keys
- Paste your new secret key (sk-xxx) into the script as indicated.
- Click Save and Run icons. Authorise the application if needed.
- 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"; // ChatGPTconst 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.
View the older code for text-davinci-003 (10x more expensive)
/*** 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, chatbotsconst 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();}