Step 1: Go to the sign-up page

Go to the sign-up page. You might need to solve a captcha to prove that you are human.

Step 2: Enter your email or use a Google or a Microsoft account

Type in your email or use a Google or Microsoft account to sign up.

Step 3: Fill in your name

After the sign-up process, during which you might need to verify your email, you can add your first name, last name, and organization name.

Step 4: Verify a phone number

To prevent abuse, OpenAI requires you to add a phone number to your account. If you prefer not to use your personal number, you can use a free virtual number, to verify your account instead.

Step 5: Enter the code

After you get a text message with the verification code, add it to the OpenAI registration page.

Step 6: Go to your profile > View API Keys

Once you add the verification code, you should be logged in automatically. Click on your avatar and click on “View API Keys.”

Step 7: Generate an API key

Click on “Create new secret key” and store this information in a safe place. This API key is shown only once, so if you don’t copy it, you’ll need to delete it and generate a new one.

Step 8: Check if you have credits

Go to “Usage” and double-check if you have any free trial credits. If you don’t have any, you’ll need to add a payment method to continue.

Step 1: Create a new Google Sheet

Create a new sheet, or open an existing sheet, in case you want to add AI to an existing file. You can visit the https://sheets.new URL to create a new Google Sheet directly.

Step 2: Create headers

Create the main headers to help you to organize your content. You can follow this structure:

Step 3: Add the setup options

Add some default values for the setup options and costs. Here is a short description along with the recommended default values for each:

• Temperature: Default: 0.7. Range: 0 to 2. Defines how “hot” or random the responses will be. With 0, you should expect the same response every time, with 1 you should expect similar replies with different wordings, and with 2 you can expect very different replies with the same prompt.
• Maximum length: Default 256. Range: 0 to infinity. The maximum number of tokens to use. More tokens usually means better, longer and more complex responses.
• Top P: Default 1. Range 0 to 1. Selects the top percentage of the sampling pool that will be used. So if you use 0.3, for example, only the top 30% of the sampling pool is considered for the replies.
• Frequency penalty: Default: 0. Range: -2 to 2. Positive values penalize repeated words, meaning that it is less likely to get the same lines verbatim.
• Presence penalty: Default 0. Range: -2 to 2. Positive values penalize current tokens that have already appeared, meaning that you will probably get new topics further down the line.

The “Tokens used” section is just a sum of all tokens in the chats section, with the formula =SUM(F:F)+SUM(G:G)

The “Price” cell just gets this value and converts it to dollars, like this:

=B10*0.002/1000

Step 4: Set up the chats section

The chats section consists of a prompt and a reply, along with the usage tokens. But on the main sheet, users can type down only the prompt. The response and the tokens count comes from the raw_chats sheet.

You can load them with these formulas:

=raw_chats!A2

=raw_chats!B2

=raw_chats!C2

You can copy and paste the first row until the end of your file, to make sure that this formula is there for a lot of prompts with the right line number (a2, a3, a4…).

Step 5: Set up the raw_chats sheet

Create a new sheet for raw_sheets. Copy this structure in it:

Notice that the prompt in this case is a formula:

=GPT!D5

Make sure to copy this formula over to as many cells as possible, so they will auto-populate with the user’s prompts.

Step 6: Add the “run” button

You need a button, so that you can click on it to run the API call. Go to Insert > Drawing, create your button and insert it on the main sheet:

Step 7: Add the buttonGPT function to the button

Click on the three dots for the button and add the buttonGPT function to it.

Step 1: Open the Apps Script editor

Go to Extensions > Apps Script to see the script editor:

Step 2: Paste the code

Replace the current function with this code:

/**
* Return a ChatGPT response from a given prompt, along with the request tokens and prompt tokens
* @constructor
* @param {string} prompt - the request prompt to be processed by ChatGPT
* @param {boolean} returnUsage [OPTIONAL] - If the return values should include the usage token or just the prompt answer - Default is true
* @param {number} temperature [OPTIONAL] - How random the responses will be from 0 to 2 - Default is 0.7
* @param {integer} maxLeght [OPTIONAL] - The maximum number of tokens used to generate a response - Default is 256
* @param {number} topP [OPTIONAL] - Get the top X% of the sampling pool from 0 to 1 - Default is 1
* @param {number} frequencyPenalty [OPTIONAL] - Positive values make it less likely to repeat the same lines verbatim, from -2 to 2 - Default is 0
* @param {number} presencePenalty [OPTIONAL] - Positive values make it more likely to talk about new topics, from -2 to 2 - Default is 0
* @customfunction
*/
function chatGPT( prompt = "Hello!", returnUsage = true, temperature = 0.7, maxLeght = 256, topP = 1, frequencyPenalty = 0, presencePenalty = 0) {
 //initialize the return array
 var contents = [];
 //use your API Key here
 const apiKey = 'ADD YOUR API KEY HERE’;
 //change the API URL here if you want to use a different API
 const url = 'https://api.openai.com/v1/chat/completions';
 //change the model here
 const model = 'gpt-3.5-turbo';


 //request along with the parameters
 const request = {
   model: model,
   messages: [{"role": "user", "content": prompt}],
   temperature: temperature,
   top_p: topP,
   max_tokens: maxLeght,
   frequency_penalty: frequencyPenalty,
   presence_penalty: presencePenalty
 };


 //headers
 const data = {
   contentType: "application/json",
   headers: { Authorization: "Bearer "+ apiKey },
   payload: JSON.stringify(request),
 };


 //connect to the OpenAI API, sending the headers and parse the JSON response
 const response = JSON.parse(UrlFetchApp.fetch(url, data).getContentText());


 //add the response message to the return array
 contents.push( response.choices[0].message.content );


 if ( returnUsage ) {
   //if the return usage is enabled, return the promtp_tokens and completion_tokens as well
   contents.push(response.usage.prompt_tokens);
   contents.push(response.usage.completion_tokens);
 }


 return contents;
}


function buttonGPT() {
 //select a file
 const ss = SpreadsheetApp.getActiveSpreadsheet();


 //selecting each of the sheets in a variable
 const raw = ss.getSheetByName('raw_chats');
 const main = ss.getSheetByName('GPT');


 //get the last populated rown
 const lastRow = raw.getLastRow();


 //generate the formula used on the prompt copy column for the raw sheet
 var formula = lastRow + 3;
 var formula = '=GPT!D' + formula;


 //get settings
 const temperature = main.getRange(4,2).getValue();
 const maxLeght = main.getRange(5,2).getValue();
 const topP = main.getRange(6,2).getValue();
 const frequencyPenalty = main.getRange(7,2).getValue();
 const presencePenalty = main.getRange(8,2).getValue();
 const prompt = raw.getRange(lastRow,4).getValue();


 //remove the last prompt
 raw.getRange(lastRow,4).clear();


 //connect with OpenAI
 var contents = chatGPT( prompt, true, temperature, maxLeght, topP, frequencyPenalty, presencePenalty);


 //add a new row with the response contents
 raw.appendRow(contents);


 //add the last prompt formula back
 raw.getRange(lastRow,4).setFormula(formula);


 return 1;
}

‍

The logic behind this code is:

• chatGPT() - take the parameters, build the POST request, send it. Returns either the prompt response or the response along with the token usage.
• buttonGPT() - this function gets the current sheets, finds the last row, reads the prompt, deletes the cell with the prompt values, connects to ChatGPT(), writes the response.

You can use one of these models: gpt-4, gpt-4-0314, gpt-4-32k, gpt-4-32k-0314, gpt-3.5-turbo, gpt-3.5-turbo-0301

Step 3: Save and run these functions

Once you save/run these functions, you’ll see an authorization request that looks like this:

Make sure you authorize it.

Step 4: Log in to your Google Account

After you click on “Review permissions,” you need to log in to your Google account to authorize it.

Step 5: Authorize the unverified app.

It’s likely that your app isn’t verified. Make sure you select to ignore the warnings and use your script.

Click on “Advanced”

Then click on Go to ChatGPT Connection by Softr (or the equivalent name for your script).

You still might need to authorize the connection on your Google Account.

Step 1: Write down your prompt

Write down anything you want ChatGPT to do on the first empty line.

Step 2: Make sure to hit enter

Apps Script runs on Google’s servers. So make sure you hit “enter” or select a different cell so the prompt value is correctly saved and propagated in your sheet.

Step 3: Click run

Click the “Run” button and wait a little bit. You should see a message about the script running, and then the results.

(Optional) Step 4: Use the chatGPT() function directly

If you want, you can add the chatGPT() function to a cell directly, passing the prompt and any of the arguments you want.

The downside of this approach is that the API call runs every time you load your Google Sheet, which might load significantly different answers and will consume API credits on each load.