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.

(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

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)
  }
}

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 🛠️

Hi can you help me. when I tried this.

it is null value

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

The more bot builders test and try, the better this project will eventually become! 👍

have no idea what this means tho. if anyone encountered the same exception :D

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

worth staying up for

or you can give us some examples of your google sheets, columns, what kind of data etc

if we can make it work together

This is why you need the csv file

Now worked lol

Let's fix all these Make.com related issues together Friday-Sunday 🫡 I have a few others already waiting in my inbox 🛠️

I'm really interested in sending data tables from botpress to google docs

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 😂

I think most of the other Make.com related issues were solved during the weekend, and also a little progress with Botpress+cron schedule

a non variable range large enough to fit the required data

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

"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:

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! ⚡

thanks again!

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.

@cold-jewelry-54343 The use case you just described sounds AWESOME!! 🔥 🔥 🔥 🔥 🔥

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.

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

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

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

that part shows the error

maybe if you delete it and add it again

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.

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

i used this for the JSON: { "Fallback chatbot pegaso": [ { "pregunta": "pregunta", "respuesta": "respuesta" } ]}

@quick-musician-29561 ????