Create Calendar Event from Google Sheets with Google Apps Script 

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

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?

Happy to hear that Glenn. Please take note of the shift in focus on this channel: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-OHd_xEabivg.html

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.

Glad it was helpful! 😃

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

Glad it was helpful!

My pleasure! 😀

Glad it helped! 👍

Thank you 😀

Thank you for watching and supporting the channel 👍

Glad it was helpful! 😀

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 :)

Glad it was helpful!

Great to hear! 😀

Happy to help. 😀

Makes me very happy to hear that 😃

You are welcome! 😀

Great to hear! 😃

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.

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-OHd_xEabivg.html

You are welcome 😀

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 can confirm that this is possible with Google Apps Script. Maybe consider hiring a freelancer to create this for you: workspacedevs.com/

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

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

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.

Yes. If you want to tweak this script check out the documentation: developers.google.com/apps-script/reference/calendar/calendar

Thank you for watching 😀

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

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

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

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)

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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

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

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 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

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.

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Rl0bzCcrZcQ.html

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.

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

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Rl0bzCcrZcQ.html

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.

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

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

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?

You're welcome 😀

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 } } } }

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)

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.

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.

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

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.

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");

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

Great!

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pqclamPUSpM.html

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

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.

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

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-Rl0bzCcrZcQ.html

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?

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

Thanks

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?

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

You are adding two separate objects instead of one. So try this: {description: entry[4], location: entry[3]}

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.

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

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

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.

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

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

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

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.

Check this video to see how to add guests to an event: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-pqclamPUSpM.html

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

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

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

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)

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.

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.

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

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.

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?

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

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

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.

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

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-efoX_J4YKfo.html

None of the methods to create a Google Calendar event give you the option of adding an attachment.

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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-OHd_xEabivg.html

@@saperis Gotcha! Thank you!