Downloads
The Video Guide
Why connect Google Sheets & Custom GPT
By linking Google Sheets with ChatGPT via a Custom GPT, you can:
- Connect a live data source from Google Sheets.
- Enable AI-powered analysis to summarise trends and insights.
- Automate workflows to generate reports, forecasts, and summaries.
- Ask ChatGPT specific queries about your data in real time.
⚡ Use Cases
- 💼 Business & Sales Teams: Get AI-generated reports on revenue, expenses, and performance metrics.
- 📊 Marketing Teams: Analyze campaign performance and generate insights.
- 📉 Investors & Traders: Retrieve and analyze stock market data from Google Sheets.
- 🛍️ E-commerce Owners: Track product sales, inventory levels, and customer trends.
Step 1 - Your Google Sheet
Your Google Sheet fundamentally can have as much data in it as you’d like!
Stock Tickers – You can use google’s inbuilt ‘Finance’ function to query a stock ticker (e.g =GOOGLEFINANCE(“GOOG”,”price”)).
Sales Data – Build out the spreadsheet as much as you want, pull in live data from your sales software.
Output – We are then going to output this data as ‘JSON’ which is a really easy format for our custom GPT to hook on to. You may need to customise the AppScript and Schema I’ve provided above – depending on your setup. Remember – ChatGPT is your friend here if you are no code.
Our Example Sheet – Our example sheet and AppScript outputs the data from cell B13 in the sheet name ‘Example’. Providing you understand this, you can customise the sheet and script as you please!
Step 2 - Google AppScript
🚀 Why Do We Need This?
- 📊 Real-Time Data Access: Retrieve live spreadsheet data remotely via an API request.
- 🔗 Integration with External Apps: Fetch data from Google Sheets and use it in ChatGPT.
- ⚡ Automated Data Retrieval: Reduce manual data entry by accessing specific cell values dynamically.
🔍 Understanding the Code
The function doGet(e) creates a public API endpoint that allows us to retrieve a specific cell’s value from a Google Sheet.
- Defines the Target Sheet & Cell: The script specifies
"Example"as the sheet name and"B13"as the cell to retrieve. - Opens the Google Spreadsheet: It accesses the active spreadsheet and tries to find the specified sheet.
- Checks if the Sheet Exists: If the sheet isn’t found, an error message is returned in JSON format.
- Gets the Value from the Cell: If the sheet exists, the script retrieves the value in cell
B13. - Returns the Data in JSON Format: The cell’s value is sent back in a structured JSON response for easy integration with other applications.
- Error Handling: If an issue occurs, the script returns an error message in JSON format.
🙌 Adding this to your Spreadsheet
- Copy the code – Copy the below code and click on ‘AppScript’ in your google sheets file.
- Edit the Sheet Name & Cell – If required.
- Deploy the App – As a new web app. Go through the authorisation process and copy the url you are provided after deployment.
function doGet(e) {
// Specify the name of the sheet and the cell to retrieve
const sheetName = "Example";
const cell = "B13";
try {
// Open the active spreadsheet
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheet = spreadsheet.getSheetByName(sheetName);
// Check if the sheet exists
if (!sheet) {
return ContentService.createTextOutput(JSON.stringify({
status: "error",
message: `Sheet named "${sheetName}" not found.`
})).setMimeType(ContentService.MimeType.JSON);
}
// Get the value from the specified cell
const cellValue = sheet.getRange(cell).getValue();
// Return the value as a JSON response
return ContentService.createTextOutput(JSON.stringify({
status: "success",
value: cellValue
})).setMimeType(ContentService.MimeType.JSON);
} catch (error) {
// Handle any errors
return ContentService.createTextOutput(JSON.stringify({
status: "error",
message: error.message
})).setMimeType(ContentService.MimeType.JSON);
}
}
Step 3 - Creating your CustomGPT
Now that you have set up your Google Sheets API endpoint, it’s time to create a Custom GPT that can retrieve and analyse data from your spreadsheet in real-time! This step will guide you through setting up your own ChatGPT instance that can fetch live data directly from Google Sheets.
📍 Step 3.1: Access the Custom GPT Editor
To begin, navigate to the Custom GPT Editor:
➡️ Visit the ChatGPT GPTs Editor
Here, you will build the foundation of your GPT by customising its name, instructions, and conversation starters.
📌 Step 3.2: Customise Your GPT
In the editor, follow these steps:
- 📌 Name Your GPT: Choose a name that represents its purpose (e.g., “Google Sheets Assistant”).
- 📝 Instructions: Describe how your GPT should behave, including how it should interact with Google Sheets data.
- 💬 Conversation Starters: Provide example prompts like:
- “What is the latest stock price in my Google Sheet?”
- “Summarise my sales data for this month.”
- “Fetch the value from my Google Sheet”.
- 📂 Add Reference Files (Optional): Upload any documents that assist in its knowledge.
🔗 Step 3.3: Connect Google Sheets via Custom Actions
Now, you need to integrate your GPT with your Google Sheets webhook by creating a new Custom Action.
- Go to “Create New Actions” in the GPT editor.
- Use the following API schema to define the integration.
- Ensure you change the URL to your URL we populated from the Google Sheet.
👉 Step 3.4: Testing Your Custom GPT
Once the action is added, you should see “getcellvalue” as an available option.
- Press the Test button.
- Click Allow when prompted.
- Your GPT should now fetch the value from your Google Sheet and display it.
{
"openapi": "3.1.0",
"info": {
"title": "Google Sheets Data Fetcher",
"version": "1.0.0",
"description": "Fetches the value of a specific cell from a Google Sheet using a Google Apps Script macro."
},
"servers": [
{
"url": "insert_your_google_sheets_macro"
}
],
"paths": {
"/exec": {
"get": {
"summary": "Fetch the value of a specific cell from a Google Sheet",
"operationId": "getCellValue",
"parameters": [
{
"name": "sheet_name",
"in": "query",
"required": true,
"description": "The name of the sheet to fetch the data from.",
"schema": {
"type": "string"
}
},
{
"name": "cell",
"in": "query",
"required": true,
"description": "The address of the cell to fetch the data from (e.g., B13).",
"schema": {
"type": "string"
}
}
],
"responses": {
"200": {
"description": "A JSON object containing the value of the requested cell.",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {
"status": {
"type": "string",
"description": "The status of the request ('success' or 'error')."
},
"value": {
"type": "string",
"description": "The value of the requested cell (if the status is 'success')."
},
"message": {
"type": "string",
"description": "An error message (if the status is 'error')."
}
},
"required": [
"status"
]
}
}
}
}
}
}
}
}
}
Step 4 - See one I've built
One of my investments in Touchstone Exploration. You can find a custom GPT I’ve built for TXP here.
I’ve uploaded all of the Regulatory News Services for TXP in it’s memory, as well as connecting it to live data such as the share price, shares in issue and more.
Happy Custom GPTing!
Congratulations! You’ve successfully created a Custom GPT that can fetch real-time data from Google Sheets. With this setup, you can now:
- 📊 Retrieve and analyze spreadsheet data effortlessly.
- 🤖 Automate reports and get instant AI-driven insights.
- 🔗 Seamlessly integrate ChatGPT with your business workflows.
- ⚡ Expand automation possibilities using APIs and third-party tools.
By leveraging Google Apps Script, and ChatGPT actions, you’ve built a dynamic system that makes data more accessible, interactive, and intelligent. The possibilities for automation, data processing, and AI-powered decision-making are endless!
💡 What’s next? You can further refine your Custom GPT by adding more actions, integrating additional data sources, or expanding its analytical capabilities.
🚀 Ready to take it even further? Keep experimenting, refining, and unlocking new AI-powered workflows.
Happy Custom GPTing! 🎉
