Create Calendar Event from Google Sheets with Google Apps Script

[Music] learn how to automatically create google calendar events based on google sheets data jane is the person in charge of the planning of the community management so let's say here at sapers we have a lot of different social media channels and different people are in charge at different times or responsible to answering to the questions um the comments and so on and so forth and we see there there's a little plan here and what we want to do is in the demo uh where is it yeah it's just choose the demo team vacation that's okay we want it to pop up there we want the plan to pop up over there okay so how do we proceed first up we start the ide tools script editor that's going to load the script editor and first things first we're going to name our project um i don't know let's call it automate calendar there you go go ahead and rename that and then we're not going to leave it on the or leave this name here my function instead we're going to rename this to create calendar events there you go the rest we're going to leave like so now we need to access our calendar how do we do so so let's head over here and over to settings and if we go down to where is it demo team vacation as i said well we will just be using that what we're interested in is this here the calendar id go ahead select that copy it because we will be needing that id that code and we're going to save it under community calendar and we need to access the calendar app service there you go so i'm going ahead and choose this and then dot get calendar by well guess what by id because that's what we just copied and we need to pass that in like so and let's close that out very good so now we have our calendar we have access to the calendar now we need access to our sheet and store that in a variable called sheet under the spreadsheet app service if you don't know what calendar app and spreadsheet app is these are services that we can use in google apps script i have a video on that that i will be linking right about now so have a look at that if you want to find out what this is all about and how you can use and interact it okay get uh this i'm gonna use get active sheet so that's the current sheet i'm on which one is that well that's this one this is the sheet so also this is something to keep in mind this is a spreadsheet the whole document is a spreadsheet and this is one specific sheet and we will be getting the active sheet so there where our cursor is on and then we're going to do the following we're going to say get the schedule [Music] fun word depending on where you're from you say schedule or schedule let me know what you how do you pronounce it in the comment section below so sheet oops my mistake sheet dot and then we're going to get the data range so that's this method here that we can use that goes oh wrong that goes and gets um the whole range that contains data so that is to make sure that i'm getting everything so especially if i do not know how many rows will i have how many columns it's okay just use get data range and it will get all of it and then we want to get out the values we want to read the values not value but instead values since multiple columns and rows and then we're going to do the following schedule or schedule splice zero and two what does that do well in my videos i mentioned time and time again that apps script is essentially javascript and splice is one of the methods that we we can use in javascript so if you're interested in finding out more about it just go ahead and google javascript splice method or just javascript splice that should be good enough too so what we're doing here just to give you a short intro or let you know why i'm using this i'm getting all of the data here which is awesome that's my data range here great but if i'm wanting to create calendar events based on the data here i have no use for this for the the big header and the subheader here the the header of the of the columns what i'm looking for is to get the data here so that's why i'm using splice to say in the zeroth position of the array um the zeroth position of the array which will be in the array you will have each row as a position get rid of position one and two i don't need those and keep the following so keep the following rows that's what we're doing with splice we're just getting all of the data and just saying okay so i know the two first ones are headers they're not useful for creating any events do away with them that's what i'm using the splice method for the splice method also takes other parameters so i said if you want to find out a bit more in depth on how to use this maybe in your special situation or specific situation then go ahead and google javascript splice learn how to automate tasks processes and data handling in google sheets head over to courses.paris.io and check out our google sheets automation course now before going any further with our automation script i want to quickly um have you look at the format i'm using for the date and start time because it's not good enough for me to schedule um you know a whole day event instead i want to have here um this let's say jane be the community manager in charge from seven until 12 and then from 12 until 10 so this is doing using the the not american way of writing the 24 hour format of writing hours um so i want to split this up so in this case i'm expecting to have two separate calendar events one running from 7 to 12 and one from 12 to 10 and i'm combining it with the date and you see up here so it's the 1st of june 2021 from 7 and the end will be the 1st of june 2021 until 12. again here in european countries we tend or we typically will write the day first and then the month that's why you see one stands for today and six for the month how did i get that specific format i used format number more formats more date and format times down here i found this and i thought hey that's exactly what i'm using for what i'm looking for this one here that's the one i chose so here by the way this is also highly configurable if you need to create your own format go ahead and do so i just found that this one works really well for the use case i have here for this example so day month hour minute okay perfect so we've applied that now let's head back into our code because we've only written half of what we need now we have to actually start creating the events because we've gotten hold of the calendar the sheet we've got the data and now we have to actually create these events so i'm going to say schedule which you remember the schedule contains the separate rows from our google sheets with the actual schedule so schedule and i'm going to use it for each loop function entry curly brackets and that doesn't belong there let me cut that out and paste it down here that looks better good so now what i'm doing here is i'm saying okay on every single row do the following so the schedule is everything or so like so everything and on each iteration some looping through on each iteration entry stands for this and then this and then this so schedule the whole thing entry stands kind of like as a placeholder for every line or excuse me every row we will be looping through and we're going to say community calendar there you go please create an event there you go by the way auto complete awesome thing love it and we're going to and you see here again this this is awesome if you use the create event you're going to have to pass in the title so the title of the event the start time and the end time okay we have all that information awesome so let's pass it in entry to entry zero and entry one if you're asking yourself what is she writing then here is the explanation so remember this is going to be the title this is the start date or the start time and the end of that event so two zero one two zero one what that means is we will be creating on the first iteration an event called jain with this start and this end and on the second iterations on the second looping through this data atom start time and time i mean that's a theory we still have to test if it works but that's what we're doing here so we're looping over this data and this is a way to access data in an array because what we're having here is essentially an array that's how this is saved and this is position 0 position one and position two and that's exactly what i'm seeing here on every iteration looping through the title get the value of what's ever in position two so index 2 the start time index 0 and the end time index 1. that's because in arrays in computer language you usually start counting at 0. that's why it's 0 1 2. okay so far so good let me make sure i already saved it okay well now it's time to test our code let's make sure that we have to create calendar selected as um the function we want to execute and let's hit run feeling a little bit nervous let's see if it works down here the execution log appears so that's our first hint if something goes wrong or not review permissions yes by all means i permit or google uh no jane permits this code to run to access the calendar because we're accessing the or using the calendar app service so you know we have to grant access here so in order for that to work execution started that's a good sign so that means probably no syntax errors execution completed okay oh what happened let's have a look let's go over to our calendar so you remember the first entry should be on the 1st of of june at 7 in the morning let's have a look first of june's wow there you go jane adam laura jane adam laura awesome it worked it worked like a charm no problem there you go there's the code you need to automate something like that you need to create maybe i don't know for your school for your work for workshops you're planning you need to add a bunch of calendar events then no problem go ahead use this code you need to invite people no problem because you can add the invitees the email addresses as uh as parameters here all you have to do is check the documentation that's something i say over and over again check how to use the create event method and you'll find out this is super useful and will save you a bunch of time believe me i hope you enjoyed the video go ahead and share it with your friends with your colleagues go ahead and reuse this this code i'm happy to share with you and do check out the other apps script video tutorials we have here on this channel and go ahead and subscribe to the channel as well so that you never miss out on any of these video tutorials also concerning gmail google drive and so on and so forth

Channel: saperis

Views: 3,520

Rating: 5 out of 5

Keywords: create calendar event from sheets, create calendar event from google sheets, google calendar, google sheets, create event from sheets, automate google calendar, apps script, google apps script, coding, google workspace, saperis, chanel greco, script, How to automate google calendar

Id: kYmdsOvUOng

Length: 14min 51sec (891 seconds)

Published: Tue Jun 29 2021