DEV Community

Jeremy SFEZ
Jeremy SFEZ

Posted on

How to Connect Your Google Sheet to ChatGPT

If you're looking to leverage the power of AI to interact with your Google Sheet content, connecting your sheet to ChatGPT is a great way to get started. In this post, we'll walk through the steps required to connect your sheet to ChatGPT and start using its capabilities to answer questions about your data.

Google sheet connected to ChatGPT

Setting Up Google Apps Script

Before we get started with ChatGPT, you'll need to set up Google Apps Script. If you're not already familiar with Google Apps Script, you can get started with the documentation here.

Javascript code snippet

The code is available in this GitHub gist.

// 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;
}
Enter fullscreen mode Exit fullscreen mode

Connecting Your Google Sheet to ChatGPT

To connect your Google Sheet to ChatGPT, you'll need to follow these steps:

  1. Copy the code provided above and paste it into Google Apps Script.
  2. Replace "YOUR_API_KEY" with your ChatGPT API key.
  3. Save and execute the code.
  4. Allow the necessary Google authorizations.

Once you've completed these steps, you'll be able to use the =gpt() formula in your Google Sheet to query ChatGPT about your sheet content. This will allow you to get intelligent responses to your questions based on the data in your sheet.

Conclusion

Connecting your Google Sheet to ChatGPT is a powerful way to take advantage of AI to interact with your data. With the help of Google Apps Script, it's easy to set up and start using in your sheet. By following the steps outlined above, you'll be able to get started quickly and start benefiting from the power of ChatGPT in no time.

Top comments (0)