{"id":16698,"date":"2024-01-31T21:02:10","date_gmt":"2024-01-31T21:02:10","guid":{"rendered":"https:\/\/kevinbk.com\/?p=16698"},"modified":"2024-01-31T21:02:13","modified_gmt":"2024-01-31T21:02:13","slug":"gpt-no-google-sheet-de-graca-com-apps-script","status":"publish","type":"post","link":"https:\/\/kevinbk.com\/en\/gpt-in-google-sheet-with-apps-script\/","title":{"rendered":"GPT on Google Sheet Free with Apps Script"},"content":{"rendered":"

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. <\/p>\n\n\n\n

Advantages of GPT integration with Google Sheets<\/strong><\/h2>\n\n\n\n

A. Personalization and Flexibility<\/strong>: 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.<\/p>\n\n\n\n

B. Economy of time and efficiency<\/strong>GPT 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.<\/p>\n\n\n\n

C. Continuous improvement<\/strong>The GPT model is constantly evolving, receiving updates and improvements that expand its capabilities and efficiency.<\/p>\n\n\n

\n
\"AIKit<\/figure><\/div>\n\n\n

Extension x Personalized script<\/h2>\n\n\n\n

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. <\/p>\n\n\n\n

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

The big problem is that using the Apps Scripts Script even with the help of chatGPT can still cause errors. <\/p>\n\n\n\n

How to integrate GPT into Google Sheets<\/strong><\/h2>\n\n\n\n

Integrating GPT into Google Sheets involves a few technical steps, including:<\/p>\n\n\n\n

A. Configuration of API<\/strong>: First, you need to gain access to the OpenAI GPT API. This usually involves creating an account and obtaining an API key.<\/p>\n\n\n\n

B. Use of Google Apps Script<\/strong>: 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.<\/p>\n\n\n\n

C. Automation and Customization<\/strong>: With the script running, you can automate tasks, such as text generation, data analysis, and other features, directly in Google Sheets.<\/p>\n\n\n\n

Apps Script Code to Use GPT in Sheet<\/h2>\n\n\n\n

Below I share the code I use to use a simple formula like GPT=(\"prompt\";CE1)<\/em><\/strong>Using this Sheet formula I can use different cells to generate content in my tables. <\/p>\n\n\n\n

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. <\/p>\n\n\n\n

var apiKey = 'SUACHAVEDEAPI';\n\nfunction GPT() {\n  var messages = [];\n  for (var i = 0; i < arguments.length; i++) {\n    var arg = arguments[i];\n    if (typeof arg === 'string' && arg.trim() !== '') {\n      messages.push({ 'role': 'user', 'content': arg.trim() });\n    } else if (Array.isArray(arg)) {\n      arg.forEach(function(cellValue) {\n        if (typeof cellValue === 'string' && cellValue.trim() !== '') {\n          messages.push({ 'role': 'user', 'content': cellValue.trim() });\n        }\n      });\n    }\n  }\n\n  if (messages.length === 0) {\n    return 'Nenhuma mensagem v\u00e1lida fornecida.';\n  }\n\n  var url = 'https:\/\/api.openai.com\/v1\/chat\/completions';\n  var headers = {\n    'Authorization': 'Bearer ' + apiKey,\n    'Content-Type': 'application\/json'\n  };\n\n  var payload = {\n    'model': 'gpt-3.5-turbo-1106', \/\/ Especificar o modelo desejado\n    'messages': messages,\n    'max_tokens': 700 \/\/ Configura\u00e7\u00e3o flex\u00edvel do n\u00famero m\u00e1ximo de tokens\n  };\n\n  var options = {\n    'method': 'post',\n    'headers': headers,\n    'payload': JSON.stringify(payload),\n    'muteHttpExceptions': true \/\/ Importante para tratar erros\n  };\n\n  try {\n    var response = UrlFetchApp.fetch(url, options);\n    var result = JSON.parse(response.getContentText());\n\n    if (response.getResponseCode() === 200 && result.choices && result.choices.length > 0) {\n      return result.choices[0].message.content; \/\/ Acessa o conte\u00fado da mensagem corretamente\n    } else {\n      Logger.log('Erro na resposta da API: ' + response.getContentText());\n      return 'Erro na resposta da API.';\n    }\n  } catch (e) {\n    Logger.log('Erro ao fazer a requisi\u00e7\u00e3o para a API: ' + e.message);\n    return 'Erro ao fazer a requisi\u00e7\u00e3o para a API.';\n  }\n}\n<\/code><\/pre>\n\n\n\n
How to Install Code in Apps Script <\/h2>\n\n\n\n
To integrate OpenAI\u2019s 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.<\/p>\n\n\n\n
Step 1: Configuring the OpenAI API<\/h3>\n\n\n\n
    \n
  1. Go to the OpenAI website and create an account or log in.<\/li>\n\n\n\n
  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.<\/li>\n<\/ol>\n\n\n\n
    Step 2: Open the Google Apps Script Editor<\/h3>\n\n\n\n
      \n
    1. Open a new or existing Google Sheets where you want to use GPT.<\/li>\n\n\n\n
    2. In the menu, click on Extensions<\/code> & gt; Apps Script<\/code>.<\/li>\n\n\n\n
    3. This will open the Google Apps Script editor in a new tab.<\/li>\n<\/ol>\n\n\n\n
      Step 3: Install the code<\/h3>\n\n\n\n
        \n
      1. In the Apps Script editor, delete any existing code.<\/li>\n\n\n\n
      2. Copy and paste the code provided in the editor.<\/li>\n\n\n\n
      3. Replace This appears to be untranslatable gibberish.<\/code> by the actual API key you obtained from OpenAI.<\/li>\n\n\n\n
      4. Save the script with a relevant project name, using File<\/code> & gt; Save<\/code> The icon of the diskette.<\/li>\n<\/ol>\n\n\n\n
        Step 4: Use the Custom Function in Google Sheets<\/h3>\n\n\n\n
          \n
        1. Go back to your Google Sheets.<\/li>\n\n\n\n
        2. In a cell, enter =GPT (\u201cYour question here\u201d)<\/code> You can also reference other cells that contain the text you want to send.<\/li>\n\n\n\n
        3. Press Enter and wait for the response to be generated. This can take a few seconds depending on the API response.<\/li>\n<\/ol>\n\n\n\n
          Important considerations<\/h3>\n\n\n\n
            \n
          • Quotas and Limits<\/strong>: The OpenAI API has usage quotas, which can limit the amount of requests you can make.<\/li>\n\n\n\n
          • Security of the API key<\/strong>Keep your API key secure and do not share it within the script or in any way that may be exposed publicly.<\/li>\n\n\n\n
          • Treatment of Errors<\/strong>The code includes basic error handling, but you may want to expand it to better deal with network failures or unexpected API responses.<\/li>\n\n\n\n
          • Customization<\/strong>: You can adjust the model (modelo<\/code>, the maximum number of tokens ('max_tokens'<\/code>) and other configurations according to the need of your project.<\/li>\n<\/ul>\n\n\n\n
            Following these steps, you\u2019ll be able to integrate OpenAI\u2019s powerful GPT into Google Sheets, opening up a range of possibilities for automation, data analysis, content generation, and more.<\/p>","protected":false},"excerpt":{"rendered":"
            Usually when looking for integration with GPT in Google Sheet we encounter several options available in the extension stores,<\/p>","protected":false},"author":1,"featured_media":16169,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/kevinbk.com\/wp-content\/uploads\/2023\/08\/ChatGPT-x-Bard-quais-as-diferencas.jpg","_links":{"self":[{"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/posts\/16698"}],"collection":[{"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/comments?post=16698"}],"version-history":[{"count":1,"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/posts\/16698\/revisions"}],"predecessor-version":[{"id":16699,"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/posts\/16698\/revisions\/16699"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/media\/16169"}],"wp:attachment":[{"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/media?parent=16698"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kevinbk.com\/en\/wp-json\/wp\/v2\/categories?post=16698"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}