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.
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.
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.
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!
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.
Dear Need to start to say that you have amazing videos about coding in TH-cam. 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
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
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: th-cam.com/video/OHd_xEabivg/w-d-xo.html
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 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 :)
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
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
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. :)
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
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!
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.
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.
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)
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.
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: th-cam.com/video/efoX_J4YKfo/w-d-xo.html
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!
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
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?
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.
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.
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.
Hi! I love how simple your codes are! I was trying to work something out for deleting events by adapting your code. Here is what I tried: function deleteClasses() { var sheet = SpreadsheetApp.getActiveSheet(); var calendarId = sheet.getRange("B2").getValue(); var eCCal = CalendarApp.getCalendarById(calendarId); var classes = sheet.getDataRange().getValues(); classes.splice(0,4); classes.forEach(function(entry) { eCCal.deleteEvent(entry[2], entry[0], entry[1]); }); } I end up with a "TypeError: eCCal.deleteEvent is not a function". the .deleteEvent prompt doesn't show up as well. when i'm typing it into the script. Thank you!
After a bit more research I'm guessing I have to somehow get the event titles or get the events in order to match them to my sheet and then attempt to delete.
Yes, if you want to delete an event you need to identify it first. For instance, there's a getEventById method you could use: developers.google.com/apps-script/reference/calendar/calendar#getEventById(String)
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.
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.
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
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.
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?
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
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
This is really helpful, thank you so much for creating this, I have 2 follow-up questions if you may assist. Is it possible to trigger additional actions by adding more columns ie 1. Adding column that will trigger to a guest invitation as this event is created 2. Adding column that will add as information to be included as calendar details such as location & details Also I ran the execution and thereafter made an edit on the sheet. Good thing is updated entry showed up but it failed to remove the old entry, how should i have that removed?
Here are some tips based on your questions: - If you want to add more information, like location or guests, when you create a new event check this: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options - If you want to not only write but also delete events you can use the deleteEvent method: developers.google.com/apps-script/reference/calendar/calendar-event#deleteevent
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?
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
To avoid duplicating events and also to avoid scheduling events on the same date and time i used this inside the forEach loop: let event = calendar.getEvents(entry[1],entry[2]); if(event.length === 0){ calendar.createEvent(entry[0], entry[1], entry[2],entry[3]); } During my tests the only problem with this approach were for events with start/end time being the same.
@@thales7783 Without the error message is hard to tell what's not working. One thing to notice is that the indexes used on entry[] are based on my spreadsheet, which is different from the one presented on the video. On my spreadsheet the startTime data is on the second column (entry[1]), while on the exemple is on the first (entry[0]). Besides that i would say to recheck the separators.
Hey, thanks for the tutorial! I also have a question: is there a way to repeat the events annually? For exemple, by setting the birthday of my students, create an annually event that could remind me to congratulate them
Currently, time driven triggers that read your script, can run as frequently as once every minute and as infrequently as once per month. So I see no direct way to send out a message once a year.
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.
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: th-cam.com/video/KC7pBjD3GGw/w-d-xo.html 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
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
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!!
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
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
Yes, you can. Check the documentation to see how you have to add further information for the event: developers.google.com/apps-script/reference/calendar/calendar-app#createeventtitle,-starttime,-endtime,-options
function ################() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("########"); var calendarId = "####################"; var calendar = CalendarApp.getCalendarById(calendarId); var dataRange = sheet.getDataRange(); var values = dataRange.getValues(); var lastRow = sheet.getLastRow();
for (var i = 1; i < lastRow; i++) { if (values[i][5] !== true) { // check if checkbox is not checked var title = values[i][0]; var description = values[i][1]; var location = values[i][2]; var startTime = new Date(values[i][3]); var endTime = new Date(values[i][4]);
var event = calendar.createEvent(title, startTime, endTime, {description: description, location: location});
Excellent video, but one thing still looking for is how to automate the process to when a new user enters an event in sheets, gets update to calendar without repeating the already events, so far it keeps creating a copy of each event.
You can do all of that but you will have to change the script quite a bit. First, you need to add a check to see if the event has already been created. Then you need to add an onEdit trigger to catch any changes to the document and have the calendar event creation triggered.
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)?
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 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: th-cam.com/video/pqclamPUSpM/w-d-xo.html
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??
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.
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?
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.
@@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?
Many thanks for this tutorial! Works really well. I have 2 questions though about making modifications and adding new events lines in Sheets. When I make a change, I run the script to make the updates in the Google calendar. However this then creates a second calendar event. Is there a way to get the script to only modify existing calendar events or add the new events? And secondly is there a way to create a button to rerun the script from Sheets without opening the Script editor tab? Many thanks in advance!
Of course you can do all you have described. Check the official documentation to find out how to do this. That's what I do when I want to make an automation script. developers.google.com/apps-script/reference/calendar/calendar-app
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
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 ?
Amazing tutorial! Tweaked some codes to implement for my calendar even though I have zero knowledge of JavaScript. I have one question though: How do I set the event color? I have tried everything and setColor() doesn't work. Thank you so much!
setColor() expects you to choose from one of the available colors. I think there are about 13 colors you can choose from. They are numbered through. So you would have to write it like so: event.setColor(1). I haven't come across a list will all colors though. So just try them.
Thanks for the amazing video, i got the result. But when i added another entry in the sheet and ran the code. It duplicated the previous events and also created the new event from the new entry in the sheet. 2 Questions 1. How do I create a trigger such that the code runs automatically and creates the events on the calendar 2. The code should create events only from the entries made today. And should ignore the previous entries. I have never coded my entire life, and i could do it in the first attempt. Love the effort!
You can do all you have mentioned. But you will have to adapt the code to your wishes. If you don't want to run the code yourself, but instead have it run once a day, you could use a time driven trigger. Find info about that here: developers.google.com/apps-script/guides/triggers/installable#event-driven_triggers You could create a conditional to check if an event was already created. If so, skip that row, if not, create the event.
@@saperis Thanks you for taking the time to reply. But, like i said i can't code. All i can do is copy what you've shown in the video. Is there any video you've created thats explains these. Or if you have any visual material please leave a link. I would love to watch it and replicate. Thanks again! ;)
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
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
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
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
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)"
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
@@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?
@@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/
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
This was so helpful!!! Thank you. I've used this in school to book out computing devices. The sheet is generated from a Google form. I would like for it to automatically update on the calendar without repeating events. Is there a way to do that?
You could change the code so that it only create an event once. You could do so by adding a timestamp or "yes" to the last column of the even row in Google Sheets. Then, before creating a new event, always check in that column to see if an event has already been created. I did a similar check in this video: th-cam.com/video/Rl0bzCcrZcQ/w-d-xo.html
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!
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
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?
Thank you for making this video, it’s been very helpful. I keep running into the issue that when I run the code, it duplicates all of the events. Do you have a solution or an explanation on how I can fix this?
Yes, you would have to expand the code to write either DONE or a timestamp whenever an event is created. You would also have to update the script to first check if the event was already created before it creates events.
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?
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)
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?
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
Your video is amazing! It helped me a lot, thank you. Unfortunately i have a problem, when i add more events in my excel file, script duplicates already existing events :( I tried everything what i could find in internet, but nothing worked. I also watched your other video.
Hi Paula. You could use tickboxes to track which events you already added to Google Calendar. In this video I show how you can use this strategy: th-cam.com/video/Rl0bzCcrZcQ/w-d-xo.html
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
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??
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.
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
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.
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!!!
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: th-cam.com/video/KC7pBjD3GGw/w-d-xo.html
@@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
@@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.
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!
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
@@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.
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
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.
Very awesome job. Question about this video. Can you explain further if I wanted to pick just my name out of a list of names on a sheet? Also, can we pick a specific sheet in a worksheet? If so what would be the coding. Also, what if we create an event but the date changes.... is there code that can change the event date or take away the event if it cancels? Thank you again
You can automate all of the things you mentioned in your comment. But I can't actually "write" it here because you need to know where the data is in the spreadsheet, what objects you have, etc.
@@saperis thank you for the quick response. Maybe if I can find your email I could share a document what I'm talking about. I'm learning about script so I'm not very proficient with it. But I love how you explain very clearly. Nicely done
@@MichaelDaniels Hi Michael, I don't offer any Apps Script or coding services. But check this directory of Apps Script developers you can hire to work on your project: workspacedevs.com/
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.
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.
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)
No, don't know why. Try running the script in debugging mode to see how every step of the script is processed: th-cam.com/video/L4V15ZxIjBQ/w-d-xo.html
You would have to adapt the script quite a bit. Every time you create a new event from a row in Sheets, you could add the date to an extra column. Then the next time the script runs, you first check if there is a date in the last column. If there is no date, you can create the event.
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!
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?
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 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.
@@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.)?
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.
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.
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!
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.
@@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 ;-)
Awesome video... What would be the script if we run the script it would just update the edited events. Currently, when I run the script it always create another schedule
It depends on what exactly you want to update. There are multiple methods you could use on an already existing calendar event like setTitle(), setTime(), etc. Here an overview of the available methods: developers.google.com/apps-script/reference/calendar/calendar-event
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
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.
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.
Happy to hear that Glenn. Please take note of the shift in focus on this channel: th-cam.com/video/OHd_xEabivg/w-d-xo.html
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!
That's a very fun use case for this automation. Have fun sharing your cars. 🚗
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.
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.
This was excellent! Your pacing was perfect. So easy to follow and understand. Thank you so much!
Glad it was helpful! 😃
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.
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!
This is amazing! I used it to add bulk created Zoom meetings to my conference Google Calendar. Clear, simple, thorough. Thank you!!
Thanks for the feedback! It's always nice to know when a video of ours helped. 😀
Kate, were you able to email the event to others through the script?
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.
Glad it helped! 👍
Just subscribed for your clear explanation. Thanks!
Thank you for watching and supporting the channel 👍
Dear
Need to start to say that you have amazing videos about coding in TH-cam. 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
I can confirm that this is possible with Google Apps Script. Maybe consider hiring a freelancer to create this for you: workspacedevs.com/
Thank you so much ❤❤
You're the Queen of Spreadsheets
You're so welcome! Thank you for learning with us on our channel.
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
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: th-cam.com/video/OHd_xEabivg/w-d-xo.html
Excellent lesson video. We studied on this video at our class 💯
Makes me very happy to hear that 😃
This was SO helpful, thank you!
Very comprehensive - thank you so much!
Glad it was helpful!
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.
Glad it was helpful! 😀
@@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 :)
Superb ma'am explanation
We have beginners Can you explain each thing
This is awesome! Thank you so much for breaking this down.
My pleasure! 😀
I really like the way you explain things, well done!
Thank you 😀
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
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
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. :)
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
Did you manage to figure the script out? If so, can you paste here? Really appreciate it!
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!
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.
Awesome Tutorial. Thank you. I have subscribed. This is a wonderful tool to have.
Glad it was helpful!
Thanks! But how do we add guest and description for an event?
Thank you for sharing. It works for me.
Great to hear! 😃
This was super helpful! I appreciate this.
Glad it was helpful! 😀
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.
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)
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.
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: th-cam.com/video/efoX_J4YKfo/w-d-xo.html
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!
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
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?
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.
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.
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.
Hi! I love how simple your codes are! I was trying to work something out for deleting events by adapting your code.
Here is what I tried:
function deleteClasses() {
var sheet = SpreadsheetApp.getActiveSheet();
var calendarId = sheet.getRange("B2").getValue();
var eCCal = CalendarApp.getCalendarById(calendarId);
var classes = sheet.getDataRange().getValues();
classes.splice(0,4);
classes.forEach(function(entry) {
eCCal.deleteEvent(entry[2], entry[0], entry[1]);
});
}
I end up with a "TypeError: eCCal.deleteEvent is not a function". the .deleteEvent prompt doesn't show up as well. when i'm typing it into the script.
Thank you!
After a bit more research I'm guessing I have to somehow get the event titles or get the events in order to match them to my sheet and then attempt to delete.
Yes, if you want to delete an event you need to identify it first. For instance, there's a getEventById method you could use: developers.google.com/apps-script/reference/calendar/calendar#getEventById(String)
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.
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.
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
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.
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?
SpreadsheetApp.getActive.getSheetByName("Sheet or Tab Name");
Go through for tab
@@davidnorman7586 SpreadsheetApp.getActive.getSheetByName("Sheet or Tab Name");
Incredible helpful to get started. THX
Great to hear! 😀
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
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
This is really helpful, thank you so much for creating this, I have 2 follow-up questions if you may assist.
Is it possible to trigger additional actions by adding more columns ie
1. Adding column that will trigger to a guest invitation as this event is created
2. Adding column that will add as information to be included as calendar details such as location & details
Also I ran the execution and thereafter made an edit on the sheet. Good thing is updated entry showed up but it failed to remove the old entry, how should i have that removed?
Here are some tips based on your questions:
- If you want to add more information, like location or guests, when you create a new event check this: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options
- If you want to not only write but also delete events you can use the deleteEvent method: developers.google.com/apps-script/reference/calendar/calendar-event#deleteevent
This is awesome, thank you. A question though, does this script only work with the specific format you have laid out in your example?
Yes. If you want to tweak this script check out the documentation: developers.google.com/apps-script/reference/calendar/calendar
This is really useful. Thanks!
Happy to help. 😀
Thank you :)
thank you!
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?
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
To avoid duplicating events and also to avoid scheduling events on the same date and time i used this inside the forEach loop:
let event = calendar.getEvents(entry[1],entry[2]);
if(event.length === 0){
calendar.createEvent(entry[0], entry[1], entry[2],entry[3]);
}
During my tests the only problem with this approach were for events with start/end time being the same.
You could also work with tickboxes. I used that technique in this video: th-cam.com/video/Rl0bzCcrZcQ/w-d-xo.html
Mine is duplicating the schedules, I tried to use the code inside forEach, but it gave an error! Are there any secrets?
@@thales7783 Without the error message is hard to tell what's not working. One thing to notice is that the indexes used on entry[] are based on my spreadsheet, which is different from the one presented on the video. On my spreadsheet the startTime data is on the second column (entry[1]), while on the exemple is on the first (entry[0]). Besides that i would say to recheck the separators.
Thank you, your code worked for me.
Hey, thanks for the tutorial!
I also have a question: is there a way to repeat the events annually?
For exemple, by setting the birthday of my students, create an annually event that could remind me to congratulate them
Currently, time driven triggers that read your script, can run as frequently as once every minute and as infrequently as once per month. So I see no direct way to send out a message once a year.
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.
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: th-cam.com/video/KC7pBjD3GGw/w-d-xo.html
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
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?
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
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!!
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
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?
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
Amazing class! I have a question, could we add description to the event, using the google script code?
Yes, you can. Check the documentation to see how you have to add further information for the event: developers.google.com/apps-script/reference/calendar/calendar-app#createeventtitle,-starttime,-endtime,-options
function ################() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("########");
var calendarId = "####################";
var calendar = CalendarApp.getCalendarById(calendarId);
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
var lastRow = sheet.getLastRow();
for (var i = 1; i < lastRow; i++) {
if (values[i][5] !== true) { // check if checkbox is not checked
var title = values[i][0];
var description = values[i][1];
var location = values[i][2];
var startTime = new Date(values[i][3]);
var endTime = new Date(values[i][4]);
var event = calendar.createEvent(title, startTime, endTime, {description: description, location: location});
if (event) {
sheet.getRange(i+1, 6).setValue(true); // tick checkbox
}
}
}
}
Excellent video, but one thing still looking for is how to automate the process to when a new user enters an event in sheets, gets update to calendar without repeating the already events, so far it keeps creating a copy of each event.
You can do all of that but you will have to change the script quite a bit.
First, you need to add a check to see if the event has already been created.
Then you need to add an onEdit trigger to catch any changes to the document and have the calendar event creation triggered.
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)?
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.
@@saperis Thank you. What if I want to add more things to the calendar event? Like Description and Attendees
@@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: th-cam.com/video/pqclamPUSpM/w-d-xo.html
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??
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.
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?
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.
@@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?
Many thanks for this tutorial! Works really well. I have 2 questions though about making modifications and adding new events lines in Sheets. When I make a change, I run the script to make the updates in the Google calendar. However this then creates a second calendar event. Is there a way to get the script to only modify existing calendar events or add the new events? And secondly is there a way to create a button to rerun the script from Sheets without opening the Script editor tab? Many thanks in advance!
Of course you can do all you have described. Check the official documentation to find out how to do this. That's what I do when I want to make an automation script.
developers.google.com/apps-script/reference/calendar/calendar-app
Were you able to resolve this issue? I am facing the same problem.
hello Can you help me understand if the ideas I had on my feasible
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
There is currently no way to get that data. None of the APIs give you access to that information.
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 ?
I don't know what went wrong. Maybe a typo in your code?
hello, do you know api connector?
do you use AI for coding?
can I find "templates" ?
Amazing tutorial! Tweaked some codes to implement for my calendar even though I have zero knowledge of JavaScript. I have one question though: How do I set the event color? I have tried everything and setColor() doesn't work. Thank you so much!
setColor() expects you to choose from one of the available colors. I think there are about 13 colors you can choose from. They are numbered through. So you would have to write it like so: event.setColor(1).
I haven't come across a list will all colors though. So just try them.
Great explanaition! can I also add guests to my invite? did not manage to do so, thanks!
Check this video to see how to add guests to an event: th-cam.com/video/pqclamPUSpM/w-d-xo.html
Thanks for the amazing video, i got the result.
But when i added another entry in the sheet and ran the code. It duplicated the previous events and also created the new event from the new entry in the sheet.
2 Questions
1. How do I create a trigger such that the code runs automatically and creates the events on the calendar
2. The code should create events only from the entries made today. And should ignore the previous entries.
I have never coded my entire life, and i could do it in the first attempt.
Love the effort!
You can do all you have mentioned. But you will have to adapt the code to your wishes.
If you don't want to run the code yourself, but instead have it run once a day, you could use a time driven trigger.
Find info about that here: developers.google.com/apps-script/guides/triggers/installable#event-driven_triggers
You could create a conditional to check if an event was already created. If so, skip that row, if not, create the event.
@@saperis Thanks you for taking the time to reply.
But, like i said i can't code. All i can do is copy what you've shown in the video. Is there any video you've created thats explains these. Or if you have any visual material please leave a link. I would love to watch it and replicate.
Thanks again! ;)
@@ukeerthan No, neither do we have such a video nor do we have such a script.
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
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
thanks for your always clear explanations !! What if I want to add a 'description' in the events I want to create ?
Ok I found that I could do that via the 'options'
Great!
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
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
HI. it didn't work for me. Its saying "getDataRange" is not a function
Hey muchas gracias, fue muy explícito
😀
Did it exactly like you and keep getting a "Cannon read properties of null (reading 'createEvent' )
The error message means that some variable is empty or some function does not exists. I guess there must be a typo somewhere in your code.
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)"
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
Hi! Can you teach how to add a custom menu for this specific script? Thank you!! Yours worked when I tried others.
In this video I create a custom menu like the one you want: th-cam.com/video/wVhGwAGHkA4/w-d-xo.html
@@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?
@@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/
This video is really help! Is there a way to pull calendar info from specific range in specific tab?
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
This was so helpful!!! Thank you. I've used this in school to book out computing devices. The sheet is generated from a Google form. I would like for it to automatically update on the calendar without repeating events. Is there a way to do that?
You could change the code so that it only create an event once.
You could do so by adding a timestamp or "yes" to the last column of the even row in Google Sheets.
Then, before creating a new event, always check in that column to see if an event has already been created.
I did a similar check in this video: th-cam.com/video/Rl0bzCcrZcQ/w-d-xo.html
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!
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
@@saperis Does it matter where this line appears in the script or can it go anywhere. Thanks so much for getting back to me :)
Yes, the sequence or order of a script almost always matters. Generally it reads from top to bottom.
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?
Thank you for making this video, it’s been very helpful. I keep running into the issue that when I run the code, it duplicates all of the events. Do you have a solution or an explanation on how I can fix this?
Yes, you would have to expand the code to write either DONE or a timestamp whenever an event is created.
You would also have to update the script to first check if the event was already created before it creates events.
my exact problem, where some times are added or changed. let me know if you figured it out, i'll do the same
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?
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)
Can it be auto updated once I update the sheets? Or I need to click run each time I update the sheets?
You can use the onEdit() trigger to run the script whenever the Sheet is changed: developers.google.com/apps-script/guides/triggers#onedite
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?
You could maybe use the getEvents() method to get all events in a certain time and then delete them.
this was great as usual!...how do i stick in removeAllReminders() so the default reminders dont get created too?
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
Your video is amazing! It helped me a lot, thank you. Unfortunately i have a problem, when i add more events in my excel file, script duplicates already existing events :( I tried everything what i could find in internet, but nothing worked. I also watched your other video.
Hi Paula. You could use tickboxes to track which events you already added to Google Calendar.
In this video I show how you can use this strategy: th-cam.com/video/Rl0bzCcrZcQ/w-d-xo.html
Have two questions How would I add description and color code it to a little category
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
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??
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.
Amazing!
Is there a way to prevent duplicates of events already added to your calendar?
Yes, you would have to change the script to first check if that event was already created.
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
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.
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!!!
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: th-cam.com/video/KC7pBjD3GGw/w-d-xo.html
@@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
@@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.
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!
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
@@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.
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
And thank you if you can reply to this. I really tried more than 5 times. Is it possible to have the codes?
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.
Hello. my Script is working but it still not sync to my calendar, ive tried everything :(
Very awesome job. Question about this video. Can you explain further if I wanted to pick just my name out of a list of names on a sheet? Also, can we pick a specific sheet in a worksheet? If so what would be the coding. Also, what if we create an event but the date changes.... is there code that can change the event date or take away the event if it cancels? Thank you again
You can automate all of the things you mentioned in your comment. But I can't actually "write" it here because you need to know where the data is in the spreadsheet, what objects you have, etc.
@@saperis thank you for the quick response. Maybe if I can find your email I could share a document what I'm talking about. I'm learning about script so I'm not very proficient with it. But I love how you explain very clearly. Nicely done
@@MichaelDaniels Hi Michael, I don't offer any Apps Script or coding services. But check this directory of Apps Script developers you can hire to work on your project: workspacedevs.com/
@@saperis you are so kind and helpful. Thank you
How can I pronounce it in text? :) moreover not sure how to write special symbols here :)
Great video, thank you! I would like to add +/- 600 events, but code works very very slow. Is there any solution?
Well, that's lots of data to process so it makes the script execution slow.
You could try to run the script with less events at a time.
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.
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.
Is there a way to set notifications, and can the available status remain as free instead of defaulting to busy?
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)
It ran and was executed; however, it's not showing up on my calendar. Do you know why?
No, don't know why. Try running the script in debugging mode to see how every step of the script is processed: th-cam.com/video/L4V15ZxIjBQ/w-d-xo.html
How to add it only at the end so that existing data is no longer included in Google Calendar? please help me Mrs.
You would have to adapt the script quite a bit.
Every time you create a new event from a row in Sheets, you could add the date to an extra column.
Then the next time the script runs, you first check if there is a date in the last column. If there is no date, you can create the event.
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!
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?
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.
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/
@@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.
@@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.)?
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.
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.
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!
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.
@@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 ;-)
@@jenniferwilhelm9986 I don't do any Google Apps Script videos anymore. Find out more in this video: th-cam.com/video/OHd_xEabivg/w-d-xo.html
@@saperis Gotcha! Thank you!
Awesome video... What would be the script if we run the script it would just update the edited events. Currently, when I run the script it always create another schedule
It depends on what exactly you want to update. There are multiple methods you could use on an already existing calendar event like setTitle(), setTime(), etc.
Here an overview of the available methods: developers.google.com/apps-script/reference/calendar/calendar-event
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
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.