GPT on Google Sheet Free with Apps Script

Usually when looking for GPT integration in Google Sheet we encounter several options available in extension stores, only that the vast majority are paid and expensive. Thinking about this, we will teach you how you can install a Free Apps Script to use your GPT API in formula on your spreadsheets.

Advantages of GPT integration with Google Sheets

A. Personalization and Flexibility: Unlike standard extensions, GPT integration allows you to create customized solutions to meet specific needs.This includes data analysis, report generation, repetitive task automation and natural language processing.

B. Economy of time and efficiencyGPT can automate tasks that would normally take a lot of time, such as compiling data from different sources, writing data summaries, and generating insights from complex data sets.

C. Continuous improvementThe GPT model is constantly evolving, receiving updates and improvements that expand its capabilities and efficiency.

Extension x Personalized script

Extensions often offer more options, a custom UI with an interface that is easy to access and configure. The big problem is the price used to perform something simple, consult the GPT.

Making a custom script in addition to saving money, will allow you to customize, place standardised instructions, create different formulas that meet your needs.

The big problem is that using the Apps Scripts Script even with the help of chatGPT can still cause errors.

How to integrate GPT into Google Sheets

Integrating GPT into Google Sheets involves a few technical steps, including:

A. Configuration of API: First, you need to gain access to the OpenAI GPT API. This usually involves creating an account and obtaining an API key.

B. Use of Google Apps Script: Google Apps Script, a JavaScript-based scripting platform for automation in G Suite, can be used to integrate GPT into Google Sheets. This involves writing scripts that make calls to the GPT API and process responses.

C. Automation and Customization: With the script running, you can automate tasks, such as text generation, data analysis, and other features, directly in Google Sheets.

Apps Script Code to Use GPT in Sheet

Below I share the code I use to use a simple formula like GPT=("promt";CE1)Using this Sheet formula I can use different cells to generate content in my tables.

You can also customize and modify the code, create different formulas that perform different functions. With the help of the GPT chat you will be able to use the OpenAI API on Google Sheet for free.

var apiKey = 'SUACHAVEDEAPI';

function GPT() {
  var messages = [];
  for (var i = 0; i < arguments.length; i++) {
    var arg = arguments[i];
    if (typeof arg === 'string' && arg.trim() !== '') {
      messages.push({ 'role': 'user', 'content': arg.trim() });
    } else if (Array.isArray(arg)) {
      arg.forEach(function(cellValue) {
        if (typeof cellValue === 'string' && cellValue.trim() !== '') {
          messages.push({ 'role': 'user', 'content': cellValue.trim() });
        }
      });
    }
  }

  if (messages.length === 0) {
    return 'Nenhuma mensagem válida fornecida.';
  }

  var url = 'https://api.openai.com/v1/chat/completions';
  var headers = {
    'Authorization': 'Bearer ' + apiKey,
    'Content-Type': 'application/json'
  };

  var payload = {
    'model': 'gpt-3.5-turbo-1106', // Especificar o modelo desejado
    'messages': messages,
    'max_tokens': 700 // Configuração flexível do número máximo de tokens
  };

  var options = {
    'method': 'post',
    'headers': headers,
    'payload': JSON.stringify(payload),
    'muteHttpExceptions': true // Importante para tratar erros
  };

  try {
    var response = UrlFetchApp.fetch(url, options);
    var result = JSON.parse(response.getContentText());

    if (response.getResponseCode() === 200 && result.choices && result.choices.length > 0) {
      return result.choices[0].message.content; // Acessa o conteúdo da mensagem corretamente
    } else {
      Logger.log('Erro na resposta da API: ' + response.getContentText());
      return 'Erro na resposta da API.';
    }
  } catch (e) {
    Logger.log('Erro ao fazer a requisição para a API: ' + e.message);
    return 'Erro ao fazer a requisição para a API.';
  }
}

How to Install Code in Apps Script

To integrate OpenAI’s GPT with Google Sheets, you will need to use Google Apps Script, which allows you to automate tasks and interact with external APIs such as that of OpenAI. Below is a step-by-step tutorial on how to install the provided code and use a custom formula in Google Sheets to interact with GPT.

Step 1: Configuring the OpenAI API

1. Go to the OpenAI website and create an account or log in.
2. Navigate to the API management section and generate a new API key. Save that key as you will need it to authenticate your requests.

Step 2: Open the Google Apps Script Editor

1. Open a new or existing Google Sheets where you want to use GPT.
2. In the menu, click on Extensions & gt; Apps Script.
3. This will open the Google Apps Script editor in a new tab.

Step 3: Install the code

1. In the Apps Script editor, delete any existing code.
2. Copy and paste the code provided in the editor.
3. Replace This appears to be untranslatable gibberish. by the actual API key you obtained from OpenAI.
4. Save the script with a relevant project name, using File & gt; Save The icon of the diskette.

Step 4: Use the Custom Function in Google Sheets

1. Go back to your Google Sheets.
2. In a cell, enter =GPT (“Your question here”) You can also reference other cells that contain the text you want to send.
3. Press Enter and wait for the response to be generated. This can take a few seconds depending on the API response.

Important considerations

• Quotas and Limits: The OpenAI API has usage quotas, which can limit the amount of requests you can make.
• Security of the API keyKeep your API key secure and do not share it within the script or in any way that may be exposed publicly.
• Treatment of ErrorsThe code includes basic error handling, but you may want to expand it to better deal with network failures or unexpected API responses.
• Customization: You can adjust the model (modelo, the maximum number of tokens ('max_tokens') and other configurations according to the need of your project.

Following these steps, you’ll be able to integrate OpenAI’s powerful GPT into Google Sheets, opening up a range of possibilities for automation, data analysis, content generation, and more.