Sync Botpress KB Table with Google Spreadsheet
# 📖tutorials
q
Log in to your Google Sheets account and create a new empty spreadsheet with a name "Users". Create three columns: Name, Age, City and fill in three rows with sample user data.
Log in to make.com and create a new scenario
Click on the big + icon, search Webhooks and select Custom webhook. Click "Create a webhook", name it "Botpress data" and click Save. The most important thing here is the address of the webhook, which we will need later. Click OK and you have created the first module (out of five).
Right click to add a second module, search for "google sheets", select that, then scroll down and select a module named "Get Range Values"
Fill in the correct information: **Connection**: Select your google account and sign with it, allow Make.com to use your Google Sheets. Enter a Spreadsheet and Sheet ID: Choose "Select from the list" Choose a drive: My Drive (shows your google sheets account) Spreadsheet ID: click and choose a correct file named "Users" Sheet Name: Sheet1 (since we only have one sheet) Range: type A2:C4 (which means we select all the User data excluding headers, from Column A and Row 2 to Column C and Row 4) Click OK to create the Google Sheets module.
b
🔥 🔥
m
Fantastic, Make is such a great tool
j
@busy-eye-77875
b
Part 2 😅🌚?
q
I agree, after a few days of intensive use, Make is one of my favorite tools to use together with Botpress 💎 However, within a few days, I used all the free 1000 ops (operations) just for my own testing and had to take a paid monthly package with 10,000 ops monthly. It doesn't Make so much sense from a bot builder's 🤖 💸 point of view, so I'm updating the tutorial a bit now, as well as the Botpress part. I'm removing all the unnecessary timers and making it so that the Botpress KB table is updated and synchronised by Make.com to Google Sheets ONLY when the user wants it
(updated tutorial continues ⬇️ ) Test: Execute the Google Sheets module (right-click it and choose "Run this module only") and verify its output, you should see your data there.
Add another module, from Tools / flow control (down below) select "Array aggregator" Source Module: Select "Google Sheets - Get Range Values (this is where the JSON module gets its data) Target structure type: (leave this and other fields empty for now)
Add a new module, search "json" and select "Create JSON" Click the Add button to open the Data structure setup. I used the default data structure name "My data structure". The easiest way to create Data structure is to generate it automatically from a JSON sample. Click the Generator button and paste the following JSON sample to the Sample data field:
Copy code
{
  "users": [
    {
      "name": "Name",
      "age": "Age",
      "city": "City"
    }
  ]
}
Click Save.
Toggle the Map, click on Users field and select "Array []" (this is how the data is outputted to this module from Array aggregator to JSON module), then click OK.
Go back to "Array Aggregator module", make sure that Source Module is Google Sheets, change Target structure type to "Users". Map items (in columns A, B, C) from the Google Sheets module to the appropriate fields (name, age, city). Click OK to finish the module.
Add the fifth and last module, select Webhooks, Webhook response. On to Body field, select "Array []" Click OK.
Now Make.com part is done. I have done some testing and everything works 💡 Next step is the Botpress part, so this tutorial will continue but it'll need much more testing ( I'm writing this as I build 🛠️ ).
Part 2. Botpress template Create a new chatbot, start from empty template Add a new Knowledge Base Table, name it "GoogleSheetsTable"
Add three columns to that KB Table: name, age, city to match with the data in your Google Sheets. IMPORTANT: use lower cases to match the data from make.com in this example
Create a new Standard Node and add the Execute code card to it.
Copy this code to Execute code card
Copy code
js
async function makeAsyncRequest() {
  const webhookUrl = 'https://hook.eu2.make.com/xxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
// REPLACE xxxxxxxx WITH YOUR MAKE.COM WEBHOOK ADDRESS
  try {
    const response = await axios.post(webhookUrl)
    const webhookResponse = JSON.stringify(response.data)
    console.log('Log response: ' + webhookResponse)
    await deleteAllRecords()
    return webhookResponse
  } catch (error) {
    console.log(error)
  }
}

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

const data = JSON.parse(newString)
await createNewRecords(data)

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

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

    const recordIDs = data.map((record) => record.id)

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

async function createNewRecords(sheetsData) {
  try {
    await GoogleSheetsTable.createRecords(sheetsData)
  } catch (error) {
    console.log(error)
  }
}
b
You are a Legend my G. If i sync the MINDBODY Database with google sheets and sync google sheets with botpress. Do you think that will work?
q
Go to make.com and start the Scenario from the bottom left: "SCHEDULING" In Run scenario use: Immidiately to run it immediately as data arrives.
Try your chatbot and type "Hi!" Correct data (which we created in the opening post) comes into Botpress chatbot, and now we have a KB table called "GoogleSheetsTable", which is synchronized with your Google Sheets.
When you update the information in Google Sheets, and the next chatbot user goes to this node or workflow to start the webhook trigger, they will immediately receive updated KB table information which they can ask questions.
@busy-eye-77875 wrote: "If i sync the MINDBODY Database with google sheets and sync google sheets with botpress. Do you think that will work?" 🫡 That is a good idea! 💡 In the coming days and weeks, we can try to build and replace this little template which only uses dummy data with something that actually makes sense 😂 The best feedback on this kind of thing is always 💥 1. NEW IDEAS - the bot builders tried it and came up with even better ideas 2. TESTED AND IT DIDN'T WORK - it is important to find all the possible (and impossible) bugs that they can be fixed 3. TESTED AND EVERYTHING WORKED !!! - then we have a little template ready for all future Botpress chatbot builders
Download the file @best-army-74344 🛠️
e
message has been deleted
Hi can you help me. when I tried this.
it is null value
q
Did you get any error messages?
remember to check that KB table column names are exact match to data from the google sheets
from the tutorial ➡️ IMPORTANT: use lower cases to match the data from make.com in this example
so when I used this: { "users": [ { "name": "Name", "age": "Age", "city": "City" } ]} Botpress KB table column names should be name, age, city
if you used something like this: { "yourStock": [ { "shoes": "Shoes", "shoename": "Shoename", "instock": "Instock" } ]} in Botpress KB table change the column names to lower case also: shoes, shoename, instock
e
Got it! Thank your a life saver.
q
🤝 🛠️ 💯
The more bot builders test and try, the better this project will eventually become! 👍
m
looks awesome!
f
Can you do this but with an assistant api?
j
hmmm, can we ask chatgpt to update the google sheet and will it update the table ?
q
I'm guessing we can 💡 🛠️
j
so i ask chatgpt to put something into the followijng google sheet, then it updates it. Then do we have to download it and change the info again ?
d
thanks for this mate!! so i need to make tables in botpress as well right but i have like multiple tabs on google sheets how can that work?
f
im working with this now. it should be ok as long as u put the correct sheet name
have no idea what this means tho. if anyone encountered the same exception :D
q
I think it means webhook was only "Accepted" to make.com, didn't give any results yet (Webhook response)
I noticed the same errors when trying to build it
you can try "Run this module only" with each module, and check if they give the correct data
f
okay thanks mik, great post btw
worth staying up for
q
normally for me, it was because wrong sheet name, or incorrect mapping (in make.com modules)
d
I am stuck on json i mean my google sheet is little complicated it has insurance rates etc so should I add manually?
q
I can try that also with multiple and complicated google sheets
or you can give us some examples of your google sheets, columns, what kind of data etc
if we can make it work together
d
q
Great! ⭐ I’ll let you know if I figure out how to make it work 🛠️
d
thanks mate
b
@quick-musician-29561 Hi again😂, Let say that the Google sheet is the table i use for find records, will it works???
q
I've heard rumours that some hard-working bot builders have already built one. Let's try to build that too!
j
@better-napkin-1703
@hallowed-lizard-35251
This is why you need the csv file
m
Same error, but I did everything like @quick-musician-29561 I think, how did u solve it?
Now worked lol
q
Great! I was just going to say that I commented to similar issue earlier here https://discord.com/channels/1108396290624213082/1180929476624912394
Let's fix all these Make.com related issues together Friday-Sunday 🫡 I have a few others already waiting in my inbox 🛠️
m
Yep I saw it, now I will try the other way, from Botpress -> make.com->Google Spreadsheet
I'm really interested in sending data tables from botpress to google docs
q
Same here! 🔥 🛠️ 💎
I gave access to my Make.com account to a few bot builder friends who wanted to see how I built scenarios in my tutorials. I logged in and they have created new scenarios for me that are even running already, all I have to do is start working. Best was of course this 😂
m
are you used to work with make.com?
j
yes
m
Did you manage to create something? I'm back in the office and now I have the data table in a string, I have to create the axios request to send the data to make.com
q
I'm also trying something similar today 🧑‍💻
I think most of the other Make.com related issues were solved during the weekend, and also a little progress with Botpress+cron schedule
m
okey, I will talk to you later to check if we made some improvements!!
@average-address-21393
f
After some fiddling about got his to work, the mistakes I made as a new Made.com user, in case it helps others were to accidentally miss out the step of mapping columns in the Array Aggregator and also not hitting save. Also missed the step of creating a table selector in the Botpress KB table. Works fine now.
b
hey @quick-musician-29561, this works absolutely great. Can we make the range dynamic tho. Can we have variable number of rows?
q
Yes, I have tested this, for example with Ramon Lepage. We had hundreds of rows and it worked well. Just make sure you use a large enough range here: Range: type A2:C4 (this means we select all the User data, excluding headers, from Column A and Row 2 to Column C and Row 4). Try for example A2:C100 or if you have more columns, A2:G100 etc. Let us know if you encounter any issues; then we can continue testing together.
b
okay so you are telling me to keep the range large enough so that the numbers of rows in the actual spreadsheet occupied are always less than this range?
a non variable range large enough to fit the required data
q
That's what I learned from Make.com documents regarding Google Sheets.
b
okay okay cool!
thank you man, this tutorial is just so important
great work
Hey @quick-musician-29561 apparently the deleteAllRecords() function is not deleting all the records its deleting only a few I had 48 entries and i ran the execute 3 times and now my table contains 144 entries im not able to figure out whats wrong
q
@best-gigabyte-81367 🫡 Check out the last messages in here, mainly the solution from @flat-dawn-22973 🛠️ https://discord.com/channels/1108396290624213082/1186610522498539520
"I had 48 entries and i ran the execute 3 times and now my table contains 144 entries" 😂 we had the exact same issues when testing this first time 💡
(this is what I used to make sure all the records are deleted) Code for larger datasets:
Copy code
js
async function makeAsyncRequest() {
  const webhookUrl = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
// REPLACE xxxxxxxx WITH YOUR MAKE.COM WEBHOOK ADDRESS

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

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

const data = JSON.parse(newString)

await createNewRecords(data)

async function deleteAllRecords() {
  try {
    let allDeleted = false;

    while (!allDeleted) {
      const data = await Data1Table.findRecords({ selectorName: 'allRecords' });

      if (!Array.isArray(data)) {
        console.log('No records found.');
        allDeleted = true;
      } else {
        const recordIDs = data.map(record => record.id);

        if (recordIDs.length > 0) {
          await Data1Table.deleteRecords(recordIDs);
          console.log(`${recordIDs.length} records deleted.`);
        } else {
          allDeleted = true;
          console.log('All existing records have been deleted.');
        }
      }
    }
  } catch (error) {
    console.error("Error while deleting records: ", error);
  }
}

async function createNewRecords(sheetsData) {
  try {
    await Data1Table.createRecords(sheetsData)
  } catch (error) {
    console.log(error)
  }
}
Chatbot file for that (using Airtable instead of Google Sheets) https://discord.com/channels/1108396290624213082/1189637391753945098
Let us know if it works as intended, and if not, then we'll fix it together. After that, we'll have the final solution in this thread as well, thanks to your testing and contributions! ⚡
b
this works!
thanks again!
c
lovely topic 💪 Great example of the botbuilders community coming together. I do have some questions: Q : everytime an update happens there will be an API call (?). Can we bundle changes inside an object so we can update many records at a time ?
currently Im building a CRM dashboard for our client who would like to update records from the Google Sheet that are used by Botpress. I believe having the database in Airtable/Google Sheets seperate from Botpress is valuable but for internal speed/calculations using the Botpress tables can be handy. It can also act like a back-up of your data. Another benefit to fetching variables directly with a webhook is that syncing your table with your database is
dynamic
: any record you add will automatically be added to your
table
making it futureproof for expanding databases. My current stack: Softr/Bubble for CRM Dashboard < make.com> Google Sheets Botpress
table
. Technically we could cut-out Google sheets and use Botpress as our knowledge base but like mentioned above I prefer to have it seperate so non-technical users can view/make edits.
q
I think you have solved these already 🫡 but for those who are not, when the client updates google sheets, chatbot users get all the latest data, all the records.
@cold-jewelry-54343 The use case you just described sounds AWESOME!! 🔥 🔥 🔥 🔥 🔥
s
Hello, greetings to everyone. I am experiencing an error that I haven't been able to solve while following the tutorial. When testing the last webhook module, it generates this error, and I haven't been able to identify its cause. Any ideas?
q
@swift-garage-22701 It appears that the issue occurs when you test the last module (or the entire scenario) within Make.com. To address this, try testing it from your chatbot. And before doing so, remember to: Click 'Save' after making changes. If it's still not working: Click 'Redetermine data structure' again. Then, test it using 'Run once' until you resolve the issue.
You have most likely done this already, but I always double-check that I have the correct data in every scenario. Google Sheets data
then after you run it from your chatbot
@swift-garage-22701 🫡 Just yesterday, we were fixing the exact same issue with other bot builders. Let us know if this works for you, or if not, we can ask how they did it.
c
I think the next stage is to find out how to use the Botpress API so we can update our Botpress data without every user firing the script.
s
Hi, devmik. I'll give you an update. I have applied "Redetermine data structure," in the first module, but it stays processing and doesn't complete despite leaving it for long time. Regarding the error I reported, it occurs in the last module when I click on "Run once." Apart from that, I have checked all the mentioned validations. The data is the same in everything just like your instructions.
Another situation that is occurring is that if I click on the first module "Run once," it stays loading and doesn't execute.
I am receiving this in Botpress.
I have imported your bpz file and connected it to my webhook. I ran a test, and now I'm getting this error.
Now the first module runs well
q
Super! 💎 I have read all your messages
I was debugging this exact same thing yesterday with @cold-jewelry-54343 🛠️
You can export the make.com scenario here if you like, and I and others can help with debugging
yesterday we used the exact scenario, code and google sheets as in this tutorial
s
Great, thanks a lot. This is the json generated from make.com
q
I think this module was the issue last time
s
Same situation here
q
I started it also, let's compare every module
Second module, Google Sheets
This way, we should be able to determine where the issue lies.
and the Google Sheets (Get Range Values) module should look like this
s
Yeap, this one's mine.
q
How is the received data on that module?
s
q
let's move to Array aggregator with Amanda 👩‍🦱
s
Sure
q
Create JSON
s
q
Last module
s
q
s
q
can you see the correct data if you click 'Body'
that part shows the error
s
q
can you change this -> 6. Array to this -> 3. Array
maybe if you delete it and add it again
s
Ok, let me try
q
I was thinking if you have some extra Array aggregators there, as I usually have then testing and building these
for me it shows Array aggregator is 3, and for you also except in that last Webhook module where it says 6.
s
Got it. I'm gonna redo the process and delete the stuff I don't need so the numbering is right.
q
Let's continue after that if needed! 🫡 This is going to be useful for other bot builders 🛠️
s
Sure, thanks
b
hey @quick-musician-29561 I wanted to use this concept of syncing the botpress table with google sheets for syncing the products in shopify with botpress table
I tried it using a shopify module instead of google sheets module
the webhook response is just fine
there is no error shown in botpress either
but the table doesnt seem to update
this is the scenario👆
async function makeAsyncRequest() { const webhookUrl = 'xxxxxxxxxxxxxxxxxxxxxxx' // REPLACE xxxxxxxx WITH YOUR MAKE.COM WEBHOOK ADDRESS try { const response = await axios.post(webhookUrl) const webhookResponse = JSON.stringify(response.data) console.log('Log response: ' + webhookResponse) await deleteAllRecords() return webhookResponse } catch (error) { console.log(error) } } const newString = await makeAsyncRequest() console.log("newString: " + newString) const data = JSON.parse(newString) await createNewRecords(data) async function deleteAllRecords() { try { let allDeleted = false; while (!allDeleted) { const data = await ProductsTable.findRecords({ selectorName: 'allRecords' }); if (!Array.isArray(data)) { console.log('No records found.'); allDeleted = true; } else { const recordIDs = data.map(record => record.id); if (recordIDs.length > 0) { await ProductsTable.deleteRecords(recordIDs); console.log(
${recordIDs.length} records deleted.
); } else { allDeleted = true; console.log('All existing records have been deleted.'); } } } } catch (error) { console.error("Error while deleting records: ", error); } } async function createNewRecords(sheetsData) { try { await ProductsTable.createRecords(sheetsData) } catch (error) { console.log(error) } }
this is the code i used👆(Exact same code used for syncing with google sheets)
this is the screenshot of logs Here👇 you can see that there was no error but the table is still empty
nevermind I figured out the problem
f
Hi @quick-musician-29561 and community, need some help here im getting crazy 🥲 i follow all steps and i having the NULL error (no data is obtained from the google sheet and transferred to the botpress KB. I also tried to lowercase everything to avoid mistake with low and uppercase variables, but im having same error. see pictures attached
i used this for the JSON: { "Fallback chatbot pegaso": [ { "pregunta": "pregunta", "respuesta": "respuesta" } ]}
@quick-musician-29561 ????
3 Views