How to create a Telegram Bot that interacts with a spreadsheet (Part 1 of 3)

hi everyone today we're gonna attempt to create a telegram bot that talks to a spreadsheet the final product would be something that looks like this where if you talk to it it will send you a multiple buttons that you can choose from and when you press one of the buttons it will give you some message or some data that corresponds to that button but before we get there I will try to slow down and work our way up to that part of a tutorial what I'm gonna do is like divide this whole tutorial into three parts we will start with first just creating a bot that replies to us when we send out a message something like this so I say test and the bot says your message has been received the second part of this tutorial would be one word when I say say this to keep not only will it reply to us but I would like this message to be saved in a spreadsheet and then finally we will be making like the third part of the tutorial would be this we will be making the inline keyboard buttons so yeah without further ado let's get okay guys so before writing code I want us to go over the API the telegram bot API documentation first to see exactly what we need to create a bot so it says here that we need a unique authentication token first and it comes with new BOTS that are being created I'm going to show you later how to get that and it also says here that all queries to the telegram about API it must be served over an HTTP request and need to be presented in this form okay so I'm going to so later you will see that I will store this as a variable so that whenever we need to make a query we can easily call you know call this URL and then we're gonna be talking to Google spreadsheet through web hooks right so I think yeah so set web hook visit web hook method we're going to basically think of our web hook is like your personal so you have a user who's like talking to your bot and think of a web hook as kind of like a courier who you know who runs back to you every time not courrier someone who's like eavesdropping someone's like wiretapping your your wiretapping your user and basically your web hook runs back to you every time he hears something new that the user is sending your bot so that's going to be we will be needing the web hope so that we can talk to to our spreadsheet later but in order to create that web hook we will need to specify a your URL first right and basically our web hook will be sending information from our user to this URL and this URL is gonna be coming from Google spreadsheet right and you know everything that our user sends gets sent to this URL and from there we can you know do whatever you want with information since we we we have it stored you know on Google spreadsheet side right and it's just gonna be a matter of like putting that inside the spreadsheet putting that data putting that message from the user into a spreadsheet so yeah I think that's mainly it and then of course as I said earlier the first part of the tutorial would be about sending messages so we have this method send message method and it requires two parameters basically the chat the idea of the user telegram idea of the user and whatever message we want to reply to the user if he text us or if he sends a message to the bot so yeah so let's get go and alright so to get started let's pull up a spreadsheet where we're gonna start writing the coning and make sure you are logged outside your company email because the restrictions and your company domain will limit you know limit the bot that your you're creating so let's just pull it up from my personal gmail only personal drive so I'm creating an empty spreadsheet here and this broadsheet comes along with a script editor and this is exactly where we're gonna be running the code cool so as I said earlier you know the first thing we need is a token right which let's store it okay the question is where do you get it right so this unique identification cocaine comes from but father so let's hit him up hey and he says that in order to create a new block we just type it up and let's give it to name maybe because we're one in this first tutorial we want we just want to send a message you name it send message okay good now let's just add a button on the end how about feed out underscore or let's say send message one well oh that's odd m-miss let's do a long you know what let's just a spreadsheet like spreadsheet one okay kay huh unfortunately you had to name it spreadsheet too cool so let me now have our token here let's store that awesome and then ask for the API right we need to be querying through a certain URL format okay so I'm just gonna call this diagram perfect now we can query all we want as long as we know what method we want to be using right so I think the next step here is to create a web hook but as I said earlier in order to create a web hook we will need first a specific URL that the web hook is gonna be sending you know sending information to in that web app URL comes from okay it will come from Google spreadsheet itself so we need first deploy the app so that we can get a web app URL and guys we see the drop-down that I'm choosing here alright cuz I don't want you guys to spend a lot of time figuring out what's wrong with your code when all it boils down to is like the drop-down options that you chose when you were deploying route so it needs to be it always needs to be new needs to be from under your email and it needs to be accessible to anyone even anonymous people deploy that perfecto we now have a web app URL alright so now that we have everything here I think it's time to create a web hook so I'm just gonna create all right so I'm gonna cheat on this one and just copy paste for those of you guys who don't know I have a step-by-step process on how to create creative telegram about that talks a spreadsheet and I posted on a medium right last year but recently there's been a lot of people who's been reaching out to me and sending questions about this tutorial and that's the reason why I'm filming a video yeah well why I'm filming a video so I'm gonna cheat and just copy paste whatever is here all right so we call upon the telegram your telegram okay telegram URL right uh-huh and then we're just gonna add some method which is the method that we're gonna add right and then we're just gonna append the method to the URL that we're gonna be sending updates or messages to through a web hook perfect let's see let's see let's run this all right so let's run you know what before we run it so I want to make sure that this is working so I'm just gonna write blogger that log think it's response URL yeah uh I think so okay so I think this should so logger dialog is like console dot log I'm sorry console yeah when you're home yeah console dot log okay so we need first to give the spreadsheet some permission and life is too short right so even if this app isn't verified we're gonna we're not gonna retreat back to safety we're gonna um verify it or we're gonna allow it yellow all right so it's now running the function because I asked it to run earlier let's look at our logs perfect so it says here right let the webhook was set and we're able to pull up the logs because I I inserted this code but there's no need for this perfect so I think the next thing that we need to do is to make sure that we have a function that can run or that can send a message and as I said earlier as per the API by the way okay it there's only two parameters that are needed it's a required sty D of the user and the text that you want to reply to every time every time you receive a message or your bot receives a message in which case here I said I said it as your message has been received so ID text okay so I'm gonna cheat again make it easier because I honestly I'm forgotten about this I wrote this a year ago I had to spend a lot of time rereading this tutorial and underst anyhow I created it I think it's here okay so um okay so I'm it looks like I'm just gonna have to create a alright a new URL telegram URL plus ago I'm old I'm just guys because I'm a 6% so anytime now anyway and message some message I think shot ID thinking I'm pretty sure I need to chat ID operated send message question mark chat underscore ID equals alright plus the ID exactly plus you know that I use her ID plus the text message we want our u-turn whenever they use her a message alright and then I'm just gonna copy paste this cool cool cool cool alright um yeah so now we're ready to write our third and last function and it's gonna be a new post function and you specifically need to name it do post because Google requires it to do so so let's ask her what I wrote here it is required by Google Apps Script documentation page let's see okay so it says here we either need to get to have a do get or a do post function and I am now developer you guys so I think this can be explained better better but from what I understood as per the internet they do i do get function it's different and it to a do post function in the sense to post or a post function or a post request it's much much more secure than a get request a get request typically sends the information over through a URL like it depends all the information through URL whereas a post request would send information through a message for those of you who can explain this better please do so in the comments I just like quickly Google that I quickly googled ah that's what came up it made sense to me but I think there is a much much better way of explaining it but you know I don't want to be get too sidetracked we need to create a new post do post function so basically do post function is what happens so this basically answers the question of okay so what do we want the bot to do or what do you want to do with the letter e here with the information that we receive from the user so I said earlier think about these the web hook as like a career or a wiretapper that likes you know that like eavesdrops and like observes what kind of messages or what messages the user sends to the bot and then when it hears or when a user sends a message it runs back to the URL and sends you know sends the message so basically that message is represented by letter E and E here is like it's not just a message it answers to what time the message was sent who sent the message what's the user ID of the person of the yeah what's a user ID of the message what's the message ID I think there's something like that I know but so yeah so basically do post function or the do post method answers to what do we do when the user sends in a message and that message is gonna be sent over to the telegram about API as the letter e and I will show you guys what the bladder e actually looks like if we look at the data structure so anyway let's just create all right so e by the way is like it's like a jason jason serialized i don't know what's the technical term for like Jason it's like a JSON object okay alright there you go a jason serialized object so we were we will break that that letter either and see what that actually looks like in layman's ice so do post e so let's start with creating a variable called constants or whatever you want to call it the contents mean meaning this is the content of the letter e okay content so this is a jason jason the parse he done jason boxcars i forgot guys let me cheat again Jason Jason epoch that post data okay whatever the data that was supposed to you okay so we do have so to show you this what that looks like let's try to get updates let's try to see what that looks like what what kind of message gets sent to our web hook whenever a user sensing it types up a message in our to our telegram lot I'm kidding this no I think this I said that's for the tiger I'm API but this is the thing that we need okay I just need to add a method name and they believe that method name would be to get updates method okay it says can't use get updates method well webhook is active we need to delete the one cup fern webhook was deleted let's try to see we can get some updates I hope so please okay - result all right so let's okay perfect no I'm gonna send out a message to our bar okay there you go and I'm just gonna copy this to a jsonparser and that's it that's the data structure of that letter e like if you what do you call that like decode it I guess this is what it looks like so for us to get the one that we want here is the user ID which is which comes from message and then from and then ID now you guys know what my user ID is on telegram excuse me all right so let's incorporate that into the code so whenever someone sends in a message I want to get the user ID so cuz like the whole this whole thing here is like the content the variable this variable where is it right so this this variable the contents right and then from the content we just want to get like we don't care about anything except the ID so I'm sorry okay so once we have the ID we're now ready to send a message so they're two parameters first one is the IV and then the text which we can either store as a variable or we can just like put down here at least I want to say confirmed we see done what in the world well in the back we're not all right so I hope this works guys it's not a toy and if what we did here is right then if we send a message we should receive that text let me just look okay hmm it's not working it's probably because we delete it you know what this we deleted a web hook but let's so let's just run the web hook back now we have a working but cool and that's how you you make one on the next part or in the next video I'm gonna go over how to get this ball to send this messages or this messages right but you send how to store that inside a cell in a spreadsheet see

Channel: Mars Escobin

Views: 29,672

Keywords: code, coding, programming, bot, telegram bot, telegram, spreadsheet, inline keyboard

Id: pV1Jt3fjcq8

Length: 25min 36sec (1536 seconds)

Published: Fri Apr 10 2020