Integrate ChatGPT with Google Sheets for automated data insights

Note: This post was originally published in our community forum.

I recently worked on an exciting project where I integrated Google Sheets with ChatGPT. This integration allows you to generate AI-driven insights directly from your spreadsheet data. If you’re looking to automate report generation or data analysis, this guide is for you!

Why Should You Integrate Google Sheets with ChatGPT?

By connecting Google Sheets to ChatGPT, you can:

• Automate the generation of summaries and insights based on your data.
• Simplify complex data analysis tasks.
• Save time by directly fetching AI-generated responses into your spreadsheet.

What You’ll Need:

• Google Sheets: A spreadsheet with your data.
• OpenAI API Key: You’ll need an API key from OpenAI to connect Google Sheets with ChatGPT.

Step-by-Step Guide

Step 1: Setting Up Your Google Sheet

Start by organizing your data and preparing your prompts.

1. Create a ‘Sales Data’ Tab:
   • This tab will hold your data. Here’s an example of what it might look like:

1. Create a ‘Summary’ Tab:
   • This is where you’ll generate your prompts and display the AI responses.

     Example

In cell A1, write: plaintext

Step 3: Connecting Google Sheets to ChatGPT Using Google Apps Script

Now, let’s automate the process by sending your prompt to ChatGPT and fetching the response.

1. Google Apps Script Example:
   • Open Google Apps Script from your Google Sheet (Extensions > Apps Script).
   • Paste the following script:

     function getChatGPTResponse() {

     var prompt = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary').getRange('A1').getValue();

     var apiKey = 'YOUR_OPENAI_API_KEY';

     var url = 'https://api.openai.com/v1/chat/completions';

     var payload = {

     'model': 'gpt-3.5-turbo', // Ensure you're using a valid model

     'messages': [{'role': 'user', 'content': prompt}],

     'max_tokens': 150

     };

     var options = {

     'method' : 'post',

     'contentType': 'application/json',

     'headers': {

     'Authorization': 'Bearer ' + apiKey

     },

     'payload' : JSON.stringify(payload),

     'muteHttpExceptions': true // Enables detailed error responses

     };

     // Fetch response from the API

     var response = UrlFetchApp.fetch(url, options);

     var json = response.getContentText();

     Logger.log(json); // Log the full response for debugging

     var parsedJson = JSON.parse(json);

     if (parsedJson.choices && parsedJson.choices.length > 0) {

     var chatGPTOutput = parsedJson.choices[0].message.content.trim();

     SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary').getRange('B1').setValue(chatGPTOutput);

     } else {

     var errorMessage = "Error: Unexpected API response structure. Full response: " + json;

     SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Summary').getRange('B1').setValue(errorMessage);

     }

     }

Replace 'YOUR_OPENAI_API_KEY' with your actual OpenAI API key.

1. Running the Script:
   • Save the script and run the getChatGPTResponse function.
   • This script will take the prompt from cell A1 of the ‘Summary’ tab, send it to ChatGPT, and place the response in cell B1.

Step 4: Example Output

Here’s how your ‘Summary’ tab might look after running the script:

In this example, the prompt in A1 was processed by ChatGPT, and the response in B1 provides a summary based on the data from the ‘Sales Data’ tab.

Step 5: Troubleshooting Common Issues

1. 404 Error:
   • If you encounter a 404 error, it might be due to using a deprecated model. Ensure you’re using a valid and supported model like gpt-3.5-turbo.
2. TypeError:
   • If you see TypeError: Cannot read properties of undefined (reading '0'), it means the response structure isn’t as expected. The script now logs the full response so you can debug it more easily.