Dialogflow Tutorials: Integrate Dialogflow Chatbot with Google Spreadsheets - Part 2

[@veeraskitchen]

Hi Anshul ,Great! video tutorial.I need to link Google Sheets which is going to act as a backend to retrieve data from sheets and display in chatbot when an intent is triggered.

[@geovilla821]

Thank you so much. Great help for my class project. Please make a lesson also on how to edit/delete data from spreadsheet using APIs.

[@AxleWebTechnologies]

You can refer to Sheet.Best API documentation for Editing and Deleting data. For details on how to integrate Dialogflow with APIs, please watch my video tutorial on how to integrate Dialogflow with External APIs.

[@digitalist9707]

Thanks for this useful video, IS IT POSSIBLE TO GET THE USERS' ID (Sender ID) TOO? I've used your method and it works fine, BUT I need to have: 1. senders' profile link/Id... 2.I'm getting FB messages. So, knowing the Post/Ad (ID) or anything that shows which Ad/Post they are sending message about is so important for me! I really appreciate it if you reply this message.

[@AxleWebTechnologies]

Sorry not sure about that but we can surely explore about that.

[@leolov2001]

Hi, really good and informative video. However, is there a google spreedsheet API offered by Google that we can use to achieve the same end result? (instead of using 3rd party apis by linking our sheet to them)

[@AxleWebTechnologies]

Yes you can use Google spreadsheet API however, they are more difficult to setup and use. These third party APIs are super easy to use.

[@richardliful]

Thank you so much for this tutorial, but I have a few questions: Do I need to install axios in some way for this to work? I have set the intent up with the spreadsheet linked from sheetbest, but when I try to run it with the hard coded data to test if it is adding onto my spreadsheet it does not work. The fulfillment status says : "Webhook call failed. Error: UNAVAILABLE."

[@AxleWebTechnologies]

Hello Richard, you have to add axios along with version number in the package.json file. You can see this file in the inline code editor. After that, When you deploy, it will install axios. For the webhook call failed error please check firebase console logs.

[@obicimsinema2445]

Hello, first of all thank you! How many parameters can we have in the fulfillment? Also is it possible to create separate intents with parameters and combine them in the same spreadsheet? I've look at Sheetbest but couldn't find the related code.

[@virgilpillay]

This is a great tutorial, but can you please provide me some advice. In the fulfillment it is asking to setup Google cloud billing. I understand that I have to setup the account, but I'm afraid of the costs. I am making a chatbot to collect information from what'sapp users, and that information will be stored in Google sheets. Can you please advise me if this will be free on the cloud platform or if I will have to pay. Please help me 🙏

[@AxleWebTechnologies]

Please contact me on [e-mail]

[@rohitdutkunwar8705]

Nice one...Thanks a lot. Can we store the values of the parameters asked in different intents in the same spreadsheet or we have to make different API's for each intent?

[@AxleWebTechnologies]

Yes you can save the values from other intents in the same spreadsheet. You just need to handle it accordingly in your code. You can refer to sheet.best documentation for details

[@obicimsinema2445]

Axle Web Technologies hello, which script should we use for that in sheetbest? I couldn’t find/understand:/

[@justbhopal28]

Thanks a lot for the tutorial. But after completion of the process screen becomes blank. Please help me to give an acknowledgment after data is saved.

[@enesefe5785]

Hello, first of all thank you so much for this awesome video. And I have a question, I want to change data on excel but I don't this because. Dialogflow send to excel as new data. How can i do this? Thank you

[@AxleWebTechnologies]

For that you need to send an update request. Please have a look at sheetdb.io documentation for how to update Google spreadsheet using APIs

[@AndrewLearned]

I followed your tutorials to integrate Twilio, and now this. Is there a way to pass the Twilio "from" phone number to the sheet without having to ask for it in a prompt? I'm sure there is, but haven't been able to figure it out yet...

[@piotr.widziszewski]

Hey, would You mind answering this two questions? 1. Is it hard to code Dialogflow chatbot who would filter through web pages and send back results to user? For example You are trying to find cheapest hotel or flight and You would have to check several pages and filter by city, $ and hotel ***. Instead You Ask chatbot: please search 4**** hotel in NY under 1000$. And then Bot would respond with links to offers. 2. Would You do some example tutorial in near future?

[@AxleWebTechnologies]

Hello, the best approach to build such chatbot would be to do this in two steps. In first step, you would write a web service which would scrape data from Websites and serve it through an API. You can find many freelancers who would help you with this on fiverr. In second step you would connect this chatbot to the API. You can refer to the video, how to connect Dialogflow with external API for this.

[@piotr.widziszewski]

@@AxleWebTechnologies thank You!!!! Appreciate Your answer and engagement! :)

[@piotr.widziszewski]

Hey, another great tutorial, thank You ! :) Could You help me to solve this problem ? I cannot figure out how to code that fullfilment My case is: - Bot is asking a questions with two possible answers A and B - user is answering with A or B I'd like to build questionnaire counter so if user answer A in Google Spreadsheets "1" appears under "A". If user would answer "A" again then cell value would increase to 2 (1 +1 ). So for eg. after answering 10 times to the question I would have in spreadsheets eg. A B 7 3 Greetings P.S. I found sth like this on stackoverflow, but I don't know how to use that function increment() { SpreadsheetApp.getActiveSheet().getRange('A1').setValue(SpreadsheetApp.getActiveSheet().getRange('A1').getValue() + 1); }

[@AxleWebTechnologies]

Have a look at sheetdb.io documentation. You can use PATCH/PUT request to update the data in the google spreadsheet. docs.sheetdb.io/#patch-put-update

[@piotr.widziszewski]

@@AxleWebTechnologies thank You :) All the best, Peter

[@DanielIslasR]

Hello friend, your video is great. I have complete all your instructions but even though sheetbest does mark me the use of an attempt, the data appears empty in my spreadsheet. If it shows that an anonymous user has modified the document, when going to the history of actions, it shows me in blue color the boxes that are supposed to have been written and sent, these fields appear empty. Any idea or suggestion please?

[@AxleWebTechnologies]

Not really sure whats going on there. However, you can try several things like, add console.log() in your code to see if the parameters are being passed correctly to the code. Also you can try using postman to write data into spreadsheet using sheetbest APIs.

[@harshalsuryawanshi8896]

Hey did you find any solution to this? Please let me know.

[@digitalist9707]

@@harshalsuryawanshi8896 let me know too!!

[@setransstadia4133]

i am getting this error at firebase and data is not getting update at google spreadsheet. dialogflowFirebaseFulfillment Error: No responses defined for platform: DIALOGFLOW_CONSOLE at WebhookClient.send_ (/workspace/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:488:13) at promise.then (/workspace/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:306:38) at process._tickCallback (internal/process/next_tick.js:68:7)

[@setransstadia4133]

agent.end(""); This needs to be inserted at the end of the function and it worked

[@AxleWebTechnologies]

Ok great, this is something new for me as well. Thank you for sharing ~ Anshul

[@mydigitalplan1856]

@@setransstadia4133 Thanks so much, really useful!

[@Машенька-г2ь]

@@setransstadia4133 OOOOMMMMGGGGG! Thank you very much. God bless u!🙇‍♀

[@benysebag]

Excellent tutorial! It works perfectly when testing using the DialogFlow interface. After integrating DialogFlow to Twilio/WhatsApp, I was able to read data from the spreadsheet but I can't write. Any ideas of what might be happening?

[@benysebag]

I am trying to test both functionalities (read and write) from the Mobile Phone. Only reading works.

[@AxleWebTechnologies]

Please have a look at firebase console logs. You might get an idea of what's going wrong.

[@benysebag]

@@AxleWebTechnologies For some weird reason I can expand the logs. Only a small part is visible. Do you know why my firebase logs are not getting expanded? Any suggestions?

[@AxleWebTechnologies]

@@benysebag That seems to be some bug with firebase logging console. You can just double click on a line to copy the whole text and then paste it in a text editor.

[@dhanrajshastri4936]

Hey did everything your way and I have tried multiple different api websites but nothing seems to work when i try axios.post idk what the problem is but itd be really helpful if you could point me in the right direction please

[@AxleWebTechnologies]

Try checking firebase console logs to see if there are any errors. Also make sure you have enabled fulfillment option in the intent. One more thing, you need to make sure billing account is enabled otherwise you won't be able to access external APIs

[@dhanrajshastri4936]

@@AxleWebTechnologies have all of that checked heres the error i keep getting in firebase: Error: Request failed with status code 400 at createError (/srv/node_modules/axios/lib/core/createError.js:16:15) at settle (/srv/node_modules/axios/lib/core/settle.js:17:12) at IncomingMessage.handleStreamEnd (/srv/node_modules/axios/lib/adapters/http.js:236:11) at emitNone (events.js:111:20) at IncomingMessage.emit (events.js:208:7) at endReadableNT (_stream_readable.js:1064:12) at _combinedTickCallback (internal/process/next_tick.js:139:11) at process._tickDomainCallback (internal/process/next_tick.js:219:9)

[@nikolaibeliaev8795]

Thanks a lot for your tutorial! It was very helpful for me.

[@vishalkadam3538]

Bole to mja Agya

[@geovilla821]

Great video lessons, it help me a lot to understand the concept of using the JSON API. Please let me know how can I add a NEW LINE BREAK in my agent.add(` text here and line break text here `). I've been trying to use but it does not work. Thanks

[@AxleWebTechnologies]

Which platform are you building the chatbot on? Facebook, slack, web?

[@geovilla821]

@@AxleWebTechnologies I'm using for WEB

[@AxleWebTechnologies]

@@geovilla821 Are you using Dialogflow web interface or any third party product like Kommunicate or BotCopy or you created your own web interface?

[@vishalkadam3538]

Good

[@orionmarineconcepts]

Great video. Have been unsuccessful to write or read/match data through fulfillment if there are Spaces, Characters or numbers on the user-input value. For example; tried to match the Name; Robert Junior, Parameter value shows only; "Junior", neglecting the first word. Similarly, for Junior2, returned only "2" and incase the user types in Lower case then it doesn't match with sheet data. Any help would be appreciated. Thanks a lot.

[@AxleWebTechnologies]

There is an option called parameter.original. you can use that to get actual user input.

[@adejumoazeez4447]

Pls its doesn't seam to work for me, can u pls post the like to the index.js code please. I really appreciate. Thanks lovely video.

[@vishalkadam3538]

Sadiyo se eski ho talash thi muje 😍

[@shreyasjani2878]

I want to write parameters from multiple intents to a single row in a spreadsheet but its writing the parameters of each intent in a different row, is there a workaround for this?

[@AxleWebTechnologies]

Yes, for that please check out the spreadsheet API documentation for how to update a single row. Basically instead of making post request, you have to make a put or patch request.

[@chaitanyapaladugu751]

Hi Anshul, would you please create a tutorial for this. I have hard time placing the data from multiple intents into single row. Thank you.

[@dayoabegunde617]

@Axle Web Technologies. Thanks for this tutorial but after following through, User entities were not been recorded on the googlesheet after removing the hand coded entries and try to use the entries from users. But whenever i switch back to the hand coded entries it works again. Please can you help out

[@AxleWebTechnologies]

Looks like something is wrong in your code. Please join our slack channel so I can help you better Please join the slack channel using below link and post your queries in the channel: join.slack.com/t/dialogflow-awt/shared_invite/enQtOTUwMzAwOTI3Mjk2LWRiOGYwOGIyOGU4ZjNkNWI3NTJiMTgyMmMyNTM2OWE2YzZmZjkwNmEyMWZmOWIyZTRmNzY3MTY2ZDYzNWVhYzQ

[@dj_chopa]

Hello. I'm having a problem with my action and parameter prompts at 02:47. They are repetitive and don't record the values I input. What could be the problem and how can i solve it? Please help.

[@devaryanshrivastava5614]

Thanks for the amazing video, part 1 video was implemented successfully. When I did part 2 that is this video I am getting an error. i did the same step by step as yours. Error: No responses defined for the platform: DIALOGFLOW_CONSOLE at WebhookClient.send_ (/workspace/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:488:13) at promise.then (/workspace/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:306:38) at process._tickCallback (internal/process/next_tick.js:68:7)" can you please help me to solve this? Thanks in advance

[@Машенька-г2ь]

Oh, i have the same;(

[@anthonymayorcab]

Excelente clase, ¿puedes colocarle subtitulos en español para entender mejor?

[@AxleWebTechnologies]

You can do it on your own. Just follow the below steps: Go to the video, click on CC, click on gear/settings icon Select subtitles/cc option Click on Auto Translate Select Spanish And there you go, you will have video with Spanish subtitles :)

[@chewjinkoh2543]

hi, thanks for your video. it is wonderful, appreciate if you could advise why i am getting this error after the POST. "Error: No responses defined for platform: DIALOGFLOW_CONSOLE at WebhookClient.send_ (/workspace/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:488:13) at promise.then (/workspace/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:306:38) at process._tickCallback (internal/process/next_tick.js:68:7)"

[@AxleWebTechnologies]

Make sure you have added agent.add() code. If you still see the issue, please drop me an email at [e-mail] I need to have a look at your code to understand the issue

[@markusnyblom4491]

@@AxleWebTechnologies Where should I add this code, at script row 87? Also, a more recent post in this thread was posted by @SE TransStadia and he claims that one should write agent.end(""); , although I'm not sure whether one should write that in addition to your agent.add() or instead of it. Thank you for your time! Best regards

[@anishbabu6649]

I followed your tutorial exactly, but the information is not writing to the google sheets. There are no errors or anything. Do you know why this is the case?

[@AxleWebTechnologies]

Make sure you have enabled fulfillment option in the intent

[@anishbabu6649]

@@AxleWebTechnologies I tried that and when I test it, there are no errors. However, the google sheet and the API do not update. I tried refreshing the page for both, but still no luck. Any idea why this may be the case?

[@b.ssadiya4653]

@@anishbabu6649 same did u find any solution regarding this

[@gorn5413]

Hi and Thanks for this Tutorial! But... I can't add axios because it show me error - 'axos' is not defined. When I added "import axios from 'axios';" I got red description bottom form: The deployment of your Cloud Function failed: Function failed on loading user code. Error message: Code in file index.js can't be loaded. Is there a syntax error in your code? Detailed stack trace: /srv/index.js:4 import axios from 'axios'; ^^^^^^ SyntaxError: Unexpected token import I don't know what to do with it. How add axios to Fullfilment?

[@AxleWebTechnologies]

1. Make sure you have included axios in package.json file. 2. Make sure you spell it correctly. In the above message, you have written "axos", make sure it is axios and not axos. 3. Instead of using import, try below format const axios = require('axios');

[@gorn5413]

@@AxleWebTechnologies Thank you! I added- const axios = require('axios'); after 'use strict'; and it works!

[@gorn5413]

@@AxleWebTechnologies may be you show to us how to generate and showing to users discount codes in Dialogflow (use axios.get) generated Google Spreadsheet?

[@gorn5413]

@@AxleWebTechnologies Hi, Friend. After some days experiments I see that my Chatbot dont writing nothing to my Google Spreadsheet. I tried add some text in "const data" but nothing. I tested "Try it now" function (it showing values) but nothing also. As I understand that it is a problem "axios.post". As I wrote ths function shown me errors but when I added "const axios = require('axios');" after 'use strict' of index.js then errors not was showing more... and as I understand it not working right now. What to do? I need your help...

[@AxleWebTechnologies]

@@gorn5413 Check console logs if there are any errors.

[@mahammasroor6153]

How to store data of fallback intent into the spreadsheet?

[@athenasaurav]

Hello first of all kudos to your channel and videos. i checked all the integrations and i found out that the data is not written using twillio (text messaging) integration. can you enlighten us how to deal with it. with all the other platform it worked perfectly. i m trying to automated my whatsapp using twillio.

[@AxleWebTechnologies]

Sorry, cannot tell without seeing the code. Please show some code, maybe post a query on stackoverflow and paste the link here.

[@athenasaurav]

@@AxleWebTechnologies i used the exact same code to see if my bot had any problem(expect for the api), billing is als included. Twilio is generating a 11200 error response whenever msg is coming from whatsapp and it try to save data to google sheets

[@RandomGuy-hi2jm]

how to do the same thing with webhook

[@justbhopal28]

One more query , How to insert the whatsapp number from which i am getting response?

[@tonyjeyifous9586]

THANK YOU!!!!!!!!!!!

[@victorbrylew1775]

I'm really wondering how much will it cost to me to use bots created based on Dialogflow with Google Assistant? Does anyone have experience in regular using of same bots?

[@AxleWebTechnologies]

Please drop us an email at [e-mail], we will let you know the cost, timeline and other details.

[@juliansalazar9591]

Thanks a lot! Actually I do have some error code from the console: Error: Request failed with status code 400 at createError (/srv/node_modules/axios/lib/core/createError.js:16:15) at settle (/srv/node_modules/axios/lib/core/settle.js:17:12) at IncomingMessage.handleStreamEnd (/srv/node_modules/axios/lib/adapters/http.js:236:11) at emitNone (events.js:111:20) at IncomingMessage.emit (events.js:208:7) at endReadableNT (_stream_readable.js:1064:12) at _combinedTickCallback (internal/process/next_tick.js:139:11) at process._tickDomainCallback (internal/process/next_tick.js:219:9) What could be the reason? Cheers

[@AxleWebTechnologies]

It is not very clear from the stacktrace you provided. 400 error means bad request. If you are making POST request, make sure you are sending correct parameters in the body. Try checking firebase console logs.

[@VivekKumar_]

I used two different sheets one for writing data and one for reading data. But it's not working

[@AxleWebTechnologies]

Are you getting any errors. Can you please share console logs?

[@Anonymous-bc7mw]

Im trying to update my contents in spreadsheet but im unable to do it using patch. Any idea how should i proceed

[@AxleWebTechnologies]

When you say you are unable to do it, what is the problem you are seeing? All you need to do is make Patch request from your Dialogflow webhook code. Check in the console logs if you see any error.

[@ktaKnot]

I follow your video but it doesn't save the data to my spreedsheet. Every thing seem fine ex.enable webhook in fullfillment , everything in Inline Editor is ok no error detected.

[@AxleWebTechnologies]

Please check your firebase console logs

[@ktaKnot]

@@AxleWebTechnologies Is it possible that I use the free firebase (spark plan)?

[@ktaKnot]

@@AxleWebTechnologies Now I trying to use firebase way (follow your vedio kzbin.info/www/bejne/eWnMkn6Fn96kpK8) and it'work but I want add new name not overwrite it how can i do? .

[@AxleWebTechnologies]

Please see the pinned comment for that.

[@sathishgendi5946]

Getting following error Error: No responses defined for platform: null at V2Agent.sendResponses_ (/srv/node_modules/dialogflow-fulfillment/src/v2-agent.js:243:13) at WebhookClient.send_ (/srv/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:505:17) at promise.then (/srv/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:316:38) at at process._tickDomainCallback (internal/process/next_tick.js:229:7)

[@AxleWebTechnologies]

please try adding following: agent.add('Hello from Dialogflow webhook');

[@santoshjv]

i have write all code as per given instruction but spreadsheet is not reflected(No data is insert)

[@AxleWebTechnologies]

Please check couple of things, 1. Have you enabled fulfillment in the intent? 2. Have you enabled billing account? 3. Are you getting any errors in firebase console logs?

[@santoshjv]

@@AxleWebTechnologies there is error in firebase

[@AxleWebTechnologies]

@@santoshjv That could be the reason. Fixing that error might resolve the issue. Feel free to comment back if you need any help.

[@MacarthurmediaAu]

I followed your video but my axios line says that axios is not defined. How can i fix this? Thanks

[@AxleWebTechnologies]

You need to include axios in package.json. Please watch my video on how to connect Dialogflow with external APIs kzbin.info/www/bejne/pGWsgYKbe6h3rqs

[@MacarthurmediaAu]

@@AxleWebTechnologies Thanks. That helped. But I now get an error that says "Warning, estimating Firebase Config based on GCLOUD_Project. Initializing firebase-admin may fail." Do you know how to fix this?

[@AxleWebTechnologies]

@@MacarthurmediaAu You can ignore that error, but please make sure to enable billing account, otherwise external network will not be accessible. You can watch my video on how to connect DF with External API to learn how to enable billing account.

[@MacarthurmediaAu]

@@AxleWebTechnologies Thanks. I ignorerd it and I now have an "unhandled rejection" error and the following log " Error: No responses defined for platform: null at V2Agent.sendResponses_ (/srv/node_modules/dialogflow-fulfillment/src/v2-agent.js:243:13) at WebhookClient.send_ (/srv/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:505:17) at promise.then (/srv/node_modules/dialogflow-fulfillment/src/dialogflow-fulfillment.js:316:38) at at process._tickDomainCallback (internal/process/next_tick.js:229:7)" Any idea Axel Web?

[@MacarthurmediaAu]

@@AxleWebTechnologies any idea on how to fix. I really dont understand this error. Thanks.

[@setransstadia4133]

agent.end(""); This needs to be inserted at the end of the function Try it // intentMap.set('your intent name here', yourFunctionHandler); // intentMap.set('your intent name here', googleAssistantHandler); agent.handleRequest(intentMap); agent.end(""); });

[@markusnyblom4491]

It's weird, I tried this but it just ended up making my script unable to request the information about people (as instructed by the 1st video) and it still does not post data in the sheet.... :( I'm no programmer though so I don't really understand the why

[@markusnyblom4491]

@SE TransStadia My console log (when running with your agent.end(""); at script row 87): textPayload: "Function execution took 24 ms, finished with status code: 200"

[@jefersoncoelho6019]

Hi friend, I tryed on my aplication but says "Error: getaddrinfo EAI_AGAIN sheet.best:443". Can you help me ?

[@AxleWebTechnologies]

You need to enable Billing account for accessing external APIs. Please watch my video on integrating Dialogflow with external APIs for more details.

[@jefersoncoelho6019]

@@AxleWebTechnologies Thanks

[@obicimsinema2445]

Hello, I was able to connect my separate intents to the same spreadsheet. However, answers are not in the same row. How can I align them in the spreadsheet without blanks etc.? please see: prnt.sc/ra863v

[@AxleWebTechnologies]

You need to use the patch function. Please refer to Sheetdb.io documentation.

[@obicimsinema2445]

@@AxleWebTechnologies but how :( it doesn't work. can you be more specific?

[@AxleWebTechnologies]

Please send an email to [e-mail]

[@connector-ketnoikinhdoanh2179]

@@obicimsinema2445 did you receive any response from AxleWeb and found the solution for your problem? I run into same problem and I want all data from different intents should be in 1 sheet. Looking forward to your reply.

[@vishalkadam3538]

Mamu

[@rbmeocean3161]

Hi I am getting this error: Warning, FIREBASE_CONFIG environment variable is missing. Initializing firebase-admin will fail how to resolve this, all steps are done as it is. cannot push data, billing account is enabled. Please help.

[@AxleWebTechnologies]

please send an email to [e-mail] with error logs and screenshot of errors you are facing. Someone from our team will help you.

[@rareramsi]

Hi, I am getting the error for axios not defined in code. I have used the same code. Its getting deployed successfully. However warming is showing for the command axios.post. Post execution also the google sheet is not getting updated. Please assist

[@AxleWebTechnologies]

Make sure you have added axios in the package.json file. Please watch the video for more details.

[@RendaniSinyage]

he never spoke about it but its const axios = require('axios'); needs to be in line number 8