A web browser displaying a code editor with OPEN AI Script integrated into Google Sheets.

Adding OPEN AI Script to Google Sheets

In this blog, we’ll be exploring how to add GPT-3 Open AI Script to Google Sheets, allowing you to quickly and easily incorporate AI into your data analysis. We’ll be covering topics such as setting up your environment, connecting GPT-3 Open AI Script to Google Sheets, and how to use the script in the best way possible. So buckle up and get ready to learn how to add AI to your spreadsheets!
Step By Step

1.

Double-click ‘Untitled spreadsheet’ and give your new blank spreadsheet a new name.

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

2.

Then select ‘Extensions’ from the menu

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

3.

and then click ‘Apps Script’

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

4.

Delete the boilerplate content which is there by default. We won’t be needing that.

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

5.

Next, we paste in the script provided below:

const SECRET_KEY = 'INSERT YOUR API KEY HERE';
const MAX_TOKENS = 200;

/**
 * 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-002".
 * @return Completion returned by GPT-3
 * @customfunction
 */
function AI(prompt, temperature = 0.4, model = "text-davinci-002") {
  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-002");
  // Replace "s and .s at the start and end of the string
  return completion.replace(/^"/g, '').replace(/["|.]{0,2}$/, '');
}
Prompt Muse | A.I News, Tech Reviews and Free Tutorials

6.

Then locate the top section, and paste in your OPEN AI API key in the section which says ‘INSERT YOUR API KEY HERE’ (Note: the key should be inside the single quotes ‘ ‘ )

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

7.

Press SAVE

NOTE: If you do not put your API key between the quotes, it will not work – and a message as such will display when you try to save.

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

8.

Click the ‘Run the selected function’ button

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

9.

It will then request a bunch of permissions. Click review, and accept.

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

11.

Give permission to your profile.

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

12.

As this is a custom script, it will be flagged, but nothing sus here, promise. – You can view the script yourself. It is simply a direct connection to Open API. Click Advanced.

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

13.

Click goto project

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

14.

Click the ‘Allow’ button

Prompt Muse | A.I News, Tech Reviews and Free Tutorials

15.

All done. You can now head back to the google sheet for some fun.

Prompt Muse | A.I News, Tech Reviews and Free Tutorials
Prompt Muse | A.I News, Tech Reviews and Free Tutorials

2 Comments

  1. Thanks for the tutorial. Seems to be great. But how exactly do I use it in Google Sheets then? I’ve never tried those App Scripts and have no clue how to handle them. Thanks!

Leave A Reply

Join The Prompt Muse Gang!

Get the latest A.I News, Reviews, and tutorials, hand-picked by A.I depending on your preference and sent directly to you.

A newsletter featuring two pictures of a man and a woman highlighting 'prompt news vs muse'.

*We promise not to spam, sell or do any other naughty things with your details – simply keep you updated when we launch new awesome tutorials and news.