INTRO:
If you’re not a coder then how can I do Java code to make a script? If you guessed it, yes we use LLMs to help us like ChatGPT. But you tried that and it never worked. Welp, there’s some basics you need to know to appropriately instruct, in your prompt, the right details to get a script properly produced. This post will give you the knowledge and instructions to successfully start your scripting journey.
WHAT YOU NEED & SHOULD KNOW TO DO:
It is ideal that you know how to build automations with the provided Airtable actions. By this point, you’re figuring out that the basic actions are not able to accomplish certain functions you want. What you do need, is to have access to is some form of LLM like ChatGPT, Claude, etc.
PREFACE:
I’m writing this, because I’m not a coder and early on I struggled a lot in figuring out how to build scripts. The early version of ChatGPT was not great but now they are quite powerful. I like to cater information to the average person. We have some amazing and talented Airtable developers in this community but I want to ensure, you don’t feel intimidated to tap the community. I scoured the past 8 years of post to find any details of scripting for beginners and I didn’t find anything that comprehensively hit “What do I do? Teach me.” Many posts related to beginners were use cases. So this is for you, your everyday Airtable person wanting to take your game to the next level.
1. WHAT YOU NEED TO KNOW ABOUT AIRTABLE FOR SCRIPTING:
This is the most important part you need to understand. Airtable has Tables and Fields. The Fields have many functions from Single Line Text, Single Select, Number, Email, etc. But each of these field’s value, that are in the cells, have a data type. When you run a script, Airtable converts field values into native JavaScript data types. For example in the table below:
| Airtable Field Type | Typical JS Type |
|---|---|
| Linked Record | “array” of record references |
| Checkbox | “boolean” |
| Long Text | “string” |
| Collaborator (single) | “object” |
| Collaborator (multiple) | “array” |
| Date / Created Time / etc. | “date” |
Why is this important to know? Airtable’s field types don’t always correspond 1-to-1 with JavaScript data types in scripting. That mismatch is the root of many subtle bugs. You need to be aware of the fields you are using in your script thus when we use the LLM, we need to clarify and detail exactly the “Field Type” it is. When you used ChatGPT to write a script and it didn’t work, this is most likely because it did not understand the data types it was working with.
2. BUILDING A PROPER PROMPT IN YOUR LLM TO DESIGN YOUR SCRIPT
The other reason why your script failed using an LLM to design it, is because it may have been poorly written. Aside from missing details from Step 1, if you don’t create clear instructions, the LLM will do its best to produce the results by filling in the blanks. If you’re not particular savvy about LLMs or know what they really are, it is very easy to have a low quality prompt. LLM’s are TASK specific tools. They operate off of instructions to perform tasks much like you giving instructions to a worker, student, or child. The more ambiguous the instructions the more ambiguous the results. Bottomline, take the time to be thorough about what you want your automation to do. Below I will give a basic framework for building prompts. There are by far solid information on the web that can go in depth about prompts but below is a great start to keep it simple.
Key details that you should describe in your prompt:
- The exact name of the Table or Tables you’re using - I use “ “ around the names to ensure there’s no mistake of the full name
- The exact name of the Fields you’re using - same thing, I use quotes around the names
- When informing the Field’s name, especially the first time, ensure you describe what kind of field it is
- The exact name of values you use - use quotes around them
- Take it step by step on what you want the script to perform and try to keep it linear. You don’t want to jump around in how it executes to reduce any errors.
- Give the prompt a persona - this is typically always the same, I use “You are an expert in javascript coding for building scripts to use in Airtable automations.” You do this to give the LLM a frame of reference what it is and what it will do.
Putting it together this is what it would look like in a prompt:
- You are an expert in javascript coding for building scripts to use in Airtable automations. I am going to use the trigger, When record matches conditions. In the Table “Toy Inventory”, I have a single select field called “Toy Type”. When the value in “Toy Type” is “Stuffed Animal”, you will create a new record in Table “Toy Tracker”. The “Toy Type” being “Stuffed Animal” is the trigger. For the new record created, in the linked record field called “Toy ID”, insert the value from the number field called “Serial Number” from Table “Toy Inventory”.
As you can see, there’s back and forth happening. It’s imperative you keep things systematic, clear, and simple. I made sure I describe every table and field used in the process. The LLM is smart enough to know based on the field types, how to convert the data types in the script. In the example the Serial Number is a number value that will be converted into an array in the linked record field for Toy Tracker.
3. SCRIPT SETUP, TESTING AND DEBUGGING
When you get the completed script, you’ll copy and paste into your script action. For the example above, the trigger is “When record matches conditions” but whatever trigger you use, the LLM understands that it needs to get a record ID. In the image below this is going to be a typical setup you need to do. You know to do this if you see in your script “recordID” being used. Thus the inputs need to be setup as shown.
Once that is done, lets test it by hitting the Test button at the top right. You may have to setup the condition that causes the automation to trigger like picking a record and setting Toy Type to Stuffed Animal. When it runs, it is not uncommon to have an error like shown in the image below. I copy the error, all of it and paste it back into the LLM chat I used to create the script. I’ve found that it is not necessary to do a bunch of prompt engineering for this. A simple paste it and hit run and the LLM will analyze the script to figure out why the error occurred and it will provide a solution to fix it. You can always ask in the prompt to provide a detail analysis and recommendation for a fix based on the error provided.
You may have to rinse and repeat the test and debugging but typically if you were thorough with your prompt, you shouldn’t have to do much. You might want to ensure that all names used are exact. Even a capitalization of a letter missed will cause an error or adding/not having a space in the name will affect results. BUT, there are some very rare cases that the error occurred has nothing to do with the script. It may be that it noticed a condition did not exist as it was working through multiple tasks and had to stop. Just be diligent on the explanation that the LLM gave you.
To note, when you do test and it is successful, it does a live test. You can see it working in your table if you flip to it fast enough or have the data table open in another window.
CLOSING
The power of LLMs has drastically opened up capabilities to any individual. For this case, even though LLMs is doing the scripting for us, it does not negate having good knowledge about Airtable. Once I understood about data types in relations to javascript, I fixed and overcame many of my prior issues. I applied that to my handy work in doing good prompt engineering. Since then, many of my complex automations run off scripts but it is also my last resort if I cant make standard actions work properly.
I want to share one tip on how I use scripts for cleaning my data. Sometimes, it’s a real headache to use excel and all its functions to organize, align, match, etc the data in the spreadsheet. I found it easier to load it into Airtable, and just write a script to do what I want it to do for the whole data table. The only difference I make is that I instruct it to review the whole table not by a trigger. If you do this, you don’t need to have an input, the ‘recordID’. You do have to have a trigger but it doesn’t matter since your not going to activate it. You’re going to test the script to perform your action. As mentioned earlier, the test is a live action. Viola, your data is cleaned.
Cheers everyone, I hope this was helpful. Once you do this several times, many of the posts in the community about scripts will start to make sense. You’ll have the foundation to put the mechanics together.
Best,
Mike