https://discord.gg/botpress logo
Join Discord
Powered by
Getting data from a Google Sheets file in a Botpre...
# 🤝help
b
Hi, I want to get data from a Google Sheets file to display in a dynamic carousel on a Botpress chatbot using Make. Can someone please show me which scenario I should create on Make.com and which code to execute on Botpress? Thank you!
f
@quick-musician-29561 You're the expert
q
Check out this https://discord.com/channels/1108396290624213082/1181662097046786118 and also other Botpress + Google Sheets related projects in #1132038253109837994
b
Thanks! I followed all the steps but want to display the sync KB table with sheets in a dynamic carousel. How can I do that?
q
If you have image URLs in Google Sheets, then follow the steps from that tutorial, and display the KB table in a carousel. But if you have actual images, then maybe that doesnt work, since at the moment Botpress requires URLs to display images.
b
I have image URLs. Where's the tutorial?
q
This same tutorial https://discord.com/channels/1108396290624213082/1181662097046786118 There I had Name, Age, City columns in KB table. You could have ImageName, ImageURL Then you can use that ImageURL in Carousel Image
Interesting idea 💡 💥 I can also try to build that
b
I tried to do the step in the tutorial video of Botpress about dynamic carousels, but it didn't work for me. I don't know if it's the right method in my situation after getting data from the sheet via Make after following your tutorial, I followed all the steps and it worked perfectly for me, but to display the KB table in a dynamic carousel I don't know exactly how to do it
q
Do you want there to always be a certain number of images, for example 5, or does it change?
b
No, only one image per card, but I want to use an unlimited number of cards because I created the Google Sheets for my client to be able to update by himself automatically the carousels from the sheets by adding the new properties without asking me every time to add properties manually for him inside Botpress, this is why I decided to create this Make automation
But I'm not sure if "Find Records" is the right option for me, I have just followed the botpress video tutorial
q
One more question, you have Google Sheets data already in KB table successfully?
b
Yes
q
Great! Then I can also start to build from there 🛠️ ⚡
b
Your tutorial was super efficient, especially for a beginner with no-code skills who had just learned how to use Botpress a few days ago
But now I'm struggling with the next step which was the main reason for me to create this whole automation
q
Feedback like that always keeps me motivated! 🫡
An easy and official solution to this Carousel issue is coming to Botpress in the coming months (based on comments from the Botpress team). Until then, we will try to build these things ourselves.
b
So what is the solution right now?
q
I can try to build something today, I'll get back to you ⚡
so we can test it together
b
Ok, thank you so much. You are a lifesaver because this issue is so urgent for me. My chatbot is almost ready, but I should finish it and present it to my client by Tuesday. That's why I feel so stressed
I also have an additional question: When we create a scenario on Make, it's only one sheet page per module (webhooks and google sheets in my case)? What mean we have to create multiple scenarios for each sheet? Or we can include multiple sheets in the same module?
This method worked for me
q
I also tried wit Find Records
with three rows in KB table
then this your method to display those in Carousel card
b
q
and it shows them like this
Same here! ⭐
b
So I have just to add "Find all records" to the filter
q
Yes
This works, and it's "okay" solution, but it shows always the same amount of Carousel images, for example 5 or 10 (I don't know the maximum). With that you can already show the chatbot to the client I guess.
But if we want the amount of Carousel images the change, based on how many images are on Google sheets and KB table, that needs some workarounds until the official Botpress solution, and we can try Guilhermy's idea here.
b
Maybe we can add a feature like showing more properties in my case. For example, I can set like 5 cards, but we suppose that we have like 30 properties to display to users, then whenever the user clicks on "show more" it will display 5 new cards, etc.
I also need to know if this hypothesis is possible
q
I have not tried that with multiple sheets, but I've seen that you can change the sheet name in the same scenario and also build some conditions which sheets to choose, so I believe it's possible. Next time when I build something with Make.com, I have to try that also 💡
with 2-3 sheets in same scenario
b
I tried that but it occurs errors
q
Good to know! Make.com is on my next weeks todo-list
I you tell your exact idea for that, I can try to build the same
b
Cards 1 and 2 are well displayed, but there's a problem with the 3rd card
q
Do you want to get multiple Google Sheets and store those on different Botpress KB tables
for me, this works but for some reason that also didn't display the third card properly at first
b
Exactly because I have multiple sheet pages in my google sheets file for each category of properties, for example one sheet for apartments, another for villas, etc.
What's this file?
q
Here's a new file, now it works so that it displays all the images, if you have in KB table 5 images, it displays 5, if you have 10 images in KB table, it shows 10 etc.
You can download that chatbot file, then create a new blank chatbot, and export that file
so you can try that test chatbot
or just add one Execute code card after Find Records, with this code
Copy code
js
const createCardForProduct = (product, index) => ({
  type: 'card',
  title: {
    dynamicValue: `${product.Title}`,
    valueType: 'dynamic'
  },
  imageUrl: {
    dynamicValue: `${product.Image}`,
    valueType: 'dynamic'
  },
  actions: [
    {
      action: 'url',
      label: {
        dynamicValue: 'Open',
        valueType: 'dynamic'
      },
      value: {
        dynamicValue: `${product.Image}`,
        valueType: 'dynamic'
      }
    }
  ]
});

const carouselItems = workflow.allRecords.map((product, index) => createCardForProduct(product, index));

bp.dm.replyToEvent(event, {
  type: 'carousel',
  items: carouselItems
});
like this
b
That's even better, I'm so grateful 🙏
q
I updated the file a little bit here
carousel-images-2024-jan-212
Try if that works for you. And also when users click the image, it opens the image link
b
So I have to delete the carousels card and keep only this code below find records?
q
we should try with different KB rows to make sure it works
Yes, try that method
normally I don't delete anything, I copy those to some extra Node which is not connected to anything
just if I have to go back, I don't need to build those again
b
q
In my KB table, I have column Image (url) and Title (image name)
change those to match your column names
these match for my KB table {product.Title} {product.Image}
also I have this variable where I stored the Find Records result workflow.allRecords change it to match your variable name
b
Ah ok that's why, I didn't pay attention
q
and I was also too happy to try this your idea, that I forgot to mention it 💎 😂
b
For the title I have "propertyName", for subtitle "price", and for image is "image"
q
then try these {product.propertyName} {product.image}
b
and for price?
q
those I have not added there yet, only name and url
If we can make those work, then I'll try to add other details
b
q
row 23 also
b
I'm a bit lost, can you edit the code please to match my KB table?
q
I think price should work like you did it, in subtitle
yes
b
But when I edited it, it didn't show it in green
q
double-check that Find Records result is stored in a variable, it seems to be disconnecting that after changes
I changed the column names to match yours, and it didn't work, then I noticed that Find Records Result Variable was removed
Copy code
js
const createCardForProduct = (product, index) => ({
  type: 'card',
  title: {
    dynamicValue: `${product.propertyName}`,
    valueType: 'dynamic'
  },
    subtitle: {
    dynamicValue: `${product.price}`,
    valueType: 'dynamic'
  },
  imageUrl: {
    dynamicValue: `${product.image}`,
    valueType: 'dynamic'
  },
  actions: [
    {
      action: 'url',
      label: {
        dynamicValue: 'Open',
        valueType: 'dynamic'
      },
      value: {
        dynamicValue: `${product.image}`,
        valueType: 'dynamic'
      }
    }
  ]
});

const carouselItems = workflow.Results.map((product, index) => createCardForProduct(product, index));

bp.dm.replyToEvent(event, {
  type: 'carousel',
  items: carouselItems
});
try this code
I added a fourth KB table row with the same details, and the Carousel automatically started to show the new images also.
b
It didn't work unfortunately :/
q
can you copy the Execute code card here
using these KB table columns
is displays the info like this in Carousel
b
I found why it didn't work: const carouselItems = workflow.Results.map((product, index) => createCardForProduct(product, index))
q
and when I keep adding new KB table rows, it automatically displays more images and info
b
I changed Results.map to allRecords, and I know it worked perfectly
q
and I changed my code from workflow.allRecords.map to workflow.Results.map to match yours 🛠️ 😂
b
I start to become a programmer even though I have never learned to code 🤣
I changed it earlier to match yours
q
That looks beautiful! 🔥 🔥 🔥 🔥 🔥
b
So now if I want to capture the user's choice in the carousel, what can I do?
Instead of clicking on open, I want him to click on a button something like "I'm interested in this property", then I will redirect him to this node that I created earlier to collect his data
q
I think at the moment it's only possible to capture users choice if they type it, or click Single/Multiple Choice button. With Carousel click it only opens the image URL There's a feature request for that https://discord.com/channels/1108396290624213082/1138100213731557376
exactly what you need also
is on that feature request
b
But if I want to capture just the title "propertyName" to post it later on another sheet that I created to capture clients data, there is this possibility?
Of course, after that the user clicks on the open button to determine which property is interested in
q
Exactly that I have seen bot builders request many times with Carousel cards, since it's has not been working yet to capture anything from images other than open the URL. Maybe Botpress team has some solution to that 💡
for now, the solution has been to browse through the images, then type the property name which you select
then the Raw Input card can capture that
b
So which solution do you suggest to figure out this issue?
q
to show your client, I would add a raw input card with a question which property the user selects or single choice, it there is more than 5 options, it shows a dropdown list but I'm not expert in building great user experienses or complex flows, since I have never done those
b
Can you show me a little example, then I will develop it by myself to adapt it to my complex flow
q
yes
b
You can't imagine how complex is my bot, it's even more than a bot it's a whole No-code AI system but I lack experience in technical issues that I face sometimes to apply my vision and ideas
q
You can customize the dropdown to look nice, but this is just a one idea
when the user selects from there, then you can capture all the necessary info
I added a second Execute code card
Copy code
js
const allRecords = workflow.allRecords

const dropdownList = allRecords.map(item => ({
  label: item["propertyName"],
  value: item["propertyName"]
}));

workflow.dropdownList = dropdownList
and a new workflow variable called dropdownList
dropdownList Type: Array Array type: Options
then I added a Single Choice card used the new variable in Items: {{workflow.dropdownList}} and created a new variable called workflow.userChoice to store the selected values there
b
That's a brilliant idea!
f
Devmik is THE man!!🤯 🛠️ 🦸‍♂️ 🦸‍♂️
q
@bulky-morning-29475 "You can't imagine how complex is my bot, it's even more than a bot it's a whole No-code AI system but I lack experience in technical issues that I face sometimes to apply my vision and ideas" That's why we work here together as a team 🔥 🛠️ 💎
b
I'm speechless now, I don't know what to say to thank you for your efforts, I hope also that you learned some useful things from me even if I can't bring any technical added value, but I have some good ideas that I want to apply in this world of AI automation and solutions that I have just discovered recently and I liked it even if I'm more a business management and marketing man
I started to like coding, even if I don't have time or energy to learn and go deep in this field, but it's gonna be a good idea if I learn some basics of coding and programming language like Python like that I can deal better when I face complex situations when no-code methods are not enough
q
This was an excellent idea from you, a lot was learned and I think all bot builders should read this thread and apply these ideas 💎
b
Yes I hope that will be useful for the whole community
If I have more questions later I'm gonna DM you
q
🦸‍♂️ 🛠️ 🫡
f
Instead of sending him a DM, can you create a help post🙏 I think it would be great if other people could watch the process and learn from it, just like this amazing thread💎
b
Yeah sure, I will use DMs only in case if I want to talk about some confidential items that I don't want to share publicly
f
Thanks a lot and I get that
q
@fresh-fireman-491 Now both Carousel and Dropdown (Single Choice) are automatically updated when a client adds, deletes or updates data in Google Sheets. It is a good idea also to find out what is the maximum number of items you can display in Carousel and Dropdown.
b
I tagged you in another thread regarding connecting the bot to WhatsApp
@quick-musician-29561 I need to connect my chabot to Calendly, there's an example in this video but he didn't show the code that he used:

https://www.youtube.com/watch?v=NOzASEDegKI▾

f
I don't think you need any code for that
Just your Calendly link
And use it in the text card
b
It will pop up automatically if I put my Calendly link inside the text card?
f
When you press the link you will go to the Calendly page
b
I know, but I prefer to setup a pop-up, like that the user can use Calendly inside the chatbot without being redirected to an external link
f
I am creating a tutorial for it now
b
When it's gonna be ready?
f
I had to figure out how to do it before I could create tutorial for it. The video is uploading to Youtube

https://youtu.be/B9EGSZGriJc▾

Still uploading in HD
b
I'm facing this issue now in Make, it was working well yesterday but I don't understand what's happening now
In botpress everything looks fine, but on Make I don't understand why
It's fine I found the solution
I didn't understand this tutorial well. Also, I'm wondering if a Calendly pop-up will work on WhatsApp
f
It will not
That tutorial is for websites only as it displays it on the website as a widget. I am finding out if making an integration is worth it and if it can be displayed on SoMe integrations then.
b
Yeah that's what I expected, so I will go for a normal card to invite people to click on the Calendly link below
f
You should do that on WhatsApp yes.
b
If I want to delete the button 'Open' from this code, to show only the property name, price and image in the carousel. How can I do?
n
Hey folks, Do you know if something like this is easily possible with botpress? I have product catalog and would like to display several images in a response. How easy is it to make?
f
Hey there Creating your own help would help us help you more but before you do that have you tried the carousel card?
j
hi, how can i add a description to the dynamic carousel
sorry i dont know code/
i want the description of the produto be under the price
and a button with a array letting the user click on it to go the page.
@quick-musician-29561
sorry for the interuption
q
I don't know why the latest chatbot file is not here, only the code. Let me check this
j
Alright thanks
q
now I know, it still has this same error when trying to import
@jolly-policeman-82775 I tried all your instructions earlier, but with this file nothing works
j
Hmm
Thats wierd.
Import the bot close all botpress tabs
Nd go back inside
q
Yes, I tried those, but I can try again
also I can build the bot again
j
hmm alright
q
I'm guessing that you can build something SUPER USEFUL with this 🦸‍♂️ 🛠️ 🫡
j
Yes of course lol
I'm creating a very advanced chatbot
this is the KB of the chatbot
dude, my bot is only code
im good cuase Decay and Devmkik save my butt
@quick-musician-29561
what about the code without a button ?
can i make these options dynamic ?
i have differant cateogries in dogs and catts.
is there a way to make them dynamic ?
f
A dynamic single choice?
j
yes
becuase i think i save the categories in a table and display them as dynamic choices
so if they click on the product "cat" then it will show can categories instead of dogs
and if they click on "dogs" then it will only display the dog categories
q
Looks really good! 🔥
did you already solve this, or is this the one giving you an error?
j
no, i have not solved it yet.
but its an extra feature im going to charge for.
im going to sell this bot for 1K$
annd to rent for 300$ per month
q
Did you try the code above in this post, which creates a dynamic Single Choice options (or a dropdown list if there are many items) I added a second Execute code card
Copy code
js
const allRecords = workflow.allRecords

const dropdownList = allRecords.map(item => ({
  label: item["propertyName"],
  value: item["propertyName"]
}));

workflow.dropdownList = dropdownList
I can only try to help with these small coding related parts. Your chatbot already looks so advanced that my head would explode if I try to build something like that myself 💎 🤯
j
no, just prompts, quary KB expressions stuff like that.
ill try that
q
I can also try this, then you can check if it works and modify it to be better.
j
alright thanks man
Your the best
oh,
can you try something for me ?
q
yes
j
already did this
q
try dogs 🐕‍🦺
cats-and-dogs-2024-feb-18
j
k thx
no i mean.
i have cteagoreis.
if they click on dog, then they go to a single choice with dog cateogires
when they click on a categry then it dispalys the category prodct
in a dynamic carousel
q
yes
j
oh, alight
alright8*
alright*
q
in that example, users have the ability to browse through all the dog images and their details, or cat images and details if they've chosen cats. They also have Single Choice options to select the one that interests them.
j
okkkk
thanks
ill try it later
!!
thank you
q
I'm sure we can further develop and improve this feature 🫡
together
j
yess
idk CSS lol
q
I wanted to build and send some quick chatbot file for that part, so we can better understand the bigger project 🐈‍⬛ 🐕‍🦺
j
okkkk
q
if you share the bot or build a simpler example of that which you can share, I can try that if my code is not working
j
lol
Can we have dynamic carousel without a button ?
e
yes, just send an empty actions array
p
What is an empty actions array? 🙂
e
Take a look
So it would be actions: []
p
Thanks!
15 Views
PreviousNext
Powered by