Re: Airtable won't give you AI features? A guide how to DIY create custom GPTs to search Airtable
0
1112
1
Hi there,
TLDR: Copy JSON code below to create a custom Action and let custom ChatGPT answer questions and make charts from your Airtable data.
1. Motivation
2. Creating custom GPTs and model instructions
3. Airtable API Actions with Open API format
4. Results, limitation & further materials
1. Motivation
Airtable has been slow in releasing any useful AI improvements, at least to the non-enterprise masses. I am still waiting on my Beta access.
From what I have seen from the few announcements the focus is on quite simple implementations like text generation inside a field (here and here) or text generation inside of a workflow (here).
Where I personally found GPT models useful was creating Airtable formulas from natural language prompts (hence here my personal plug for my chrome extension https://community.airtable.com/t5/show-tell/having-issues-with-airtable-formulas-try-hint-gpt-airtab...). I do prefer for AI to give me a starting formula to fine tune, especially with date related formulas.
Another interesting use case was inspired by one of my clients who would just drop CSV files from Airtable to ChatGPT and ask analytical questions. Shuffling CSV files between screens is certainly not an optimal experience. For a moment I was thinking about building some custom interface to query data in Airtable. That is not needed anymore.
With the November OpenAI announcement and release of Custom GPTs you can pretty much get it to query Airtable directly.
Below is an example of how I built a custom GPT to query info from this sample base https://www.airtable.com/universe/expFo1yNQPYwhey5n/vc-funds-for-early-stage-startups
2. Creating custom GPTs
Lets start the DIY part!
Visit: https://chat.openai.com/gpts/discovery and click on "Create a GPT"
You can use Chat dialog to create your GPT configuration or you can just go to Configure and fill out the fields there:
- Name & Description - this is pretty much for informational purposes - especially if you share it with other
- Instructions is the core part - this explains the "system" function to GPT - here it is also where I saved app ID for the base in question
- Conversation starters - these help with testing or guide your users
- Knowledge - here where you could benefit from free vector search and addtional context (like company policies, internal knowledge base etc.), in my case I used it unutilized
- Capabilities - Code Interpreter is essential for getting charts to work, I excluded Dall-e with motivation being that it might use up unnecessarily context room.
Here is example of my configuration screen:
Here are the Instruction settings I used:
---
As the Airtable Analyst, your communication style will be professional and straight to the point, focusing on clarity and efficiency. When interacting with users, you will prioritize straightforward and factual responses, ensuring that the information provided is directly relevant to their Airtable-related queries. Your language will be clear and concise, avoiding jargon unless necessary, and always aiming to make complex data concepts accessible. In seeking clarifications, you will ask precise questions to gather the necessary details, ensuring that your advice and solutions are accurate and tailored to the specific needs of the user’s Airtable base. This professional demeanor will establish a reliable and trustworthy atmosphere for users seeking help with their Airtable data management and analysis.
Base ID you are after: appXXXXXXX When answering questions make sure you always ask for the schema of the base, so that you know the names of the tables and fields inside the base.
If the response contains “offset”, it means you need to ask for next page of the results. Make sure that you always conduct next request if there is an offset. Otherwise the data will be incomplete.
Whenever a field data contains array of IDs in following format “recXXXXXXXX” (example “recFb5eWFX1iqaG06” ) this means it is a linked record id linking to another table. Try not to show record IDs to users, unless they specifically ask for them. Instead try to find the primary name of the linked record ID in the original table, so that you can show a human friendly name to user
---
The "appXXXXXXX" was specifically replaced by my Airtable App ID. The purpose of the paragraphs 2-4 will become more clear once you read about set up of custom actions.
3. Airtable API Actions with Open API format
For each custom GPT, you can also add custom Action. These actions allow you to call external APIs, also ones that are authenticated. In case of Airtable use can use Bearer PAT token (https://airtable.com/create/tokens) for that.
This means that you can access any Airtable API, as long as you can define it using Open API (https://www.openapis.org/) schema convention.
Airtable does not provide their API spec in Open API format but you can build one yourself - or copy the JSON for the initial 2 routes that I have built:
- get base schema (https://airtable.com/developers/web/api/get-base-schema) - this was essential to minimize GPT hallucinations and having it query Airtable fields/tables , it thinks there should be. That is also the reasons for adding in Instruction part "When answering questions make sure you always ask for the schema of the base, "
- list records (https://airtable.com/developers/web/api/list-records) - this is was the core of the exercise allowing to extract data. In the specification of this API endpoint I have included all addtional parameters such as "sort" or "formula" that allow GPT to extract specific records from the base - matching specific order and conditions.
Action configuration screen:
I have described only 2 API endpoints, there is plenty more in the Airtable developer docs. Not that I have also used "non-destructive" API calls. Nothing would stop you from adding also Create/Update Record routes to allow a chat based record creation.
With ability to save custom GPTs as public (or accessible via link) you could even imagine to use of GPT as a survey assistant asking question to complete a user profile...
Note the "x-openai-isConsequential": false parameter in JSON means that user will have option to "Always allow" API calls by the chat. When set to true the chat will always require confirmation to execute the action.
The Action JSON:
{
"openapi": "3.0.0",
"info": {
"title": "Airtable API",
"version": "1.0.0",
"description": "API for interacting with Airtable bases."
},
"servers": [
{
"url": "https://api.airtable.com/v0"
}
],
"paths": {
"/meta/bases/{baseId}/tables": {
"get": {
"summary": "Get Base Schema",
"description": "Returns the schema of the tables in the specified base.",
"operationId": "getBaseSchema",
"x-openai-isConsequential": false,
"parameters": [
{
"name": "baseId",
"in": "path",
"required": true,
"schema": {
"type": "string"
},
"description": "The ID of the base."
},
{
"name": "include",
"in": "query",
"schema": {
"type": "array",
"items": {
"type": "string"
}
},
"description": "Additional fields to include in the response"
}
],
"responses": {
"200": {
"description": "Successful response",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {
"tables": {
"type": "array",
"items": {
"$ref": "#/components/schemas/Table"
}
}
}
}
}
}
}
},
"security": [
{
"bearerAuth": []
}
]
}
},
"/{baseId}/{tableIdOrName}/listRecords": {
"post": {
"summary": "List Records in a Table",
"description": "List records in a table. Table names and table IDs can be used interchangeably. Returns a maximum of 100 records. To fetch more records, use the offset parameter.",
"operationId": "listTableRecords",
"parameters": [
{
"name": "baseId",
"in": "path",
"required": true,
"schema": {
"type": "string"
},
"description": "The ID of the base."
},
{
"name": "tableIdOrName",
"in": "path",
"required": true,
"schema": {
"type": "string"
},
"description": "The ID or name of the table."
}
],
"requestBody": {
"required": false,
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {
"pageSize": {
"type": "integer",
"default": 100
},
"maxRecords": {
"type": "integer"
},
"view": {
"type": "string"
},
"cellFormat": {
"type": "string",
"enum": ["json", "string"],
"default": "json"
},
"fields": {
"type": "array",
"items": {
"type": "string"
}
},
"filterByFormula": {
"type": "string"
},
"sort": {
"type": "array",
"items": {
"type": "object",
"properties": {
"field": {
"type": "string"
},
"direction": {
"type": "string",
"enum": ["asc", "desc"]
}
}
}
},
"offset": {
"type": "string"
}
}
}
}
}
},
"responses": {
"200": {
"description": "Successful response",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {
"records": {
"type": "array",
"items": {
"$ref": "#/components/schemas/Record"
}
},
"offset": {
"type": "string"
}
}
}
}
}
},
"422": {
"description": "Iteration may timeout",
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/Error"
}
}
}
}
},
"security": [
{
"bearerAuth": []
}
]
}
}
},
"components": {
"schemas": {
"Table": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"primaryFieldId": {
"type": "string"
},
"fields": {
"type": "array",
"items": {
"$ref": "#/components/schemas/Field"
}
},
"views": {
"type": "array",
"items": {
"$ref": "#/components/schemas/View"
}
}
}
},
"Field": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"type": {
"type": "string"
},
"description": {
"type": "string"
},
"options": {
"type": "object"
}
}
},
"View": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"name": {
"type": "string"
},
"type": {
"type": "string"
}
}
},
"Record": {
"type": "object",
"properties": {
"id": {
"type": "string"
},
"createdTime": {
"type": "string",
"format": "date-time"
},
"fields": {
"type": "object",
"additionalProperties": true
},
"commentCount": {
"type": "integer"
}
}
},
"Error": {
"type": "object",
"properties": {
"error": {
"type": "object",
"properties": {
"type": {
"type": "string"
}
}
}
}
}
},
"securitySchemes": {
"bearerAuth": {
"type": "http",
"scheme": "bearer"
}
}
}
}
4. Results, limitation & further materials
It does work, it gets you info about your Airtable, answers questions about data and makes charts:
It also sometimes does not work:
- field name hallucination might occasionally happen
- despite instruction, sometimes it tends to ignore offset and stop getting data from API after 200-400 rows
- with too much data it is possible to overload the context and have it crash while generating charts
- limited understanding of data beyond field name, adding wider context provide better search results
Despite limitation listed, having ChatGPT app on your phone and being able to use voice to ask, how many orders we got today and from where... is pretty neat.
Do check out also my video where I go over the whole build out process:
