How to integrate ChatGPT into Google sheets via AppScripts?

Lukas Görög

ChatGPT predictea

Google Apps Script is a useful tool for automating processes and integrating numerous Google services, such as Google Spreadsheets and the OpenAI Chatbot model GPT. In this article, we’ll explain how to use Google Apps Script to integrate ChatGPT with Google Spreadsheets.

You must have a fundamental understanding of Google Spreadsheets, Google Apps Script. Having “php” or “javascript” knowledge is a plus, but we provide you with both the code anyway, which you can choose either of those. To access Google Spreadsheets and Google Apps Script, you will need a Google account.

  1. Open a new or existing Google Sheet and select “Apps Script” from the “Extensions” menu.
  2. Make a new project in the Apps Script editor and give it the appropriate name.
  3. Thereafter, paste the subsequent code into the editor:

PHP code:

function CHATGPT(input) {
  var response = UrlFetchApp.fetch('https://api.openai.com/v1/engines/davinci-codex/completions', {
    method: 'POST',
    headers: {
      'Content-Type': 'application/json',
      'Authorization': 'Bearer YOUR_API_KEY'
    },
    payload: JSON.stringify({
      prompt: input,
      max_tokens: 150,
      temperature: 0.5,
      n: 1,
      stop: '\n'
    })
  });
  var data = JSON.parse(response.getContentText());
  return data.choices[0].text.trim();

Java Script code:

function CHATGPT(prompt, parameters) {
  var response = "";
  var data = {
    "prompt": prompt,
    "max_tokens": 150,
    "temperature": 0.5,
    "n": 1,
    "stop": "\n"
  };
  if (parameters) {
    Object.keys(parameters).forEach(function(key) {
      data[key] = parameters[key];
    });
  }
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'headers': {
      'Authorization': 'Bearer YOUR_API_KEY',
    },
    'payload': JSON.stringify(data),
  };
  var response = UrlFetchApp.fetch('https://api.openai.com/v1/engines/davinci-codex/completions', options);
  var json = JSON.parse(response.getContentText());
  return json.choices[0].text.trim();
}
  1. Your actual OpenAI API key should be substituted for “YOUR API KEY.”

After you run the code, you should see something like this on your Execution log if it is successful.

  1. Close the Apps Script editor after saving the script and return to your Google Sheet
  2. In a cell, enter =CHATGPT(“your prompt text here”) replacing “your prompt text here” with your actual prompt text.
  3. Once you press Enter, the ChatGPT response should appear in the cell.

That’s it! Google Sheets and ChatGPT are now properly linked.

To confirm if the response from ChatGPT is appropriately stored, run your script in the Script Editor and view the Google Spreadsheet. After you are happy with the outcomes, you can schedule the script to run by using Google Apps Script’s “Triggers” feature.

Google Apps Script offers a strong and adaptable method of integrating ChatGPT with Google Spreadsheets. It can be connected with other Google services to build robust workflows and used to automate a wide range of operations, including data collecting and analysis.

Menu