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.
@@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.
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
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.
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 :)
Dear Need to start to say that you have amazing videos about coding in RUclips. 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: ruclips.net/video/OHd_xEabivg/видео.html
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.
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)?
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
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
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 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.
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.
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?
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)
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?
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
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
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 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.
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)
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)
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.
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});
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
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
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 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.
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.
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
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: ruclips.net/video/Rl0bzCcrZcQ/видео.html
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: ruclips.net/video/KC7pBjD3GGw/видео.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
@@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/
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: ruclips.net/video/pqclamPUSpM/видео.html
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.
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
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
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?
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)
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.
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 ?
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: ruclips.net/video/efoX_J4YKfo/видео.html
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.
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
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
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.
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]);} }
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).
@@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
@@strongwatercampingcabins1116 Maybe the official documentation can better explain how to use the method: developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-startdate,-enddate
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
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 I have to figure out exactly what to omit and how to process that row, I'm an extreme novice here! Proceeding on to your Google App Script Playlist.
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.)?
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
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
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
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: ruclips.net/video/Rl0bzCcrZcQ/видео.html
I found the answer at the end of the video :) I will check deeper on the createEvent method The only thing I got stuck was on the time input, how can I make it human error proof?
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: ruclips.net/video/KC7pBjD3GGw/видео.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.
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.
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 ;-)
Whenever you want to know how to do something with Google Apps Script it's time to check the official documentation. See that you can add further information when creating an event: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options
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/
No, this script only runs when you click the button. You could use an onEdit trigger to achieve this level of automation: ruclips.net/video/KC7pBjD3GGw/видео.html
very informative video! Question: can this also work the other way around? like getting the events data from google calendar and place it automatically in sheets.
Yes, you could write a script that gets the events from a calendar and writes them to a sheet. Check out the documentation: developers.google.com/apps-script/reference/calendar/calendar-app
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: ruclips.net/video/OHd_xEabivg/видео.html
I have spent days and nights to do this, researching blogs, youtube, AI, etc. Finally, you made my day! Thanks a million!!
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. 🚗
This was excellent! Your pacing was perfect. So easy to follow and understand. Thank you so much!
Glad it was helpful! 😃
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.
Just subscribed for your clear explanation. Thanks!
Thank you for watching and supporting the channel 👍
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?
So nice to see such an intelligent woman coding. You’re awesome. You explain it so we’ll. Also SKedule lol.
Thanks for watching! By the way, my new videos are all about learning JavaScript. Just in case you might be interested. 😀
@@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.
Excellent lesson video. We studied on this video at our class 💯
Makes me very happy to hear that 😃
You're the Queen of Spreadsheets
You're so welcome! Thank you for learning with us on our channel.
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
Thank you! God bless you. Subscribed
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! 👍
Very comprehensive - thank you so much!
Glad it was helpful!
This was SO helpful, thank you!
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 :)
Thanks!
You're welcome and thank you for supporting our channel! 😀
I really like the way you explain things, well done!
Thank you 😀
This is awesome! Thank you so much for breaking this down.
My pleasure! 😀
Awesome Tutorial. Thank you. I have subscribed. This is a wonderful tool to have.
Glad it was helpful!
Dear
Need to start to say that you have amazing videos about coding in RUclips. 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 for sharing. It works for me.
Great to hear! 😃
Very informative. Thank you.
You are welcome! 😀
Thank you so much ❤❤
This was super helpful! I appreciate this.
Glad it was helpful! 😀
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: ruclips.net/video/OHd_xEabivg/видео.html
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.
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)?
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
This is really useful. Thanks!
Happy to help. 😀
Incredible helpful to get started. THX
Great to hear! 😀
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!
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 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.
Superb ma'am explanation
We have beginners Can you explain each thing
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.
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?
Thanks! But how do we add guest and description for an event?
Excelent how to!!!
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)
Hey muchas gracias, fue muy explícito
😀
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?
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
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
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?
Brilliant; thanks!
You are welcome 😀
Thank you :)
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.
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!
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: ruclips.net/video/pqclamPUSpM/видео.html
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)
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)
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.
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
}
}
}
}
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
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
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
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 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.
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.
Just brilliant
Thank you for watching 😀
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
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: ruclips.net/video/Rl0bzCcrZcQ/видео.html
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.
thank you!
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: ruclips.net/video/KC7pBjD3GGw/видео.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
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
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: ruclips.net/video/wVhGwAGHkA4/видео.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/
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: ruclips.net/video/pqclamPUSpM/видео.html
Wow that's cool!
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.
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
hello Can you help me understand if the ideas I had on my feasible
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
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?
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)
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.
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 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: ruclips.net/video/efoX_J4YKfo/видео.html
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.
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.
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
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.
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]);}
}
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).
@@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
@@strongwatercampingcabins1116 Maybe the official documentation can better explain how to use the method: developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-startdate,-enddate
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
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 is very helpful! If I only wanted to get the first row and not forEach how would I do that?
the forEach method is to loop over multiple rows. So just omit that part and directly process that one row.
@@saperis I have to figure out exactly what to omit and how to process that row, I'm an extreme novice here!
Proceeding on to your Google App Script Playlist.
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.)?
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
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
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
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: ruclips.net/video/Rl0bzCcrZcQ/видео.html
That is awsome, I got a question.
How can I add Description and Location to the code to enable it on the event creation?.
I found the answer at the end of the video :) I will check deeper on the createEvent method
The only thing I got stuck was on the time input, how can I make it human error proof?
Time in JavaScript are a difficult subject. You will have to do some research and testing to get it right.
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: ruclips.net/video/KC7pBjD3GGw/видео.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.
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.
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: ruclips.net/video/OHd_xEabivg/видео.html
@@saperis Gotcha! Thank you!
Hello, I was wondering how I can add descriptions to the events as well.
Whenever you want to know how to do something with Google Apps Script it's time to check the official documentation.
See that you can add further information when creating an event: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options
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
does it will automatically add new event, if i come latter and add a new row to the table, with new information?
No, this script only runs when you click the button.
You could use an onEdit trigger to achieve this level of automation: ruclips.net/video/KC7pBjD3GGw/видео.html
If I update/add an event to Google sheets, will there be corresponding update on Google Calendar?
No. This script reads your Google Sheets once and then creates calendar events based on that. It doesn't have a live update feature.
Amazing!
Thanks!
very informative video! Question: can this also work the other way around? like getting the events data from google calendar and place it automatically in sheets.
Yes, you could write a script that gets the events from a calendar and writes them to a sheet.
Check out the documentation: developers.google.com/apps-script/reference/calendar/calendar-app