Connect Google Sheet to chatGPT

connectGoogleSheetToChatGPT.js

// Load OpenAI API key from script properties
const openaiApiKey = "YOUR_API_KEY";

// Load Google Sheet data
const sheet =
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() ||
  SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
const data = sheet.getDataRange().getValues();

// Fetch response from OpenAI's completion API
function generateResponse(prompt) {
  if (!openaiApiKey) throw new Error("OpenAI API key not found.");
  const openaiUrl = "https://api.openai.com/v1/completions";
  const response = UrlFetchApp.fetch(openaiUrl, {
    method: "post",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${openaiApiKey}`,
    },
    payload: JSON.stringify({
      prompt,
      model: "text-davinci-003",
      max_tokens: 1024,
      n: 1,
      stop: null,
      temperature: 0.5,
      presence_penalty: 0,
      frequency_penalty: 0,
      best_of: 1,
    }),
  });

  const responseData = JSON.parse(response.getContentText());
  return responseData.choices[0].text.trim();
}

function gpt(prompt) {
  const response = generateResponse(
    `${data.map((row) => row.join("\t")).join("\n")}\n\n${prompt}`
  );
  Logger.log(response);
  return response;
}

Connect your Google Sheet to ChatGPT

This code send provide the =gpt() formule to your Google spreadsheet.
When gpt formule is used, your sheet content is sent to chatGPT and used as context to answer your prompt.

Installation

1. Copy this script the code above to Google App Script.
2. Replace "YOUR_API_KEY" by your chatGPT API key.
3. Save and execute the code.
4. Allow the Google authorizations

You are now able to use =gpt() in your google sheet.

Example

Formule: =gpt("What is Rose's age?")