Create Calendar Event With Natural Language in Google Sheets
Learn how to create calendar events using natural language in Google Sheets and manage them efficiently with a custom menu. This tutorial covers both creating and deleting events seamlessly.
We're gonna create natural language description of an event, and we're gonna create that event in your Google Calendar from Google Sheets. This is really cool to add to task managers, project managers, any kind of sheet where you are managing multiple things and you want to go over to calendar, and maybe you have this side panel over here and you have a calendar and you want to add to it. You want to not have to find the day and enter it. You just want to simply say, Hey, on Friday at 10:00 PM finish this report, or check this report, or review this report. And you wanna do it multiple times. We're going to create an event with just a little bit of text. We're going to log it here, and then we're also going to be able to delete it. So let's go over to Extension's app script and start scripting this.
This shouldn't take too long. One of the first things I want to do is create a function on open, and what this is going to do is create a little menu inside of our sheet that we can use. So we're just gonna do variable UI equals spreadsheet app that get UI. And we're gonna say UI dot create menu. We're gonna call this calendar menu, add an item, and we're gonna have two items here. Create event and delete event. One of these is gonna go to a function called Create Event, and the other one is going to go to a function called delete event, and let's go build those functions now after we add to ui. There we go. So let's create the function , create event first.
We're gonna need our UI as well in here. Get ui, we're going to need a response from a prompt, so UI prompt, and this is gonna ask us a question and we can fill it in in sort of a text form here. So we're gonna create calendar, even that's just the name of this prompt. But the question we're gonna ask is. Describe your event. I'll give an example here. Dinner with Sarah, or finish report with Sarah Friday at 4:00 PM We need one more thing, which is we want a certain button set. Button set. Okay. Cancel. So we're going be able to describe our event and then either we're like, ah, I don't want to do it. Cancel, or I do wanna do it. Okay. Now if the response
do get selected, button is equal to UI button. Okay. Now if that's true, we're gonna do something but else, meaning if they cancel, we're gonna say UI alert. And this is just another prompt that is just a string and it just tells us event creation canceled. But what do we do if we actually want to put in the information in here? Like we wanna create the event? Let's do that. We will get the description from response dot get response text. The event is going to be calendar app dot get default calendar. Get default calendar, create. Event from description, and then we'll have that description there. So this is pretty much the only line of code we need in order to
actually create this event, but we have to get it from the user. So we're using this UI that says, Hey, just fill in your information here. But ultimately it's this calendar app dot get default calendar, create event from description that we can enter a natural language description. And create a calendar event in our calendar. And now once that's created, we want to get actual information from this, right? We want to get what is the event name, what is the time, what is the day and an id, because ultimately we may want to delete that event later. So let's get some information from the response here. This event that we've created. So we'll get the title, which will just be event get title. We'll get the event time.
Now this is gonna be interesting because we do get event do get start time, but we need to wrap this with utilities dot format date. It's gonna come back as a date. We will get the session time zone and we'll format it. HH colon. Mm a. So what this is doing is taking whatever date format we have, just a string of date information and just grabbing the hours. Just grabbing the minutes, and if it's am or pm. Event day. Now this is again going to be just that event start time, but we need to format it in the word of the day, like Monday, Tuesday, Wednesday, Thursday, Friday. So again, we'll wrap this with utilities format, date session, dot, get the script time zone, whatever time zone we're in.
And the format here is four capital E. This is the entire name of the day of the week and and parentheses there. And ultimately we want the event id, whatever calendar event is created, we want the ID of that. So that's just event dot get. I. With parent. Make sure you have those parentheses there. Now where are we gonna store all of these? We're going to store them over on the events tab. So let's create an events tab, variable events equals spreadsheet , app dot get active spreadsheet , do get sheet by name, events, and now we can just do events dot append row. In an array, we're going to get the event title comma event time, comma event day, comma event id.
So these are the four things we want. We can separate these with a little bit of space here just so it's easier to read in this particular killer format , and we want another. An alert here instead of just say it's canceled. When we do it, we wanna say, alert event created at a plus sign event, die get title. Or we could just bring this variable here. Event title plus at plus event start time. So we'll see the entire start time here, but we could also do, let's just do event time plus on, put another space there on plus day. There. So let's test this all out. Command S to save. We still have to do create the delete event, but I wanna
make sure this is all working. So in order to get this on open function to actually run, we need to open. So we'll have to refresh the sheet and we'll see this calendar menu. And the very first time we create an event, it's going to ask us for authorization . We'll do that. Click okay. Select your account, select all , click continue. And now it's asking us to describe our event. So I'm gonna do exactly this. Finish report with Sarah on Friday at 4:00 PM click okay. And it says, event created. Finish report with Sarah at. And change it to 16:00 PM but that's still 4:00 PM Okay. And here's the event title. Here's the time, here's the day. It is Friday, and here's my event in my calendar. Already done. Pretty cool, right? Let's see. How do we delete this? We have this ID here.
Let's go back to extension's app script . Click okay. And here, function delete event. It's only gonna be two lines of code. We only need variable ID of the event. We're gonna do calendar, app, get default, calendar, get event by. Id put in the ID and delete event. The ID is going to be, now we could do this a number of different ways, but I wanna just get this selection , whatever we're in D two, so whatever the active range is, spreadsheet , app dot, get active. Spreadsheet , get active range , get value, whatever the value is there. And we would get errors if it's, if we're selecting the wrong thing, which we could code in sort of more stuff, right?
But for this particular case, right now, we're just trying to delete the event if we have it selected. So let's go and try that. Let's select D two. Delete event and it did it, we may want to code more things like delete the entire row here of information or archive it in other ways. But there's other videos that show you how to move rows of data or delete rows of data . This one was just to show you how to create an event with natural language and delete that event. If you do wish to, based on the id, 'cause we get the ID over here. This sheet and the app script are available down below. Go and grab them right now if you want the exact app script . You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right
now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.