Botpress + + Airtable
# 📖tutorials
This is how the final scenario should look like
Log in to your Airtable account Create a new blank base Select Start from scratch
Create a new table with columns Name, Age, City, Superpower and fill those with data Give the table name Friends
Login to your account and create a new scenario, change the name from "New scenario" to "Airtable"
Click the big PLUS icon and search "webhook" Select Webhook, then Custom webhook
Create a new webhook, "Airtable data"
click Save, and then OK
Add another module, search "airtable" and select "Search Records"
Create a new connection, click Add, Connection type, select Airtable OAuth then you need to give access to connect to your Airtable account Add a base -> select the Friends base created earlier
Base: select "Friends" Table: select "Table 1" Output fields: Select all and click OK
Create the next module, click "Tools", select "Array aggregator"
Source Module: select "Airtable - Search Records" and click OK
add another module, search "json" select "Create JSON"
Data structure: click Add Data structure name: Airtable then click Generate
paste this code to Sample data field
Copy code
{ "friends": [ { "name": "Name", "age": "Age", "city": "City", "superpower": "Superpower", "id": "ID", "created time": "Created Time"  } ]}
and click Generate
Check that your data structure is correct and click Save
click Map and select the Array[] (so that data comes from Array aggregator) click OK
go back to Array aggregator, click Target structure type, select Friends and map everything to the correct fields
Add the last module, select Webhook response click "Body" field select Array[] and click OK
then scenario should look like this
Go to your Botpress account, create a new bot, start from scratch Create a new KB table, name it "Data1Table" add the same columns to match the Airtable, but use lower case letters here "name", "age", "city", "superpower" add also two extra columns to track Airtable data "airtable_id", "created time"
Go to your main workflow, with only one Standard Node Add Execute code card and paste this code:
Copy code
async function makeAsyncRequest() {
  const webhookUrl = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

  try {
    const response = await
    const webhookResponse = JSON.stringify(
    console.log('Log response: ' + webhookResponse)
    await deleteAllRecords()
    return webhookResponse
  } catch (error) {

const newString = await makeAsyncRequest()
console.log("newString: " + newString)

const data = JSON.parse(newString)

const modifiedData = => {
  const { id, } = item;
  return { airtable_id: id, };

await createNewRecords(modifiedData)

async function deleteAllRecords() {
  try {
    const data = await Data1Table.findRecords({ selectorName: 'allRecords' })

    if (!Array.isArray(data)) {
      console.log('No records found.')

    const recordIDs = =>

    if (recordIDs.length > 0) {
      await Data1Table.deleteRecords(recordIDs)
      console.log('Old records deleted.')
    } else {
      console.log('No records to delete.')
  } catch (error) {

async function createNewRecords(sheetsData) {
  try {
    await Data1Table.createRecords(sheetsData)
  } catch (error) {
go back to your scenario, and from the first Webhook module, copy the webhook address and replace it to that code (I don't mind showing my webhook address here, I'll delete that)
Click scheduling ON, use Immediately as data arrives on the first Webhook module click "Redetermine data structure" and click OK
Try to run your chatbot one time
check that the Airtable data is correct
then when you run your bot again, it shows all the correct data in the logs
and it has also received all Airtable data to Botpress KB table
If you update your Airtable data and you run the bot again (or the next chatbot user goes to that workflow)
the new data is updated to Botpress KB table
Since I used characters from Friends in this example and updated Chandler's location, I want to end this tutorial by saying: Rest in peace, Matthew Perry 🫡 ✨
Thank you for sharing @quick-musician-29561, Airtable is a powerful tool
You are on fire @quick-musician-29561 ! 🔥
this is game changer for me jajajaa, it works superrr!! thanksss @quick-musician-29561
That is the best possible feedback! 🫡 🔥 🛠️ Here's the bot file also, even thought it's only a small template and I shared it earlier in other thread.
Especially when connected to Botpress, Airtable is a very useful platform for us AI bot builders 🛠️ 💎 I noticed that there were over 100 messages or posts related to Airtable topics, and a many of them were unanswered questions from dozens of Botpress users. So now I'd like to ask if you fellow bot builders have already got Botpress working well with Airtable, and have a look if this tutorial is of any help in your use cases 👍 @fancy-shampoo-27308 @bulky-island-7146 @brave-dawn-96867 @chilly-church-46005 @brave-butcher-38884 @billions-dawn-55821 @melodic-dusk-37332 @magnificent-plastic-390 @stocky-salesmen-80530 @tall-market-29797 @cold-jewelry-54343 @clean-doctor-91284 @prehistoric-dog-28186 @handsome-sundown-8633 @busy-forest-12326 @flat-monkey-40370 @magnificent-belgium-43933 @big-wall-28234 @adorable-noon-42596 @curved-psychiatrist-13525
I've received DMs from bot builders who haven't gotten Airtable to work properly with Botpress yet, and it's a vital feature for their clients. I've been helping them for hours over the past week privately, so that's why I wanted to put a little extra effort into this also publicly to benefit a larger number of Botpress users 🫡 🛠️
@best-army-74344 If you or others got this to work well for your use cases, there's one bot builder who hasn't got it to work yet, and the experiences of others on what to change would certainly help 🙏
you are the best! thank you so much for all you do for the community
I think it's great to be involved in others' Botpress projects, see how they build AI bots and share ideas. And as it has often come up with other bot builders, we all have our own strengths, and when we work well as a team, their strengths are also my strengths and vice versa.
I think you're ready to become a botbassador 😉 ! @quick-musician-29561
Hello, @quick-musician-29561 Nice job you know. Я code the same like you, but failed. { "Occupancy": [ { "dateStart": "Date start", "dateEnd": "Date End", "guestName": "Guest name", "rooms": "Rooms", "objectName": "Object name", "status": "Status", "deposit": "Deposite (note)", "cleaningNote": "Cleaning note" "id": "ID", "created time": "Created Time" } ]} Could you check, please, what's wrong ?
I finished all way and set up the same like you, but I have error. Could you understand what is it?
And in make too
The error message says "Invalid JSON". I've noticed many times that we have to be careful with them, if even one character is wrong, it won't work { "Occupancy": [ { "dateStart": "Date start", "dateEnd": "Date End", "guestName": "Guest name", "rooms": "Rooms", "objectName": "Object name", "status": "Status", "deposit": "Deposite (note)", "cleaningNote": "Cleaning note" "id": "ID", "created time": "Created Time" } ]} there is a missing comma (,) between the "Cleaning note" and "id" properties, that'w why it doesn't work. When you correct that, it should work
Copy code
  "Occupancy": [
      "dateStart": "Date start",
      "dateEnd": "Date End",
      "guestName": "Guest name",
      "rooms": "Rooms",
      "objectName": "Object name",
      "status": "Status",
      "deposit": "Deposit (note)",
      "cleaningNote": "Cleaning note", // ADD COMMA (,) HERE
      "id": "ID",
      "created time": "Created Time"
Hi, @quick-musician-29561 you are actually right. I lost comma, really. I corrected it, json is working now. Thanks.
But it is actual mistake and workflow isn't working
We can try to solve all the errors together 🛠️ I've now helped many other bot builders here to get to work for their project. Usually the biggest problems are that the requested data doesn't match what sends back (variable or column names don't match for example), and when we fix small things like that, in the end it always works 👍
Hello, @quick-musician-29561. I have delete function to clear my table inside a botpress bot // Deleting all existing records from the occupancy1Table await deleteAllRecords(); I have 22 records in the table, but function are deleting only 20 records. Next try I have 2 old records + 22 new records, next try 4 old rec + 22 new, ... etc Why are await deleteAllRecords(); deleting only 20 records?
I already solved ti with the part of code: // Function to delete all records from the occupancy1Table async function deleteAllRecords() { try { let allDeleted = false; // Flag to track if all records are deleted while (!allDeleted) { const existingRecords = await occupancy1Table.findRecords({ selectorName: 'allRecords' }); const recordIDsToDelete = =>; if (recordIDsToDelete.length > 0) { await occupancy1Table.deleteRecords(recordIDsToDelete); console.log(
${recordIDsToDelete.length} records deleted.
); } else { allDeleted = true; // If there are no more records to delete, exit the loop console.log('All existing records have been deleted.'); } } } catch (error) { console.error("Error while deleting records: ", error); } }
Yes!!!!! Thank you @flat-dawn-22973 🔥 🛠️ 💎
All these great additional ideas will be very useful for Botpress chatbot builders ⭐
I was just about to comment that 20 records is probably the default value then. I would try setting it bigger (if it's not a max value already), or to do pagination, checking if the code has already deleted all records, if not then doing it again until all records are deleted. Like in Botpress docs: You were faster with that, I will test your idea and code as well 💯 👍
Hey @quick-musician-29561, I trying now to change the values, but I get this, Could you help me with the sample data structure
I used this JSON with your data:
Copy code
    "data": [
            "combined": "combined",
            "title": "title",
            "location": "location",
            "typeofexperience": "typeofexperience",
            "price": "Price",
            "image": "Image",
            "complete": "complete",
            "pricedy": "pricedy",
            "url": "Url",
            "tipo": "Tipo",
            "experience": "Experience",
            "typeprecio": "typeprecio",
            "preciosgp": "preciosgp",
            "combinada": "combinada",
            "duration": "Duration",
            "completa": "completa",
            "activity": "Activity"
I updated the code to use your much larger data set, removed one item from your Airtable data, ran the bot again and it did its normal Botpress magic ⚡ (now I'll move away from this tutorial to your Help-section thread)
The last webhook status is 200
but orm some reason creating the records give me error