Create Calendar Event from Google Sheets with Google Apps Script

[@glenngrubb9824]

Finally, somebody who is not participating in a speed-typing contest! Thank you for teaching at a pace that someone can follow. I will certainly be following your other videos.

[@saperis]

Happy to hear that Glenn. Please take note of the shift in focus on this channel: kzbin.info/www/bejne/hXnHkKt7lsecrMk

[@jaguar-w9o]

I have spent days and nights to do this, researching blogs, youtube, AI, etc. Finally, you made my day! Thanks a million!!

[@bayzano]

Works like a charm. I made a car-sharing calendar for me and my neighbors with a Google form that was automated with this function. Way to go!

[@saperis]

That's a very fun use case for this automation. Have fun sharing your cars. 🚗

[@tomspine]

This was excellent! Your pacing was perfect. So easy to follow and understand. Thank you so much!

[@saperis]

Glad it was helpful! 😃

[@arturcamargo6216]

Girl you're amazing. Thanks for sharing your content. You explain in a very clear way and you help people like me taking the first steps into coding in an easy way.

[@saperis]

Thank you for the feedback. I know how difficult it can be starting to code. So I'm happy you enjoy learning with our videos.

[@PedroSilva-bc9gy]

Thanks from Venezuela. You are great!

[@FabioDozzadeMiranda]

Amazing, thanks very much! . But how to make it easier for the user not to have to type the date twice, just the times, and including the date, which is in another column, as many times as necessary, in case there are several times in other columns to fill in (all times of the same day , which was already filled in in the previous column)?

[@saperis]

You can adapt the script to your needs. Whenever you automate something with Apps Script, check the developer documentation to understand how to write your own script: developers.google.com/apps-script/reference/spreadsheet

[@MichaelDaniels]

Thanks! This was so helpful. I am sooo not good at code. I really how you explain. Where do you find the code you create?

[@saperis]

Thank you for supporting the channel! I make up all the code examples myself. Very important when working with Apps Script, learn to work with the official documentation. That's where you find out what methods to use. Without the documentation it's only guessing. developers.google.com/apps-script/reference

[@RoeGabriel_i_EMPOWER]

So nice to see such an intelligent woman coding. You’re awesome. You explain it so we’ll. Also SKedule lol.

[@saperis]

Thanks for watching! By the way, my new videos are all about learning JavaScript. Just in case you might be interested. 😀

[@RoeGabriel_i_EMPOWER]

@@saperis I'm very interested. I know SQL so its not a huge leap to jump to JS. I will follow your videos and let you know when I've created something using your guidance.

[@eicoz]

Excellent lesson video. We studied on this video at our class 💯

[@saperis]

Makes me very happy to hear that 😃

[@thomasjohansson6820]

Dear Need to start to say that you have amazing videos about coding in KZbin. Thanks a lot! Question: In a sheet I´ve all the activities like training, matches etc. My next goal is to automatically create calendar events from the sheet. I´m new with Google script. What we need is a script that: 1. Create an event from a single activity for an example match. Start and end date/time is the same 2. Create events from a start date/time and end date/time, like every Monday 6 PM-8 PM. -both from the same Google sheet

[@saperis]

I can confirm that this is possible with Google Apps Script. Maybe consider hiring a freelancer to create this for you: workspacedevs.com/

[@kodemonki]

This is amazing! I used it to add bulk created Zoom meetings to my conference Google Calendar. Clear, simple, thorough. Thank you!!

[@saperis]

Thanks for the feedback! It's always nice to know when a video of ours helped. 😀

[@reneemoree3754]

Kate, were you able to email the event to others through the script?

[@AnneBronte-s2t]

is there any way to make this an automatic sync thing? in a way that people can just keep adding entries to the google sheet and it automatically appears in the google calendar? (if not completely automatic then maybe a button that they can press after adding an entry to the same sheet to add it to the calendar)

[@Vin-nq4fz]

Just subscribed for your clear explanation. Thanks!

[@saperis]

Thank you for watching and supporting the channel 👍

[@katiecook9005]

I wanted to create a google form that would allow for one of the questions to schedule an event. I did this and connected it to a google sheet where I had the scheduled lines separated and moved to another tab. I then wanted to have the information on the scheduled tab be the information added to my calendar. I had to change the script slightly to have it call out for the specific tab, but even though when I run the script it says it works, nothing is moved to the calendar. what do I do now?

[@alisonhollier3229]

Love your clear explanations - video is great. I just wanted to know if it should update automatically on the calendar every time you add an new event to the spreadsheet or is it a one off upload ? Mine doesnt seem to do it.

[@learningstuff5679]

Awesome tutorial!!! Thank you so much. I had mistyped some of the code and spent a frustrating 5-10 mins losing my mind trying to figure out what was wrong but got there in the end.

[@saperis]

Glad it helped! 👍

[@GeeDeaux]

Thanks for the great video! How would I go about limiting this code to only run on a section of the sheet, such as rows 03 - 10 for example?

[@marcobanderas3306]

You're the Queen of Spreadsheets

[@saperis]

You're so welcome! Thank you for learning with us on our channel.

[@stevemcvaugh4928]

I like the way you teach, I want to build a program to add customers, schedule them and price the job all in sheets. I have been paying companies to do it and their programs suck. I will be watching lots of your videos

[@saperis]

Thanks for watching! We have lots of Apps Script videos you can watch. We don't publish any new Apps Script videos though. Watch this video to find out why: kzbin.info/www/bejne/hXnHkKt7lsecrMk

[@scsnstaff5815]

Thanks!

[@saperis]

You're welcome and thank you for supporting our channel! 😀

[@aqilahaina5686]

Is it only run if the data have times? May i ask how did you cut and paste during 9:16? Another question of mine is why your splice () and for each loop is black in colour while mine turns green? Because of that im unable to do the last line of code

[@aqilahaina5686]

And thank you if you can reply to this. I really tried more than 5 times. Is it possible to have the codes?

[@saperis]

To cut out something I use the keyboard shortcut of Command + X (on my Mac). For a Windows PC that would be Control + X. Those colors are indicating that there are no typos in my code. So it probably is an indication that you have to check your code again as there probably are mistakes like missing semi-colons, or something like that. No, I don't share my code other than through these videos.

[@Prospertxre]

How do you pull from a specific tab? I have a sheet with 4 tabs and I only want to pull the calendar entries from let's call it Sheet2. Not only that, but how do you get it to pull from a specific sheet? I may have multiple sheets open with different google accounts at the same time. I'd want this to pull a specific tab on a specific sheet and to add complexity, I'd want the title of the calendar entry to append and include the name of the sheet because I may have 20-50 different sheets that are all based on the same template importing their calendar entries into the single google calendar. Finally, I'd want it to change the imported calendar entry in google calendar as changes are made within the sheet. I do not want 2 way sync with Google calendar back to the sheet.

[@davidnorman7586]

I am also looking for a solution like this. I have multiple tabs that all have information that needs updated to a different calendar. How do I get the script to process one sheet/tab at a time and add it to a different calendar? Each sheet has a Calendar ID, but how do I write the script so that it processes each sheet/tab?

[@mmebernatchez3095]

This is very helpful! Thank you so much. As a teacher, I plan on creating a sheet with yearly lesson plan details and then automating the creation of the calendar.

[@saperis]

Glad it was helpful! 😀

[@mmebernatchez3095]

@@saperis Could we add a description to this? Moreover, if in the description text, there is a link, how would we do that and store that in the google sheets? Thanks if you have any ressources regarding this :)

[@Sippin777]

Thank you! God bless you. Subscribed

[@Nancy-gi9zl]

This has been such a help! Now, I have a question. I have a sheet with multiple rows and columns (about 60 rows and 60 columns). I want to create calendar events only for select rows instead of the entire sheet. How can I do that? I'm not familiar with script/code but I was able to understand this video and did a "dummy" sheet which carried over onto my calendar. So...I have a little bit of hope.

[@livinginsacramentocalifornia]

thank you for your videos. I have 2 columns to the left that I don't want as part of the event as they are just information for me. Would I have to use a different function altogether for this since the one shown is for all the data? error coming up array bc the left 2 columns are not included in my entries for the for each function

[@saperis]

No, you simply have to adjust the data range and omit the first two columns. Check the different getRange() methods: developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column

[@mechellegattis1934]

This was so helpful! I am needing this to schedule athletic practices for different facilities at our high school (basketball gym, soccer field, practice soccer field, etc) I need each entry to be added to the specific calendar. I have all of the calendars created already. Is there a way for the calendar ID to be linked to a cell in the spreadsheet where the user chose 'Location'? For example, if a coach chooses the Soccer Field for a specific date and time, an event will be created on the Soccer Field calendar. But the next coach might choose the Football Field. Thanks!

[@saperis]

In theory, this should be possible. But obviously, you would have to edit the code to make this work for your use case. Also make sure that all the coaches have full rights on the calendars so that the script can access them.

[@MatthewCavanagh]

Very comprehensive - thank you so much!

[@saperis]

Glad it was helpful!

[@ivanurbina2422]

Awesome Tutorial. Thank you. I have subscribed. This is a wonderful tool to have.

[@saperis]

Glad it was helpful!

[@reizhskrivadenn]

Hi ! Your tutorial is great! Everything is really clear and well explained accessible to an audience that doesn't know how to code. Thank you very much. One small question : is the table automatically synchronised? I mean, if you add an new date on the sheet, does it goes directly on the Google Calendar or do you need to execute the script manually each time you put a new entry? Also, do you know which formula is used for a event that goes all day ? Thank you very much and keep going this way!

[@PatrickMuvunyi-j4u]

Thanks! But how do we add guest and description for an event?

[@BhemPolina]

Thank you for sharing. It works for me.

[@saperis]

Great to hear! 😃

[@antoniograterol9371]

I really like the way you explain things, well done!

[@saperis]

Thank you 😀

[@renewolf2296]

This was SO helpful, thank you!

[@Kucingcomel-1]

Can it be auto updated once I update the sheets? Or I need to click run each time I update the sheets?

[@saperis]

You can use the onEdit() trigger to run the script whenever the Sheet is changed: developers.google.com/apps-script/guides/triggers#onedite

[@Garich-x5z]

Thank you so much for sharing the video on how to connect the sheet to the calendar. It was incredibly valuable, and I learned a lot from it. However, I'm currently facing a couple of issues. Firstly, when I add new content to the sheet, it doesn't sync automatically to the calendar. Do I need to run the script every time I add something new? Secondly, when I delete or modify existing content in the sheet, the changes don't reflect in the calendar. The old content still appears, and it doesn't get deleted or updated accordingly. I would appreciate any guidance or suggestions on how to resolve these issues. Your help would be greatly appreciated.

[@saperis]

You will have to adapt the script to your use case. The way I wrote the script, it only runs when you manually trigger it and it doesn't first check to see if the data in the row has already been processed.

[@mountolivelutheranchurchlhc]

Thank you so much!! I have a sheet that is collecting data for calendar events, is there a way I can get the new data received to create a new event without repeating previous scheduled events??

[@saperis]

Yes, you would have to update the script to do that. For example, you could have "DONE" or something written in the last column whenever a calendar event is created. Adapt your script to then always checks to see if "DONE" is present before it starts creating new events.

[@KaterinaApostolaki-o4k]

Is there a way to include events that repeat once per week until a specific date? Also, can we automate the notifications we receive for those?

[@saperis]

Whenever you want to use Apps Script to achieve something specific, I suggest checking the developer documentation. That's where you'll find out what's possible and how to get it done. Like how to create recurring events: developers.google.com/apps-script/reference/calendar/calendar#createeventseriestitle,-starttime,-endtime,-recurrence,-options

[@GoldenCobraDJ]

This is awesome! Thank you so much for breaking this down.

[@saperis]

My pleasure! 😀

[@chrisder1814]

hello Can you help me understand if the ideas I had on my feasible

[@carlanthonydelosangeles7411]

Very informative. Thank you.

[@saperis]

You are welcome! 😀

[@rabbitmp3443]

hi, i have facing this issue when running the same script. what should i do ? i've done so many google search and end up the fixing error by changing the format of the spreadsheet data but still facing this issue. what should i do ? "Exception: The parameters (String,String,String) don't match the method signature for CalendarApp.Calendar.createEvent. (anonymous)"

[@saperis]

The error messages probably means that you are not providing the right parameters or that they don't have the right format. See in the official documentation that you need to provide three parameters (title, start time, end time): developers.google.com/apps-script/reference/calendar/calendar?hl=en#createeventtitle,-starttime,-endtime

[@kima8454]

Hi! Thx for your help!! I making my schedule for the semester at college and want to know if I can change it so the different fill colors on sheets can also transfer to the calendar??

[@saperis]

Interesting idea. I did some research and here is what I've come up with. You can't change the color of the event when you create it in Google Calendar. But you can change the color once you've created the event. Here are the details about that: developers.google.com/apps-script/reference/calendar/calendar-event#setcolorcolor And here is how you can get the color of your cell background in Google Sheets: developers.google.com/apps-script/reference/spreadsheet/range#getbackgrounds That should get you started with your script.

[@rufusstewart762]

Hey there thank you so much this is so awesome for setting my rota for staff! Just wondering if I have a sheet where the data required for calendar creation is not necessarily on cells ABC and starts between L-N, how would I augment the code to account for this. Thankyou!

[@saperis]

Depending on your sheet you might want to use the getRange() method. There are different variants you should check but they all let you get a specific range, like columns L-N. developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column

[@rufusstewart762]

@@saperis Does it matter where this line appears in the script or can it go anywhere. Thanks so much for getting back to me :)

[@saperis]

Yes, the sequence or order of a script almost always matters. Generally it reads from top to bottom.

[@livinginsacramentocalifornia]

Hello, I am trying to do the same where I want the first 2 columns omitted from the range, as well as the top 2 rows like @saperis shows in video. Were you able to figure it out?

[@VishalSharma-lo5ol]

Thank you. I am using an on edit trigger to create new events but previous events are getting de-duplicated. Is there a way to add flag after events is created or some pointers Thank you

[@saperis]

Yes, you could use a tick box or a time stamp to check if the event has already been created. I used a similar method in this video: kzbin.info/www/bejne/iJ2Tk615mNeNmbM

[@lillyanab]

Have two questions How would I add description and color code it to a little category

[@saperis]

When creating an event you can pass in the description as explained in the documentation: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options Adding a color to an event is not possible with the built-in Apps Script interface to Google Calendar. You would have to use the Google Calendar advanced services for that. Find out more here: developers.google.com/apps-script/advanced/calendar

[@ClaireSchwaegler]

Thank you so much for this- super helpful! Is there a way to update/write the code so that it can recognize if events are already on the calendar or not so the script can be run/calendar can be updated without creating duplicates?

[@saperis]

Yes, you would have to add an extra column and add a timestamp or a text like "created" after creating an event for that row. Then you'd also have to change the script to first check if that column is empty. If it is, create the event, if not, skip the row.

[@stefandirghalli458]

Thanks for putting this together, it is very helpful! Is it possible to change the scripts to create "full-day" events as opposed to events with start and end times? I tried removing one of the entries in the createEvent command but the script returned a null error because I think it's looking for three pieces of data. Thanks!

[@saperis]

You can use the createAllDayEvent() method. Check the documentation to see how to use it correctly: developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-date

[@frmcf]

Thanks for this! I'm a freelance trainer and it would be really useful for me to have a tool that does this in reverse: ie. when I make schedule changes on my calendar, it logs them in my spreadsheet. I'm going to go ahead and try to make it. To take it a step further, I could have it generate my invoices as well. I've been doing this manually for years, but I think it would be a fun scripting challenge to automate it. :)

[@saperis]

You should be able to automate the tasks you mentioned. But obviously, the script to automate it, would be very different from the one in my video tutorial. Make sure to check the official Apps Script documentation to guide you when writing your script: developers.google.com/apps-script/reference

[@aershey1noor1]

Did you manage to figure the script out? If so, can you paste here? Really appreciate it!

[@pawankumarnalla1535]

Superb ma'am explanation We have beginners Can you explain each thing

[@srellestech8723]

This was super helpful! I appreciate this.

[@saperis]

Glad it was helpful! 😀

[@raphaelfoscarini]

does it will automatically add new event, if i come latter and add a new row to the table, with new information?

[@saperis]

No, this script only runs when you click the button. You could use an onEdit trigger to achieve this level of automation: kzbin.info/www/bejne/gXSaoXWgeZh6fdk

[@Mitch-Q]

Thank you so much for this, however when I enter this code is it only creating an invite for the first row of the data (row 3) and not the rows following. What am I missing ?

[@saperis]

I don't know what went wrong. Maybe a typo in your code?

[@aabarchaeologie]

Thank you for the tutorial. I really loved your video, but I am new to this and I am setting up a booking system that I want logged in Google Calendar. I have 2 issues: 1. when I try to add a new column it should check (e.g. entry[3]), the code returns an error every time (the field is not blank in the table, so I am not sure what causes this); 2. If I have a range, say a week, in the calendar the last day is not included and I think it has to do with the fact that the time it takes is 12.00 am, but not sure how to solve this.

[@saperis]

I don't offer any coding services. I suggest you hire an Apps Script freelancer to help you set up your booking system: workspacedevs.com/

[@aabarchaeologie]

@@saperis Oh, thank you, I was not requesting that. I was merely trying to figure why I am getting an error in Apps Script when I try to add another column from which info should be included in the Google Calendar.

[@saperis]

@@aabarchaeologie Did you adapt the range in the script to account for that extra column? Did you check the Google Calendar docs to see if that information is accessible via Apps Script? If yes, did you check what data type the interface is expecting (e.g. date, string, etc.)?

[@alixwest3537]

Im trying to streamline a paper calendar for scheduling shifts to an online version. Any recommendations on how I can have approximately 20-30 different people be able to sign up for shifts for one of two different positions and if need be drop shifts and have scheduled shifts appear on a calendar for anyone to view? Thanks

[@saperis]

If you want to achieve this goal with only Google Workspace tools: - Have everyone sign up for the shifts in a Google Sheet where you plan out all the shifts. - Use an automation script to generate a calendar event for every shift. Have these events added a general Google Calendar. If you want to have a proper tool for planning your shifts I'm sure there is a software that enables the planning for shifts.

[@Unpopulr]

Hello there. Super helpful. So I am not a coder but I would like to use the code. I've done everything mentioned in this video but once I run it an error message states undefined and it's indicating the error is on line 5. I see this video was created 2 years ago but hopefully you have time to get back to me on this. Either way thank for sharing.

[@saperis]

Whenever the error message contains the word "undefined" it usually means that the variable is empty. So you are accessing a variable on line 5 which doesn't contain a value. Check this video to learn how you can find errors in the code: kzbin.info/www/bejne/m5fSiZKAab5-nNE

[@tanyavandervecht6758]

I have this connection working with a sample file. If I add new rows of information though and run again, all of the data that was there prior is duplicated. I also tried adding a time trigger, now every minute it duplicates all of the information in the calendar endlessly. How can you automate this to only import new data to the calendar? Note: Tickboxes is not an option as multiple users are using this sheet and I want the sync to the calendar to be automated to update by the minute. Tickboxes leave too much room for error and then time consumed by deleting the duplicates it creates.

[@saperis]

I can think of two possibilities: A) You add some sort of timestamp, checkbox, text or whatever on the sheet for every row (e.g. event) you handled. B) You get a full list of event from the calendar and then check them with the rows in the sheet. If you can't find an event for the the row in your sheet, you have to create a new one. I would go with option A) as option B) is much more difficult and will make your script much slower as there is lots of data to process.

[@beccalamon6777]

Get tutorial. Worked like a charm; however, as I update the sheets and run the script again it duplicates the calendar events. Is there code to add to the script to prevent this from happening?

[@saperis]

I have no code available for what you are trying to achieve. But you can code it yourself. Check out the developer documentation to learn more about Apps Script: developers.google.com/apps-script

[@annuannu1743]

Thank you so much ❤❤

[@AbigailDougherty-b9o]

how can i do this with both timed events and all-day events? also, every time i run the code, it adds another event instead of replacing the existing one

[@saperis]

You can either use the createAllDayEvent or the createEvent method. Check the documentation to find out how to use these methods: developers.google.com/apps-script/reference/calendar/calendar#createAllDayEvent(String,Date) You can adapt the script to first check if the event has already been created before it creates one. You could use a timestamp or a "Yes" in the last column to signal that an event has already been created.

[@richardthemagician8991]

Great tutorial! A couple of questions. Will the script run automatically every time there's a new event added to the sheet or is it set on the timer? I'm assuming I can just have checkboxes until the script to add an event only if the box is AND there's actually an event present. Second question: if I wanted to make this and then share it with a client, what would I need to type into the script so it will look for a particular cell on the sheet where they can paste their calendar ID instead of having to put it in the script itself? I'm very new the app scripts. Try to move away from using all the plug-ins. I appreciate the help.

[@saperis]

First answer: No, this script will only run whenever you trigger it yourself. Check this video to see how you can use triggers to automatically run the script: kzbin.info/www/bejne/gXSaoXWgeZh6fdk Second answer: You could have the script get the data (e.g. values) from a specific cell where the user has inputed their calendar ID. Check the documentation to see how you can get a specific range (e.g. cell) from a sheet: developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getrangea1notation

[@keithmottley5315]

I am trying to use this script to pull data from a Sheet that is populated by responses from a Google Form. Would anyone know how to have the script only pull a new row to Calendar each time a row is added? I don't want duplicate events created everytime a user adds a new row.

[@saperis]

Yes, you would have to update the script to do that. For example, you could have "DONE" or something written in the last column whenever a calendar event is created. Adapt your script to then always checks to see if "DONE" is present before it starts creating new events.

[@ainulibnukhotob8532]

thank you for the tutorial. but i have a question: in case we have email data on spreadsheet, can we also set the email to be automatically invited to the calendar created?

[@saperis]

You should be able to pass the email addresses as options when creating a new event. Check the documentation on this: developers.google.com/apps-script/reference/calendar/calendar#createEvent(String,Date,Date,Object)

[@benvb8457]

Hey thank you so much for this. In my situation I really don't have an end time or date and I don't mind not having one . Will the formula still work with out a end date? Thanks I hope I hear back from you

[@saperis]

If you check the Calendar Apps Documentation you will see, that you can either create all day events or events with start and end time. developers.google.com/apps-script/reference/calendar/calendar

[@davidjenkins5985]

Is there a way to set notifications, and can the available status remain as free instead of defaulting to busy?

[@saperis]

According to the documentation the createEvent() method doesn't give you the ability to do that: developers.google.com/apps-script/reference/calendar/calendar#createEvent(String,Date,Date,Object)

[@strongwatercampingcabins1116]

I really enjoyed your video, so easy to follow. I am trying to create a reservation consisting of several days automatically from Google Sheets to Google Calendar. I have the whole app script working until I actually have to createAllDayEvent. I wonder if you could help me. It keeps telling me that I can't do (string, string, string) for the name of the reservation and the arrival and departure dates.

[@strongwatercampingcabins1116]

Here is what is I've tried. function AutomateCalendarEvent() { let swCalendar = CalendarApp.getCalendarById("our Calendar Id"); let sheet = SpreadsheetApp.getActiveSheet(); let reservation = sheet.getRange(2, 3, 4).getValues(); reservation.splice(0, 1); { swCalendar.createAllDayEvent('Last Name'[2], 'Arrival Date'[3], 'Departure Date'[4]);} }

[@saperis]

I'm not quite sure if this is just because you are explaining how you wrote the code. But if you have variables lastName, arrivalDate and depatureDate, you do not want to wrap them in quotation marks. If you are, that's probably what's causing the error message as you are passing in strings (a type of data).

[@strongwatercampingcabins1116]

@@saperis Thank you for replying so quickly. I have tried quotations and no quotations. I've tried using the column headings (which I've used here); or title, startDate, endDate; or a combination of both with "Last Name", startDate, endDate. I still get this error message: Error Exception: The parameters (String,String,String) don't match the method signature for CalendarApp.Calendar.createAllDayEvent. AutomateCalendarEvent @ Code.gs:7

[@saperis]

@@strongwatercampingcabins1116 Maybe the official documentation can better explain how to use the method: developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-startdate,-enddate

[@jacobherrera9]

I have a calendar built in google sheets that has about 35 people on it showing their work schedule. With letters showing shifts they will be working. Would this work for me? If a date is changed would it reflect on google calendar?

[@saperis]

Well, you would have to adapt the script to your needs. The way it works now is that it create a new calendar event for every row in Sheets.

[@komalkhiriya3858]

Thank you so much... Your videos are very helpful... Can I insert 2 more column in the sheet like attendees (Guests) & meeting ID? What additionally I'll need to write in script to get these 2 columns captured... Thanks again!!

[@saperis]

If you want to add more information when creating the event you have to check what the method let's you do. That you do by checking the official documentation: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options

[@danielamoralesleal7860]

Hi thank you for the tutoial, i'm pretty sure i followed it step by step but i got this error 'Exception: The parameters (String,String,String) don't match the method signature for CalendarApp.Calendar.createEvent.' I already checked that the date data is in the format date, can someone help me? Edit: I tried it on a spreadsheet that has multiples sheets, when i tried it for a spreadsheet with just one sheet it worked but i can't make it work even with getSheetByName()

[@saperis]

Well, I can't tell for sure what the problem is. If you search on Google with your exact error message you might find further ideas on how to solve the problem. I found this that might be useful: support.google.com/docs/thread/51982394/previously-working-script-to-create-calendar-event-from-sheet-now-returns-exception-message?hl=en

[@gincl01]

thanks for your always clear explanations !! What if I want to add a 'description' in the events I want to create ?

[@gincl01]

Ok I found that I could do that via the 'options'

[@saperis]

Great!

[@ValentinBarCohen]

Great explanaition! can I also add guests to my invite? did not manage to do so, thanks!

[@saperis]

Check this video to see how to add guests to an event: kzbin.info/www/bejne/pqLGnZSjhbqGpq8

[@AlexDKennedy]

This is really useful. Thanks!

[@saperis]

Happy to help. 😀

[@jasonfloyd2617]

This was a great tutorial! One question - if I am submitting a Google Form that is setup into sections, then it is putting the information in new columns for each section. So how to I set it up to pull from a different column if that section is blank? I am assumming some form of an IF statement but I cannot seem to figure it out. Thanks!

[@saperis]

You could use a conditional statement for this. Let's say you want to check if cell B1 is empty: if ([whatever variable name B1 is stored in] !=== " ") { then your code} Find out more about conditionals in here: developer.mozilla.org/en-US/docs/Learn/JavaScript/Building_blocks/conditionals

[@jasonfloyd2617]

@@saperis Thank you for the response! That was very helpul! I stumbled across another issue - I have a start date and an end date in two seprate columns but when the event posts to the calendar it is only showing for the start date. I have looked up different classes like createAllDAyEvent but still cannot seem to figure out a way to get an event to span say 2 or 3 days.

[@tawshisms]

this was great as usual!...how do i stick in removeAllReminders() so the default reminders dont get created too?

[@saperis]

The official Apps Script documentation has a code sample that should be able to help: developers.google.com/apps-script/reference/calendar/calendar-event#removeallreminders

[@fahadameen2558]

Thank you so much it is really helpful and well explained... Actually making this for Event Management System i have a Google Form for proposal and the approved that is updated on calendar... But I'm facing an issue in Data Range how we can fix if a new row add under data its automatically create an event because if i Execute the log it create the same events again and again from start to bottom.. Your response is valuable for me... Thanks!!!

[@saperis]

For your use case you will have to change the script. - Have the script first check if the event has already been created. You could do this by adding a time stamp or "created" for every row you have processed. - Use the onEdit trigger so that the script runs every time a new row is added. See this video for more information about triggers: kzbin.info/www/bejne/gXSaoXWgeZh6fdk

[@fahadameen2558]

@@saperis Thank you for your response... Actually the issue is when i'm using 'Trigger' then its create the same Event on Calendar when its Trigger the code runs from start to bottom

[@saperis]

@@fahadameen2558 As mentioned in my first answer, you will have to adapt the script to first check if the event has already been created. That is not something you will find in my code. This you have to add yourself.

[@chrisder1814]

hello, do you know api connector? do you use AI for coding? can I find "templates" ?

[@awaketripsibiza7635]

Hi @saperis! Amazing video. Love it how you explain every detail!!! What I'm struggling with is how do I transfer extra colums to the "meeting notes" part of the appointment in the Google calendar? Thank yoouuuuuuuuu

[@saperis]

According to the developer documentation, the createEvent() method allows you to add optional information like a description. But it doesn't give you access to adding an attachment as meeting notes. developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options

[@EliotHochberg]

A great video! Definitely appreciate the clarity and demonstration. I have this working, but I have a puzzle: I want to use a spreadsheet to be able to change dates as well. Is there a way for the script to delete an event it already posted and replace it? The only thing I can think of so far is to create a separate script that somehow recognizes a selected cell, activates, and then you can replace. But I don't want to run the whole script again on the same page, as it will duplicate the event. So then the second question is, can such a script check if the event is already there and not add it again?

[@saperis]

The script can do (almost) anything you can come up with. An easy way for a script to check if an event is already created is by using another column and adding a timestamp to it whenever an event is created. So the next time the script runs you could have it first check that column. And yes, you can modify to script to also delete. Again a column for deleting might be an option.

[@EliotHochberg]

@@saperis Thanks for your response! I do have to make this pretty bulletproof for other users, so it ideally should do all of that automatically, i.e. if there needs to be data in another column, then it would need to do that for each date that is entered or changed when it is done. Can you direct me to any resources that might help me work that out?

[@1985andrewg]

Hi... How would one change it from start and end times to full-day events that show at the top of that date in the calendar and not fill in the entire day as an event?

[@saperis]

There's a method for creating all day events: developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-date

[@romax7351]

Is it possible for this script to update each time there is a new entry in de Google sheet?

[@saperis]

Yes, you could change it to do so. Check this video about triggers to learn more about them: kzbin.info/www/bejne/gXSaoXWgeZh6fdk

[@johngewald]

How do i create an event during the whole Day? When i create a startdate from 0:00 till the same date 0:00 , it doesn't create a event during the whole Day.

[@saperis]

Have a look at the official documentation: developers.google.com/apps-script/reference/calendar/calendar?hl=en Depending on what kind of event you want to create you have to choose a different method to do so.

[@treyhughes3869]

This was extremely helpful. I got it to work making my data match this format, but now I am stuck on how to tailor it to my needs. I need it to create all day events for my endurance group (list of possible races in the area as well as other big events. Somehow I keep running into issues.

[@saperis]

Maybe have a look at the documentation on how to create all day events: developers.google.com/apps-script/reference/calendar/calendar#createAllDayEvent(String,Date)

[@ecoed96]

Thank you, this works like a champ. The only problem I'm having is when my google form updates the google sheet it is creating duplicate events. I haven't been able to figure out how to fix that. Any suggestions.

[@saperis]

You would have to build in a check to see if the event has already been created. You could add another column where you write "done" or a timestamp whenever you have created an event.

[@ecoed96]

How would I go about doing that. I haven't had any luck finding a way to automate adding a time stamp and check if the event was created. Could you point to a video that could help with this.@@saperis

[@CompassSolarEnergy]

Is there a code to auto-populate the description and address in the calendar event?

[@saperis]

I don't know off the top of my head. But you should find your answer in the developer documentation: developers.google.com/apps-script/reference/calendar

[@KathrynJimenez-u4x]

Hi! Can you teach how to add a custom menu for this specific script? Thank you!! Yours worked when I tried others.

[@saperis]

In this video I create a custom menu like the one you want: kzbin.info/www/bejne/rYfLeKp3fK2ed5Y

[@KathrynJimenez-u4x]

​@@saperis Thank you!! Sorry another question is, I am trying to use this google sheet to track our events. So, creating a calendar event through google sheet will be useful. But, whenever I sync it, it creates a double/triple... events on calendar. Can you help me with this please?

[@saperis]

@@KathrynJimenez-u4x For your case you have to adapt the script. I don't offer that service. Consider hiring an Apps Script freelancer: workspacedevs.com/

[@jonathanlabbe3506]

Amazing! Is there a way to prevent duplicates of events already added to your calendar?

[@saperis]

Yes, you would have to change the script to first check if that event was already created.

[@johnwinters4324]

Hi, thanks for making this it is incredibly helpful. I am trying to see if there is a way to bulk delete all of my events and then re-run your script to re-import them. Haven't been able to get it to work, any thought/suggestions?

[@saperis]

You could maybe use the getEvents() method to get all events in a certain time and then delete them.

[@kurteades6797]

The Lesson and Content were nearly exactly what I was looking for, Great Job! I have an Appsheet Calendar App which I use to collect new business events. This data goes to a gsheet, I am going to try to use a Bot within the Appsheet to pass the data from the sheet to the Google Business Calendar. I had a follow up question. How can I configure the script to only pass new data, currently every run adds all the data from the gsheet to the calendar (so duplicates)?

[@saperis]

Do the following: 1. Whenever you create a new event add a time stamp or a "Done" or whatever in the last column of the row. 2. Edit the script to check first if there is a value in the last column. If there is one, go to the next row. If there isn't one, create an event. So you have to edit the script to check first if the event has already been created.

[@kurteades6797]

@@saperis Thank you. What if I want to add more things to the calendar event? Like Description and Attendees

[@saperis]

@@kurteades6797 Whenever you want to automate something with Apps Script, you check the documentation to see what methods you can use. Have a look at the methods for Calendar: developers.google.com/apps-script/reference/calendar/calendar#methods Adding guests to an invite only works with the Calendar advanced service. Check this video for further information: kzbin.info/www/bejne/pqLGnZSjhbqGpq8

[@aciphThihthi]

If I update/add an event to Google sheets, will there be corresponding update on Google Calendar?

[@saperis]

No. This script reads your Google Sheets once and then creates calendar events based on that. It doesn't have a live update feature.

[@metaeditors]

Incredible helpful to get started. THX

[@saperis]

Great to hear! 😀

[@ycpdps]

Enjoyed this tutorial a lot and was able to apply it to a project that I'm working on. I'm working on a solution to add data to the google sheet using a sidebar html form;, which I then dump into my calendar. The sidebar form includes two date/time fields using html form type="datetime-local". Unfortunately, my date/time result (ex. 2021-06-30T08:00) separates my date and time with a "T" which causes my code to fail as it won't accept the "T" as part of the parameter. Any suggestions?

[@ycpdps]

Is it possible to send the date and time to the calendar as separate data items?

[@saperis]

Hi John. Maybe you could create a new date based on the datetime-local HTML input. Something like this might help: const date = new Date("2016-07-27T07:45:00Z"); Read the details on how to handle date and time in this blog post: www.toptal.com/software/definitive-guide-to-datetime-manipulation

[@saperis]

Unless you are creating an all day event, you need to send add at lest the date, start, endtime of the event to create a new event. These are all separate parameters that are required at the same time for creating a new event.

[@NeerajNidhi]

This is amazing, need your help in exporting google calendar data (like who has attended the meeting and for how much duration), please help me with the script

[@saperis]

There is currently no way to get that data. None of the APIs give you access to that information.

[@syedaanamsultana]

How would I automate spaced repetition sheet to calendar

[@saperis]

You could maybe use a trigger to automatically run the script: kzbin.info/www/bejne/gXSaoXWgeZh6fdk

[@shirleyyang358]

This video is really help! Is there a way to pull calendar info from specific range in specific tab?

[@saperis]

Theoretically yes. Check out the official documentation to find out how you would have to write your script: developers.google.com/apps-script/reference/calendar/calendar

[@carsonguo5013]

Very nice orientation. I would like to know how to add hyperlink in the google sheet then transfer to Google calendar so when the users click on the calendar, it will take them to the pages linked

[@saperis]

When creating a new calendar event with Apps Script you can add more information. Check the documentation to see what types of information you can add: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options

[@courtneypropp3678]

This is very helpful! I keep getting an error code I'm hoping you could help with, it says "TypeError: Cannot read property 'createEvent' of null" I've copied your code exactly as written, and my sheet is correctly formated, so I'm really not sure what else to try!

[@saperis]

There could be different reasons for this error. "null" always stands for nothing. Are you trying to run a function that doesn't exist? Are all characters like () and {} written correctly?

[@barbieb3677]

My CalendarID shows as my gmail address and won't work with this script? Is there something I need to fix? Thank you.

[@saperis]

Since this is not a calendar attached to a specific Google Workspace user, it looks different from your calendar Id. But the script should work for your calendar. Please check for any possible typos.

[@jenniferwilhelm9986]

This was great. Thank you. One issue I'm having is, anytime I make a change to the events in my spreadsheet and re-run the code, it creates a second calendar event instead of replacing what was there. I tried four times, and now I have four events for the same date and time, even though I deleted the first (and second and third) event and replaced it with updated details. What am I doing wrong? Thank you!

[@saperis]

You're not doing anything wrong. The script I use in this video simply creates an event from Sheets. So if you run the script multiple times, you get multiple events. For your situation you would have to edit the script to first check if an event is already created. You could, as an example, have a date or a string "created" added to row when the event was created.

[@jenniferwilhelm9986]

@@saperis Thanks for the quick reply! I'm not a coder so while I understand what you're saying, I don't know how to do it! If you ever make a video on it, I'll watch ;-)

[@saperis]

@@jenniferwilhelm9986 I don't do any Google Apps Script videos anymore. Find out more in this video: kzbin.info/www/bejne/hXnHkKt7lsecrMk

[@jenniferwilhelm9986]

@@saperis Gotcha! Thank you!

[@MrDeso89]

Hello. my Script is working but it still not sync to my calendar, ive tried everything :(