Step-by-Step Guide : Build your own AI Powered Google Sheet Extension
If you love google sheets and want to add AI into it then you can simply build your own personal extensions that work the way you want. It's really fun and easy to build your own copilot.
How about making a tool that can generate Social Media Posts for any topic you want.
Check out this video to get a glimpse.
We do a lot of work on Google Sheets every day. Many companies run only on Google Sheets, including accounts, inventory tracking, expense tracking, attendance, timesheets, quality assessments, customer tracking, and more.
Every business starts with a Google Sheet or an Excel Sheet. Imagine our Google Sheets become smarter with AI and help in your day-to-day work and you can do it for your work leveraging AI with your Scripts & Extensions
Today, we will see how we can make our google sheets smarter with the help of AI.
This is simple but powerful for many can address a lot of use cases.
Idea: I want to write social media posts on any topic.
Steps:
Write the prompt
Copy the prompt and paste it in Claude AI
Open a Google Sheet and name it as “Social Media posts”
Create a Google Apps Script, copy the output given by Claude in that script,
Update the OpenAI key and run the code
Go to Google Sheets and you will see the Extension “Social Media Generator”
Step 1: Write the Prompt
Prompt: I want to build an extentsion for Google Sheets, powered with OpenAI GPT to write the Social Media Posts based on the Topic. I will define the social media channel in the first row and give a reference for the cell where the Topic is written. Based on the topic write an engaging social media post for the platform.
I should also be able to drag the cell, so that if there are multiple such topics, the content get’s created for multiple topics for that platform
I want it like this
Row 1 : Topic, Linkedin, Twitter, Facebook etc
Row 2 : AI in Education, Linkedin Post, Twitter Post, Facebook Post etc..
Row 3 : AI in Movies, Linkedin Post, Twitter Post, Facebook Post etc.Step 2: Copy the Prompt in Claude.
Output Code will have two file -
Code.gs
Config.html .
The files names can be any when you are trying because AI is AI and it can give whatever name it wants.
// OpenAI API configuration
const OPENAI_API_KEY = 'YOUR_API_KEY_HERE';
const OPENAI_API_URL = 'https://api.openai.com/v1/chat/completions';
// Add custom menu to Google Sheets
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu('Social Media Generator')
.addItem('Generate Posts', 'generateSocialPosts')
.addToUi();
}
// Main function to generate social media posts
function generateSocialPosts() {
const sheet = SpreadsheetApp.getActiveSheet();
const activeCell = sheet.getActiveCell();
const row = activeCell.getRow();
const column = activeCell.getColumn();
// Get platform from first row
const platform = sheet.getRange(1, column).getValue();
// Get topic from the first column of the current row
const topic = sheet.getRange(row, 1).getValue();
if (!topic || !platform) {
SpreadsheetApp.getUi().alert('Please select a cell and ensure both topic and platform are specified');
return;
}
// Skip if the selected cell is in the first column (topic column)
if (column === 1) {
SpreadsheetApp.getUi().alert('Please select a platform cell to generate content');
return;
}
const post = generatePost(platform, topic);
// Write the generated post in the selected cell
activeCell.setValue(post);
}
// Function to generate post using OpenAI API
function generatePost(platform, topic) {
const prompt = createPrompt(platform, topic);
const payload = {
'model': 'gpt-3.5-turbo',
'messages': [
{
'role': 'system',
'content': `You are a professional social media manager. Create engaging social media posts.`
},
{
'role': 'user',
'content': prompt
}
],
'temperature': 0.7,
'max_tokens': 150
};
const options = {
'method': 'post',
'headers': {
'Authorization': `Bearer ${OPENAI_API_KEY}`,
'Content-Type': 'application/json'
},
'payload': JSON.stringify(payload)
};
try {
const response = UrlFetchApp.fetch(OPENAI_API_URL, options);
const json = JSON.parse(response.getContentText());
return json.choices[0].message.content.trim();
} catch (error) {
Logger.log(error);
return 'Error generating post. Please try again.';
}
}
// Function to create platform-specific prompts
function createPrompt(platform, topic) {
const promptTemplates = {
'LinkedIn': `Create a professional LinkedIn post about: ${topic}. Focus on business value and insights. Keep it under 3 paragraphs.`,
'Twitter': `Create an engaging tweet (max 280 characters) about: ${topic}. Include relevant hashtags.`,
'Facebook': `Create an engaging Facebook post about: ${topic}. Make it conversational and engaging. Keep it under 3 paragraphs.`,
'Instagram': `Create an engaging Instagram post about: ${topic}. Include relevant hashtags and emojis. Keep it under 3 paragraphs.`
};
return promptTemplates[platform] || `Create a social media post about: ${topic} for ${platform}.`;
}
// Function to save API key
function saveApiKey(apiKey) {
PropertiesService.getUserProperties().setProperty('OPENAI_API_KEY', apiKey);
}
// Function to get API key
function getApiKey() {
return PropertiesService.getUserProperties().getProperty('OPENAI_API_KEY');
}
// Custom function for drag-to-fill functionality
function GENERATE_SOCIAL_POST(topic, platform) {
if (!topic || !platform) return "Missing topic or platform";
return generatePost(platform, topic);
}
Config.HTML
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
.container {
padding: 20px;
font-family: Arial, sans-serif;
}
.input-group {
margin-bottom: 15px;
}
label {
display: block;
margin-bottom: 5px;
}
input {
width: 100%;
padding: 8px;
margin-bottom: 10px;
}
.button {
background-color: #4CAF50;
color: white;
padding: 10px 20px;
border: none;
border-radius: 4px;
cursor: pointer;
}
.button:hover {
background-color: #45a049;
}
</style>
</head>
<body>
<div class="container">
<h2>Social Media Post Generator Settings</h2>
<div class="input-group">
<label for="apiKey">OpenAI API Key:</label>
<input type="password" id="apiKey" name="apiKey">
</div>
<button class="button" onclick="saveSettings()">Save Settings</button>
</div>
<script>
function saveSettings() {
const apiKey = document.getElementById('apiKey').value;
google.script.run
.withSuccessHandler(onSuccess)
.saveApiKey(apiKey);
}
function onSuccess() {
alert('Settings saved successfully!');
google.script.host.close();
}
</script>
</body>
</html>
Step 3: Open a Google Sheet and name it as “Social Media posts”
Create a new sheet or Open a new Google sheet
Step 4: Go to extensions and click on Apps Script
In the Apps Script, copy the Code.gs , code And copy config.html code.
Step 5: Update OPENAI_API_KEY
If you don’t have it, create an account in https://platform.openai.com/ and go to Dashboard and then go to API Keys.
Step 6: Go to Google sheets and you will see the Extension “Social Media Generator”
Write the Topic, Social media Platform like below
Use the function : =GENERATE_SOCIAL_POST(A2,B1) i.e Topic and the Platform
and check the output.
You can improve the prompt, change the prompt and make it do any other kind of tasks as well like classification, long-form article writing, information extraction, sentiment analysis, text summarization, language translation, and a lot more.
It's quite amazing to know what AI can do. Writing scripts in general for Google Sheets or even for Excel has always been a challenge for me. But with AI now I can write the scripts for myself for whatever use case I want to address.
You can build your Custom extensions which you can use in your day-to-day work.