Connect Google Sheets to OpenAI: Generate Content with Apps Script

Artificial intelligence has revolutionized content creation, and now you can harness that power directly within Google Sheets. By connecting OpenAI’s API to your spreadsheets using Google Apps Script, you can generate content, summarize text, analyze data, and much more—all without switching between different tools.

In this tutorial, I’ll walk you through how to create a Google Sheets integration with OpenAI that lets you generate content with a simple function call or button click.

What You’ll Build

By the end of this tutorial, you’ll have:

• A Google Sheet connected to OpenAI’s API
• Custom functions that generate content based on your prompts
• A user-friendly interface with buttons
• The ability to process batches of content requests

Prerequisites

1. A Google account
2. An OpenAI API key (requires signup and payment method)
3. Basic familiarity with Google Sheets
4. No prior coding experience required, though it helps

Step 1: Set Up Your Google Sheet

First, let’s create a spreadsheet that will serve as our interface with OpenAI:

1. Create a new Google Sheet
2. Set up the following columns in row 1:
   • A1: Prompt
   • B1: Generated Content
   • C1: Model
   • D1: Temperature
   • E1: Max Tokens
   • F1: Generate

Format the header row to make it stand out (bold, background color, etc.).

Step 2: Create the Apps Script Project

Now, let’s create the script that will connect your sheet to OpenAI:

1. Click Extensions → Apps Script
2. Delete any existing code
3. Paste the following code:

// Store your OpenAI API key
const OPENAI_API_KEY = 'YOUR_API_KEY_HERE'; // Replace with your actual API key

/**
 * Creates a menu item for the Google Sheet
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('OpenAI')
    .addItem('Generate Content', 'generateForSelectedRows')
    .addItem('About', 'showAbout')
    .addToUi();
}

/**
 * Shows information about the script
 */
function showAbout() {
  const ui = SpreadsheetApp.getUi();
  ui.alert(
    'OpenAI Integration',
    'This script connects Google Sheets to OpenAI\'s API for content generation.\n\n' +
    'Created by Blogmines.com',
    ui.ButtonSet.OK
  );
}

/**
 * Generates content for the selected rows
 */
function generateForSelectedRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const selectedRange = sheet.getActiveRange();
  const startRow = selectedRange.getRow();
  const numRows = selectedRange.getNumRows();
  
  // Process each selected row
  for (let i = 0; i < numRows; i++) {
    const currentRow = startRow + i;
    
    // Skip the header row if selected
    if (currentRow === 1) continue;
    
    // Get values from the row
    const prompt = sheet.getRange(currentRow, 1).getValue();
    if (!prompt) continue; // Skip empty prompts
    
    // Get optional parameters or use defaults
    const model = sheet.getRange(currentRow, 3).getValue() || 'gpt-3.5-turbo';
    const temperature = sheet.getRange(currentRow, 4).getValue() || 0.7;
    const maxTokens = sheet.getRange(currentRow, 5).getValue() || 500;
    
    // Call OpenAI API and write the result
    try {
      const generatedContent = callOpenAI(prompt, model, temperature, maxTokens);
      sheet.getRange(currentRow, 2).setValue(generatedContent);
    } catch (error) {
      sheet.getRange(currentRow, 2).setValue('Error: ' + error.message);
    }
  }
}

/**
 * Custom function to generate content in a cell
 * Example: =GENERATEAI(A2, C2, D2, E2)
 */
function GENERATEAI(prompt, model, temperature, maxTokens) {
  // Default values if not provided
  model = model || 'gpt-3.5-turbo';
  temperature = temperature || 0.7;
  maxTokens = maxTokens || 500;
  
  try {
    return callOpenAI(prompt, model, temperature, maxTokens);
  } catch (error) {
    return 'Error: ' + error.message;
  }
}

/**
 * Call the OpenAI API
 */
function callOpenAI(prompt, model, temperature, maxTokens) {
  // Prepare the request
  const url = 'https://api.openai.com/v1/chat/completions';
  const payload = {
    model: model,
    messages: [
      {
        role: "user",
        content: prompt
      }
    ],
    temperature: temperature,
    max_tokens: maxTokens
  };
  
  const options = {
    method: 'post',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + OPENAI_API_KEY
    },
    payload: JSON.stringify(payload)
  };
  
  // Make the API call
  const response = UrlFetchApp.fetch(url, options);
  const responseData = JSON.parse(response.getContentText());
  
  // Extract and return the generated content
  return responseData.choices[0].message.content.trim();
}

/**
 * Add buttons to the "Generate" column
 */
function addGenerateButtons() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = Math.max(2, sheet.getLastRow()); // Start from row 2
  
  // Create buttons for each row
  for (let row = 2; row <= lastRow; row++) {
    // Skip if there's already a button
    if (sheet.getRange(row, 6).getValue() === 'Generate') continue;
    
    // Add a "Generate" button
    sheet.getRange(row, 6).setValue('Generate')
      .setBackground('#4285F4')
      .setFontColor('white')
      .setHorizontalAlignment('center')
      .setVerticalAlignment('middle');
  }
}

/**
 * Handle clicks on the "Generate" buttons
 */
function onEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  
  // Check if the edit was in the Generate column (column F)
  if (range.getColumn() === 6 && range.getValue() === 'Generate' && range.getRow() > 1) {
    const row = range.getRow();
    
    // Get values from the row
    const prompt = sheet.getRange(row, 1).getValue();
    if (!prompt) {
      sheet.getRange(row, 2).setValue('Error: No prompt provided');
      return;
    }
    
    // Get optional parameters or use defaults
    const model = sheet.getRange(row, 3).getValue() || 'gpt-3.5-turbo';
    const temperature = sheet.getRange(row, 4).getValue() || 0.7;
    const maxTokens = sheet.getRange(row, 5).getValue() || 500;
    
    // Show "Generating..." while processing
    sheet.getRange(row, 2).setValue('Generating...');
    
    // Call OpenAI API and write the result
    try {
      const generatedContent = callOpenAI(prompt, model, temperature, maxTokens);
      sheet.getRange(row, 2).setValue(generatedContent);
    } catch (error) {
      sheet.getRange(row, 2).setValue('Error: ' + error.message);
    }
  }
}

1. Replace 'YOUR_API_KEY_HERE' with your actual OpenAI API key
2. Save the project with a name like “OpenAI Integration”

Step 3: Test Your Integration

Let’s test the basic functionality:

1. Go back to your Google Sheet
2. Reload the page (you should see a new “OpenAI” menu)
3. In cell A2, enter a prompt like: “Write a short paragraph about cloud computing”
4. Leave B2 empty (this is where the generated content will appear)
5. In C2, enter: “gpt-3.5-turbo” (or “gpt-4” if you have access)
6. In D2, enter: 0.7 (controls creativity, higher = more creative)
7. In E2, enter: 150 (maximum length of response)
8. Click OpenAI → Generate Content

After a brief moment, you should see AI-generated content appear in cell B2!

Step 4: Add Interactive Buttons (Optional)

To make your sheet more user-friendly, let’s add buttons that trigger generation for individual rows:

1. Go back to the Apps Script editor
2. Run the addGenerateButtons function by:
   • Select the function from the dropdown at the top
   • Click the Run button (play icon)
3. When prompted, authorize the script
4. Return to your sheet to see the buttons in column F

Now you can click these buttons to generate content for a specific row without using the menu.

Step 5: Use Custom Functions in Cells

You can also use the built-in custom function directly in cells:

1. In a new cell (e.g., G2), enter: =GENERATEAI(A2, C2, D2, E2)
2. Press Enter

This will generate content based on the parameters in the referenced cells. The advantage of this approach is that it recalculates when the input cells change.

Practical Applications

Here are some ways you can use this OpenAI integration:

Content Creation

Prompt	Purpose
Write a product description for a blue ceramic coffee mug	E-commerce listings
Generate 5 email subject lines about our summer sale	Email marketing
Write a professional LinkedIn post about our company’s 5th anniversary	Social media content

Data Analysis

Prompt	Purpose
Summarize the following customer feedback: [paste feedback]	Customer insights
Convert this technical description into simple language: [paste text]	Simplification
Identify key action items from this meeting transcript: [paste transcript]	Meeting summaries

Search Engine Optimization

Prompt	Purpose
Generate 10 SEO-friendly blog title ideas about indoor gardening	Content planning
Write meta descriptions for a webpage about healthy breakfast recipes	SEO optimization
Create a list of 20 long-tail keywords related to sustainable fashion	Keyword research

Advanced Customization

Handling Longer Responses

For longer content, you might need to adjust the formatting:

// Modify the callOpenAI function:
function callOpenAI(prompt, model, temperature, maxTokens) {
  // ... existing code ...
  
  // Format longer responses with line breaks
  const content = responseData.choices[0].message.content.trim();
  
  // Properly format paragraphs in Google Sheets
  return content.replace(/\n\n/g, "\n");
}

Adding a Progress Indicator

For batch processing, add a progress indicator:

function generateForSelectedRows() {
  // ... existing code ...
  
  const ui = SpreadsheetApp.getUi();
  ui.alert('Processing', `Processing ${numRows} rows. This might take a moment.`, ui.ButtonSet.OK);
  
  // ... process rows ...
  
  ui.alert('Complete', `Successfully processed ${numRows} rows.`, ui.ButtonSet.OK);
}

Rate Limiting

To avoid hitting OpenAI’s rate limits, add a delay between API calls:

function generateForSelectedRows() {
  // ... existing code ...
  
  for (let i = 0; i < numRows; i++) {
    // ... process row ...
    
    // Add a delay between API calls
    if (i < numRows - 1) {
      Utilities.sleep(1000); // 1-second delay
    }
  }
}

Security Best Practices

Protecting Your API Key

Storing your API key directly in the script is convenient but not the most secure method. For better security, use the Properties Service:

// Store API key securely
function setApiKey() {
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt(
    'OpenAI API Key',
    'Enter your OpenAI API key (it will be stored securely):',
    ui.ButtonSet.OK_CANCEL
  );
  
  if (response.getSelectedButton() === ui.Button.OK) {
    const apiKey = response.getResponseText();
    PropertiesService.getUserProperties().setProperty('OPENAI_API_KEY', apiKey);
    ui.alert('Success', 'API key saved successfully.', ui.ButtonSet.OK);
  }
}

// Retrieve API key
function getApiKey() {
  return PropertiesService.getUserProperties().getProperty('OPENAI_API_KEY');
}

// Modify callOpenAI to use the stored key
function callOpenAI(prompt, model, temperature, maxTokens) {
  const apiKey = getApiKey();
  if (!apiKey) {
    throw new Error('API key not found. Please set up your API key first.');
  }
  
  // ... rest of the function using apiKey instead of OPENAI_API_KEY ...
}

Add a menu item to set the API key:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('OpenAI')
    .addItem('Generate Content', 'generateForSelectedRows')
    .addItem('Set API Key', 'setApiKey')
    .addItem('About', 'showAbout')
    .addToUi();
}

Troubleshooting

Common Errors

1. API Key Error: Ensure your OpenAI API key is correct and has sufficient credits
2. Timeout Error: For large batches, process fewer rows at once
3. Content Policy Violation: Ensure your prompts comply with OpenAI’s content policy

Debugging

Add logging to troubleshoot issues:

function callOpenAI(prompt, model, temperature, maxTokens) {
  try {
    // ... existing code ...
    return responseData.choices[0].message.content.trim();
  } catch (error) {
    console.error('Error calling OpenAI API:', error);
    console.error('Response:', response ? response.getContentText() : 'No response');
    throw error;
  }
}

Conclusion

By connecting Google Sheets to OpenAI’s API, you’ve created a powerful tool for content generation and text processing. This integration allows you to leverage AI capabilities without leaving your spreadsheet, streamlining your workflow and saving valuable time.

The possibilities are virtually endless—from creating marketing copy to summarizing data, analyzing feedback, or generating creative content. As AI models continue to improve, your Google Sheets integration will become even more powerful.

Next Steps

Ready to take your AI integration further? Check out these other tutorials:

• Build a CRUD Web App with Google Apps Script
• Automate Google Sheets with Time-Saving Scripts
• Send Personalized Emails from Google Sheets

Have you used AI in your spreadsheets before? Share your experience or questions in the comments below!