How to automatically add a schedule from Google Sheets into Calendar

for Friday’s event, I’m trying to make sure our
volunteers know where to go. Is there a way for me to export
schedules from our spreadsheet into our team calendar? JOANNA SMITH: Oh, so
like putting all these shifts into the event
calendar automatically? SPEAKER: Yeah, exactly. JOANNA SMITH: Absolutely. SPEAKER: Oh, awesome. JOANNA SMITH: You
can use Apps Script. Apps Script is a really easy
way to customize your apps with really simple code. SPEAKER: Awesome. So what do we do first? JOANNA SMITH: Give
me one minute. OK, so I went ahead
and set some things up in the spreadsheet to make
it a little easier for us. First, I reformatted
the dates, you can see, to make them easier for
the code to understand. And I just did this by clicking
Format, Number, Date and Time. Trust me, you do not
want to have to learn how dates work in programming. And then second, I went ahead
and wrote some skeleton code for you in the script editor. So if you want to open it up,
it’s Tools, Script Editor. SPEAKER: Cool. So what do I do now? JOANNA SMITH: Well,
any coding task is much simpler if you break
it into smaller tasks first. And you wanted help syncing
a sheet to a calendar. SPEAKER: Yeah. JOANNA SMITH: OK, so
task one, what calendar? SPEAKER: Our team calendar. JOANNA SMITH: We
need to open that. Task two, what
part of the sheet? SPEAKER: The part with
the dates and shifts. JOANNA SMITH: OK, so we
need those cell numbers. Task three, we need to
actually take that information and make an event. And then task four is
bonus, and I did it for you to make it easy for
everyone to use. SPEAKER: Oh, thank you. Cool. So now what? JOANNA SMITH: Now we begin. Task number one,
opening the calendar. It’s really simple. The call is
CalendarApp.getCalendarByID. Now, to get the ID, we’re going
to take it from the public URL. Go ahead and open this,
Calendar Settings, and scroll down to where it is,
because it’s not always the URL in your view. But if you get that and
copy it into the cell I created in the
spreadsheet, our script can find it every time. SPEAKER: So what’s next? JOANNA SMITH: Task
number two, we want to pull each
shift into a form that the computer
can understand. To do that, we’ll make a call
to the spreadsheet, getRange, and that’ll be the cell
range, and then getValues, to turn that into the form
that I created for you. And that’s that matrix. It’s a list of lists. And that way, we can
go through each one. SPEAKER: OK, so
what’s the next step? JOANNA SMITH: Task number three,
where we do the actual work. We have our matrix, and now
every shift in that matrix, we’re going to call with
our Calendar.createEvent, with a name, a start
time, and an end time. And this is the
advantage of Apps Script. It is that simple. SPEAKER: So let’s say I actually
want someone else on my team to add the shifts. JOANNA SMITH: Oh. This is that bonus part that
I actually built for you. I created a custom menu. As easily as you can
click File, Print in Docs, you can now click up
here on our custom menu and run the script with a click. SPEAKER: Oh, that’s
so simple now. JOANNA SMITH: Yeah. If you have any questions
on this pro tip, be sure to leave them in
the YouTube comments below, or check out some
of my other posts. And for more tips, follow
us on Twitter @gsuite. [MUSIC PLAYING]

About the author


  1. thank you very much – this is a nice one… but how can you sync back and forth? I mean: …changing or deleting something in the sheet applies to the calendar… changing or deleting something in the calendar applies that to the sheet… you get the idea? …that would be awesome: a fully integration of a calendar in a google spreadsheet – do you have a solution for that?

  2. thank you very much for the info…

    Two (2) questions:

    1. With this script that you've modeled in the video, is it triggered by a specific event or do you have to manually run it every time you need it?
    2. Does this script take into consideration to automatically update a shift detail if you make any changes?

  3. I wrote a free script that syncs back and forth between sheets and calendar if anybody wants something like that:

  4. better you put this as an option in UI of Calendar or Google Sheets for ease of use. It will avoid coding in Google app scripts 😀

  5. Answer directly. Writing Google documents Experiencing a list of problems not in phone Or else already Personally, like writing documents, is a common matter. We understand this But the truth There are also shared drives. Which designed the standard of other telephone systems that are installed only by themselves Anyone who uses Google documents to perform daily tasks every day.

  6. Is there a link to the coding that she whizzes through? I feel so out of the loop with coding these days I'm a bit shy to ask! I've read the blog post that breaks down the directive for the APP SCRIPT and it's super helpful.

  7. I need to digitalise my case management. So it will have last date case no. Court premises. Court room. Filer. Opposite party. Stage and next date. Can this excel be automated in such manager that once I feed next date it goes auto to calender

  8. I need help to create a button in google sheets which can show a click affect whenever i am clicking it for running an assigned script

  9. Actually is very handful but i want to put all my activity worksheet and some more add ons as email reminders do you have some more deep toturial?

  10. Not 100% (also not a coder). Can this function to create multiple events or do I still need to create an event manually? Clarification: We would like the bookings button on our website (which currently automatically fills a sheet with desired date and time) to automatically create a new event with the same info and contact details added to our Google calendar. If yes I will try to follow along and set this up. If not, how do I do that. Each time a new line is added to our sheet, a new event is created with the details pulled automatically.

  11. is it also possible to do it the other way around? To make a timesheet each week with the calendar as the source of the information?

  12. Is there a way to create 1 event and then add those names & emails and guests in the event?
    Also, if the event changes in the sheet and the script runs, will it move all the old data to the new event, or will it just create a new event leaving the old one?

  13. function scheduleshift2() {
    var Foglio = SpreadsheetApp.getActiveSheet();
    var IdCalendario = Foglio.getRange('A1').getValue();
    var eventCal = CalendarApp.getCalendarById(IdCalendario);
    var signups = Foglio.getRange("A2:C3").getValue();

    for (x=0;x<signups.lenght;x++) {
    var shift = signups[x];
    var volunteer = shift[0]
    var startTime = shift[1]
    var endTime = shift[2]

    function onOpen(){
    var ui = SpreadsheetApp.getUi();
    .addItem( "Sincronizza ora","scheduleshift2")

    Why is nothing created in the calendar? I don't understand where the error is

  14. Hi Joanna and G-Suite team,
    Actually I wanted to use this feature to sync a Project plan in Google sheets to Calendar where I have multiple columns So,Can I add more than 3 columns (as shown in video) ? If yes, can I name them differently than the headers you have given?

  15. I ran the whole thing as specified along with onOpen function. Both of them ran without error but the output didnt get reflected.. Neither the sheet got integrated with Calendar, nor their was a new Menu creation. Can you please help me at this at the earliest?

  16. Great!! How can i create a detailed event (with description, location and any other details)? I've using IFTTT service for this but now doesn't work correctly

  17. Thanks for sharing this very helpful tip! Anyways to have the tasks in google sheets automatically added on google tasks as well? Thanks!

  18. Hi Joanna and G-Suite team

    great tutorial. I can create my events on Calendar, but i Have a problem every time I sync the Sheet with Calendar, using the UI option in the menu, the event in calendar Duplicates.. do yoy have another function that resolves this,?? maybe first delete the content of the calendar and then insert the events like if there were new. ???

  19. i must have the concentration and brain power of a flea …. this has completely baffled me (never used coding by the way and learning is tough)
    could really use some help

  20. Thanks for the great video. The only thing that is missing is a link to the script in its entirety so that your viewers can copy and paste. Is there a reason that you haven't done that, as you promised to do in a comment below, where you said you'd put the script on GitHub? It is very inconvenient to type what's presented in the video when you could easily provide it. Actually you could probably provide the whole script in the description above. Thanks for your video and for your help.

  21. Hey, Joanna first of all awesome video! I am currently however experiencing some trouble. Can the example be downloaded somewhere?

  22. So if my school follows a 7 day cycle instead of a weekly cycle, I should be able to use this as a base and make it easier to add info to my calendars? I hope so at least. This is a great place to start! Thanks!

  23. Trying to modify this script so it can be used for lesson planning. Want to be able to type a module #, unit #, start date, end date, lesson description, lesson target into 6 columns on my spreadsheet then run the script to have it show up on my google calendar. Can someone contact me to help me with this or who could I contact for more direct help?

  24. This is great. Thanks a lot. But I'd like to take it a step further. Is there a way to sync the events created this way? If the events in the spreadsheet change I'd like to update them in Google Calendar accordingly. Also, it'd be good to adapt the script to make sure that the events already in the calendar are not re-added to it. (I'm thinking of synchronising a marketing plan that lives into a spreadsheet with a calendar). Any hints on how to do it?

  25. "you definitely dont want to learn how dates work in programming"

    Dislike! Some of us are in school to learn exactly that and others may already know it. The video shouldn't be about that, but don't assume what we want to learn!

  26. same thing i am using but show Error "Cannot find method createEvent(string,string,string). (line 17, file "oo")" Please resolve it

  27. hello, I saw your youtube that shows the App Scripts update from a spreadsheet to Google calendar. I’ve added this additional function but apparently when publishing “Deploy as web app” vs “Deploy as sheets addon” makes a difference. I’m guessing the sheets addon is what you would indicate as the choice but there are “User Managed Cloud Platform Required” limitations that aren’t explained. So it won’t autoload into the sheet as an addon. I’d loved to find the answer to make it work since this is very useful.

  28. Is it possible to have the event publish to the calendar of the individual that is assigned and not just a "general" calendar? Meaning if I have a task or event and want to create a calendar event or reminder for that individual automatically when the date is entered and the individual selected, is that possible? Also what about creating an email notification of the task.

  29. function delete_events()


    var fromdate = new Date('01/01/2018');

    var todate = new Date('01/01/2020');

    var calendarname = 'SPRINT';

    var calendar = CalendarApp.getCalendarsByName(calendarname)[0];

    var events = calendar.getEvents(fromdate, todate);

    for(var x = 0; x < events.length; x++){

    var ev = events[x]





  30. I've been having problems with this script. It worked fime for a couple of months, But now, It tells me that I have exceeded the number of events created or eliminated in a short time. I don't understand how is that possible if I'm creating or eliminating almost 250 events per month. Please, your help.

  31. TYPE ERROR: I have copied this code exactly and only changed the CalendarId to my calendar. It doesn't matter which Calendar URL (public, ical, etc) I have put in the "C4" cell on the sheet, I get the same error, TypeError: Cannot call method "createEvent" of null. (line 25, file "Code"). What else am I supposed to do here?

  32. Please show me how to automatically import data from google form to calendar. Last year i use third party extension which is free. but, early in this year i can't used that extension anymore because i had to paid..your's video only show for event created using spreadsheet itself. not from spreadsheet that im automatically created using google form. please show me how to do it

  33. Hi Joanna!

    Can I access the user's calendar when he edits a cell by a trigger 'onEdit'?
    I added a menu with a simple script in 'onOpen' to cause an authorization request, but it does not apply to 'onEdit'.


  34. To coordinate volunteers I have them sign up for slots via Google Forms, The form writes to a sheet that runs THIS Code to populate a calendar with the volunteer dates. I have a Trigger set on "Form Submit" so the calendar updates every time someone completes a form.
    However, It seems like this gives up after a while and stops updating after a couple weeks.
    When I go in to debug, I run it to test before I make any changes and it starts right back up no issue.

    Is there some timeout or other issue with the triggers?

  35. Thanks this is very helpful but how can I do this starting from a Google form that serves to make appointments?

Leave a Reply

Your email address will not be published. Required fields are marked *