Create Calendar Event from Google Sheets with Google Apps Script
Вставка
- Опубліковано 26 лип 2024
- Learn how to create calendar event from Google Sheets with Google Apps Script.
0:00 Intro
0:09 Start coding
6:44 Date format explanation
12:27 Run code
👉🏽 If you're interested in leveling up your Google Apps Script skill have a look at our online course: https//courses.saperis.io
#CreateCalendarEventFromSheets #GoogleSheets #GoogleAppsScript #AppsScript #GoogleWorkspace #saperis #ChanelGreco
-----
Want to learn how to be more productive with Google Workspace? 🤩
bit.ly/productivity-for-creat...
saperis - Helping you become a Google Workspace Pro!
Sign up to our UA-cam channel for more Google Workspace, Gmail, Google Apps Script, Google Drive, etc. videos: / saperis
saperis website: saperis.io/
saperis Linkedin: / saperis
saperis Twitter: / saperis_io
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: ua-cam.com/video/OHd_xEabivg/v-deo.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. 🚗
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?
This was SO helpful, thank you!
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! 😃
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! 👍
You're the Queen of Spreadsheets
You're so welcome! Thank you for learning with us on our channel.
Very comprehensive - thank you so much!
Glad it was helpful!
Just subscribed for your clear explanation. Thanks!
Thank you for watching and supporting the channel 👍
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 so much ❤❤
This was super helpful! I appreciate this.
Glad it was helpful! 😀
Incredible helpful to get started. THX
Great to hear! 😀
This is really useful. Thanks!
Happy to help. 😀
Awesome Tutorial. Thank you. I have subscribed. This is a wonderful tool to have.
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 :)
Very informative. Thank you.
You are welcome! 😀
Thank you for sharing. It works for me.
Great to hear! 😃
thank you!
Excellent lesson video. We studied on this video at our class 💯
Makes me very happy to hear that 😃
Wow that's cool!
Hey muchas gracias, fue muy explícito
😀
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.
Brilliant; thanks!
You are welcome 😀
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!
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: ua-cam.com/video/OHd_xEabivg/v-deo.html
Dear
Need to start to say that you have amazing videos about coding in UA-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/
Thanks!
You're welcome and thank you for supporting our channel! 😀
Amazing!
Thanks!
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!
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
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.
Just brilliant
Thank you for watching 😀
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)
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
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
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
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 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.
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.
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: ua-cam.com/video/Rl0bzCcrZcQ/v-deo.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
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 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
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: ua-cam.com/video/KC7pBjD3GGw/v-deo.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
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.
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
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?
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
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 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!
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: ua-cam.com/video/Rl0bzCcrZcQ/v-deo.html
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)
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! 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 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.
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 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
}
}
}
}
nice
Thanks
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
Thx
You're welcome 😀
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: ua-cam.com/video/Rl0bzCcrZcQ/v-deo.html
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?
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: ua-cam.com/video/pqclamPUSpM/v-deo.html
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
wow this is so amazinggg !! btw, everytime I run the code, it adds the old "meeting" (let's call it) one more time, is there anyway to fix it? It'd be nice to only use the newly imported data in sheet to create a meeting, not the whole sheet everytime I run the code ^^ Love to hear it soon ^^
The script I created looks at the sheet and adds the event to the calendar. So if you run it repeatedly, you get the same event multiple times in your calendar.
You would have to add a conditional at the beginning of the script to check if this row was already processed. This you could do by adding a time stamp to every row you already processed.
thank you for this very helpful video. I have 3 different sheets (Response-1, Response-2, Response-3) and I want the calendar to be updated from Response-2(inside Response-2 there is Reservesheet-1). How to call this Response-2 in "let bookingSheet = SpreadsheetApp.getActiveSheet();".
If you have multiple sheets you are better off getting a specific sheet by name. This makes sure that you choose the correct one.
Then it's only a matter of getting the data range from that sheet you want to use.
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
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.
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
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.
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 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.
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.
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)
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: ua-cam.com/video/pqclamPUSpM/v-deo.html
Hi Chanel great explanation how to do this. My question is how can i skip empty cell or cells with other information then a date. How can i set autofill to start automatically.👍👍
You would have to change the script quite a bit. You could check to see if a specific cell in a row is empty (e.g. ==! ' ') for example.
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?
Thank you for creating this tutorial. This tutorial and others you have made are super helpful for newbies like me. I'm having an issue which I'm hoping you could help me with. I've run the script you taught in this video but I added an installable trigger "on form submit." I ran into some permissions issues which I was able to quickly fix by updating the scope in the manifest. The code seem to run without error but I've yet to have an event be created in my Google Calendar. Any idea why that would be? Also, my data doesn 't include an end time because all of the event end times will end at the same time. I wanted to input that in a string "4:00:00 PM UTC" could that be the issue? Appreciate any advice you have to offer.
Thanks for watching our videos.
When changing a script to suite your own needs or writing your own script you will have to check the documentation.
See here that the method expects the end time too: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime
Loved the video! Very helpful. The thing I'm trying to figure out now is, when I run the code it will add all of my current events on to the calendar, which is what i want. However, if a new event is added to my sheet, what additional code would I need so that when I run the code it only adds the new event, and doesnt duplicate the ones already in my calendar. Any help would be awesome. Keep up the killer videos! thanks
Thanks for the feedback.
You could do something like this:
- When an event is created from sheets a timestamp is added to the last column of that row.
- Change the script so that before events are created the last column is checked > if it's empty create the event.
So what you're essentially doing is giving the events in the sheet a status: last column empty means it wasn't created as an event in Google Calendar yet.
@@saperis Would you write out the code to check to see if column is empty and how to create time stamp on the sheet? Does each time something is created all items are re-added to calendar?
Sure, you can add a timestamp so that already created events aren't created again.
@@saperis Do I add it to the spreadsheet on the last coulmn?
How do you create time stamp what tab is it under?
Hello Saperis! Thanks for amazing video.
I'l like to show description and location at the same time. But one of them is workin, not both of them. I should add either location or description. Do you know how to add both of them:
schedule.forEach(function(entry){communityCalendar.createEvent(entry[2],entry[0],entry[1],{description:entry[4]},{location:entry[3]}
You are adding two separate objects instead of one. So try this: {description: entry[4], location: entry[3]}
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: ua-cam.com/video/KC7pBjD3GGw/v-deo.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 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.
Thanks! But how do we add guest and description for an event?
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: ua-cam.com/video/Rl0bzCcrZcQ/v-deo.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.
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");
Hi... How would one change it from start and end times to full-day events that show at the top of that date in the calendar and not fill in the entire day as an event?
There's a method for creating all day events: developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-date
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
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: ua-cam.com/video/efoX_J4YKfo/v-deo.html
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
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: ua-cam.com/video/OHd_xEabivg/v-deo.html
@@saperis Gotcha! Thank you!
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.
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.
Cool, can I make a birthday calendar which repeats events every year? Or maybe as addition to this sends happy birthday letters?
Sure, you could do that.
I have a calendar built in google sheets that has about 35 people on it showing their work schedule. With letters showing shifts they will be working. Would this work for me? If a date is changed would it reflect on google calendar?
Well, you would have to adapt the script to your needs. The way it works now is that it create a new calendar event for every row in Sheets.
Enjoyed this tutorial a lot and was able to apply it to a project that I'm working on. I'm working on a solution to add data to the google sheet using a sidebar html form;, which I then dump into my calendar. The sidebar form includes two date/time fields using html form type="datetime-local".
Unfortunately, my date/time result (ex. 2021-06-30T08:00) separates my date and time with a "T" which causes my code to fail as it won't accept the "T" as part of the parameter. Any suggestions?
Is it possible to send the date and time to the calendar as separate data items?
Hi John. Maybe you could create a new date based on the datetime-local HTML input. Something like this might help: const date = new Date("2016-07-27T07:45:00Z");
Read the details on how to handle date and time in this blog post: www.toptal.com/software/definitive-guide-to-datetime-manipulation
Unless you are creating an all day event, you need to send add at lest the date, start, endtime of the event to create a new event.
These are all separate parameters that are required at the same time for creating a new event.