Getting Data from Google Sheet
# 🤝help
t
Hey, I want to get some data from a google sheet. I currently use Zapier to search for a specific order number which the user inputs into the bot in a google sheet. How can I pass back the data from the whole row with the order number in the sheet (name, date etc.) to botpress? I would appreciate some help 🙂
r
Hey @thankful-processor-79226, Have you already connected Botpress to the Zap you created?
t
Current workflow is: Botpress bot stores the confirmation code in the variabel "code" this variable "code" is being passed to Zapier successfully. Then Zapier searches my google sheet for the row with the code and extracts the other data like date, name etc. I need to pass this information to the bot now.
r
Okay! You can create another workflow variable, say
row
of type Object, and in the execute code assign the response to it
workflow.row = response
You will then be able to access all the information using
@workflow.row.XYZ

https://cdn.discordapp.com/attachments/1133465098468470834/1133469753583861921/image.png

t
Let me try to understand this and I will give you feedback, ty for the fast response.
But how do I get the data from Zapier? Still a bit confused.
This is my zapier code:

https://cdn.discordapp.com/attachments/1133465098468470834/1133483885402017862/image.png

https://cdn.discordapp.com/attachments/1133465098468470834/1133484104084635668/image.png

@red-rocket-93714
r
Oh, I see Zapier webhooks don't seem to return a result. Will have to combine that with a Trigger I believe. I need to dig around; I'll get back to you.
t
Thank you!
@red-rocket-93714 any update? Would appreciate it.
r
Hi! I'm back. Took me a bit, but figured it out 😅 Okay, so the Zapier Webhook simply replies back with an http 200 code to acknowledge the reception of your request and closes the connection. So, it's an asynchronous call. That means, we need to get your Zap to make a request back to Botpress as the last step. So. This only works in a published bot (does not work in the emulator). Each step in a screenshot. Step 1. Be sure to pass in the
event.conversationId
when you call the Zapier Webhook for Zapier to pass it back to Botpress later so that Botpress may link the webhook request to the right user conversation. Step 2. Enable the Botpress Webhook integration on your chatbot. Step 3. Setup the POST Webhook action in your Zap that uses the Botpress Webhook URL and pass back the conversationId as well as all the other properties you want Botpress to receive. Step 4. In your bot, create a Webhook Trigger (You need access to the Triggers Beta), and link that to an Execute Code card. In the Execute Code card assign the conversationId from the webhook to the event conversationId. This ensures the webhook continues the user conversation. Step 5. Assign other webhook response properties to your workflow variables for use in your user conversation.

https://cdn.discordapp.com/attachments/1133465098468470834/1133845817883639838/image.png

https://cdn.discordapp.com/attachments/1133465098468470834/1133845818147868753/image.png

https://cdn.discordapp.com/attachments/1133465098468470834/1133845818378571843/image.png

https://cdn.discordapp.com/attachments/1133465098468470834/1133845818621825204/image.png

t
Thank you Ill try this tonight and let you know.
@red-rocket-93714 How can I get access to the triggers beta?
r
t
Filled out!
Please let this thread open until I got access and can try it.
@red-rocket-93714 I filled out the form about 5 days ago and @early-train-33247 posted that he granted access to all who filled out the form but I still cant add a trigger node..I dont see the option. It's crucial cuz I have to finish work for some client.
e
Hey mate, you probably entered a wrong email address. I shall be adding a new batch today!
Please fill out the form again
t
@early-train-33247 filled out again
b
Hi there! I’m also interested in joining. I have already filled out the form
b
me too, i've filled the form
a
Hey there, I'm interested in exploring this feature as a way to import a Google spreadsheet that is constantly updated using web hooks from Zap as a knowledge base object for the bot to use. Is that possible?
@early-train-33247
e
We don't have dynamic knowledge bases yet, but you can easily integrate with Zapier! Check out this tutorial by a community member: https://discord.com/channels/1108396290624213082/1136369863317786745
a
This is great! Thanks so much for the reply. However, I'm actually hoping to do that the other way around. For example, if we had a constantly updated spreadsheet that listed clock in and clock out times for employees, could the bot utilize the data from that spreadsheet to allow a project manager to inquire about specific employees in a natural language set up?
n
This is something im interested in as well!
e
Hello folks, sorry for the late reply. We have two upcoming updates that will help you: 1. Being able to upload CSVs to use as KB sources. @average-oyster-99594, it will still require a bit of work to upload it again everytime there is an update. 2. Tables will be available as KB sources, so there would be no need to use external spreadsheets!
In the meantime, you could save the sheets as a PDF and upload it, then Botpress will extract the structured information to provide answers, please try that out!
q
@early-train-33247 I hope you are fast with tables as kb source update, my real estate client is waiting for it, long time now -.-
e
Coming soon! KBs are a relatively new feature, and Tables are a very new feature, but we are working on it 😉
172 Views