Hi Joanna! This is AMAZING info, but I'm in way over my head. I have ZERO coding experience, so trying to make your shift calendar work for my needs is... well, I don't know what the hell I'm doing! Please help?!? Goal: I want to use a spreadsheet to set reminders in my calendar based off of the dates customers purchase specific products. I'd like to reminder to read "Customer, Item, Quantity, Expiration Date". Any guidance?
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.
Amazing tutorial tyvm! :) But i am wondering if i suddenly decide to delete the schedule or update it, will it also be updated in the calendar or will it just create another calendar?
How do you manage this if you don't want each run to create a duplicate event? If I run this 2 times, it creates the same event twice. The video was excellent, thank you
I followed the video, triple checked functions & variables, and ran the script without the Script Editor returning any errors, but my Google Sheet and script seem fundamentally disconnected. The new menu option in the Google Sheet has not appeared and no events have been created in the relevant calendar. Is there an obvious basic step I'm missing outside the scope of this video?
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?
Hi, thanks for the video! I'd like to have the (sheets and g-calendar) automated so anytime something is updated on the sheets, it's also updated on the calendar and vice versa. I see it can be customized but is there a way to do it automatically? I'm also curious if I have the locations set with a specified time in different timezones, how can I make that show up on my sheets/caldendar?
I see a lot more code in the video than I do in the 'Step-by-step guide'. Where can I get a copy of the code to inspect and modify? Where is this 'skeleton code'?
I was just wondering. Would it be possible to actually create a calendar event by submitting a google form? Without the possible or showing conflicting events on the same calendar? Google form to calander event? For elderly, and children ,is it possible? Thanks and blessed 2024
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? Thanks!
Hi Thanks for the Video. I wrote the code to pull data from the spreadsheet and push into my calendar (one 1 week) with google app script. Its work well. It has created Events. But Instead of Events I want to create Tasks. Is there any method availabe for this ?
Also, I have a Sheet one where I have a master schedule where I pull data. is there a way for me to NOT duplicate a calendar event?? I want to run the script but it duplicates as things get added from sheet one. Thank you
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?
HELP REQUEST: I used a google sheet to schedule my day, and I'd like this to be put in to my calendar. Now, if I use this script today, it will update my calendar today. But tomorrow when I use a different range within the sheet to update my plans for tomorrow, will today's stuff be deleted? My google sheet has 5 days for scheduling tasks, but I schedule each day in the morning. The days don't have a date field. Just start time, finish time, task name. At the end of the week, my google sheet is full. The week after that, I delete and start the next week. The question is, will my historical tasks/events be deleted in google calendar?
Trying to have a budget/bills to pay sheet merged with a bill calendar? Do I need to do this or is there a built in merge within google workspace which would seem logical.
Hi Joanna & G-Suite Team, Question about the shift events. Right now we can make the events last from a certain timeframe, such as 8am-1pm, how can I make them an 'all day' event instead? For instance, this person works all day from 11/10/22 through 11/30/22 And lastly, is there a way to have the google sheet completely sync with the Calendar? Meaning if I change the event in the calendar, can that update the google sheet & vice versa?
Is it possible to do the same process in reverse? I want to extract a calendar event/roster but don't want to export the entire calendar. Then use this data in sheets to import it into a doc or PDF
? Has this been deprecated ? Getting This: TypeError: Cannot read property 'createEvent' of null (line 16, file "Code") Can this how-to be removed if it no longer works. I'm not going to get that 2 hours back.
Thanks for putting this together. What if I wanted the process to flow in the opposite direction? For example, when an event is added to a GCalendar, details from that event are added to a new row in a GSheet?
It’s a bug that app script throws for dates (String,string, string) don’t match the method signature for utilities date .. but the script works fine. Can you plz check and rectify
Hi Joanna, thank you so much for this video! I was wondering if its possible to send event on google calender to each person that his name is on the cell of the even.For example, If for example on Monday Jason is working from 8-5 at a certain location , would it be possible that once I'm typing his name it will automatically send to his google calendar invitation to event ?. I appreciate any guidance you can provide. Thank you so much . Happy new year
@@rezjiwa3702 Sure, check the CalendarApp documentation, you can get all event on a range of time and other stuff related developers.google.com/apps-script/reference/calendar/calendar.html
Is it possible to sync multiple calendars to one spread sheet? Looking to have certain event dates send to the individual's email who is working that event. Currently have all the events on one spread sheet.
i got this error The parameters (String,String,(class)) don't match the method signature for CalendarApp.Calendar.createEvent. (line 13, file "code") this is my code function myFunction() { var spreadsheet = SpreadsheetApp.getActiveSheet(); var calendarId = spreadsheet.getRange("U1").getValue(); var eventCal = CalendarApp.getCalendarById(calendarId); var signups = spreadsheet.getRange("A2:C500").getValues(); for (x=0; x
@DOR MOSHE - Hi , was wondering if you solved this issue? I am running into the same error message for the following line and wanted some insight on how to solve it. eventCal.createEvent(name, startTime, endTime)
New to anything Google, besides web browsing. Can you "link" a record in google sheets to calendar, so if either calendar or the sheet is updated it will update the other automatically?
This is great! Now what if I have a description that I want to include (for example, if I have volunteers and they're all going to different locations or something)? How would I add that bit as well?
Hi Jessica, here is what you are missing: eventCal.createEvent(dataArray[i][0],dataArray[i][1],dataArray[i][2],{ description: dataArray[i][4] + ' ' + dataArray[i][5] + ' '}).setColor(CalendarApp.EventColor.YELLOW); The first value is the name, the second is start time, third is end time and THEN----> {description: "describe your event or use a variable to pull from the sheet"+ return + "this is the HTML link to the event"} make the calendar event yellow. - does make sense?
Awesome stuff. I would like to export my google calendar into google sheets, as I have to time keep how many hours I've put in either per week/month/year. Could you provide?
I wonder if there is any (easy) way to generate an ICS file (for use with outlook calender) from a google docs spreadsheet ? (because I do not use google calender)
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?
1. This is manually triggered by clicking the custom menu. Setting up a trigger would be just as simple--for example, to run everytime the Sheet is edited. (developers.google.com/apps-script/guides/triggers/) 2. Nope, this script does not automatically update, but setting up an onEdit() trigger would do so! Note that you'd want to add support for tracking the event ID in order to make changes. Should I write up an example of this?
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. ???
how can i set the date as dd-mm-yyyy its not working when i change it ( can i have the time set to a default time"08:00" so i don't need to enter it ?- and what is the code to not duplicate events if all ready there ?
I have successfully repurposed your script to export our work schedule into a shared calendar. BIG THANKS! …but it times out when I have too many lines of script in the for loop. Is there a way to batch the eventCal.createEvent outside the for loop? I have an array containing just the staff members name, start date/tie and end date/time. Thanks in advance.
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.
Thanks! This is great. I'm trying to add the sync to calendar and it shows up in UI but when I click it gives me a message Script function not found: scheduleShifts. Am I missing something?
I'm getting an error: "Exception: The parameters (String,number[],number[]) don't match the method signature for CalendarApp.Calendar.createEvent." 👉 I have changed the date information to "Date time" 👉 When I console.log the data I get an 'object'. This seems to be a data type error) 👉 The signature method a String, Date, Date which I have. Here is my for loop: for (x=0; x
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?
Namaskaram How can I start with managing my calendar in GSuite..... what's the simple way... can you please guide me.... Also, can I send email directly from my excel sheet... say a welcome email or something
Hi Joanna! Thank you so much for this. Could you please help me arranging the code to just make an all day event from a date in my google spread sheet?
Is there a way to only send newly added data to the calendar? Right now when I run the script to add new data - the entire thing is added again; putting items on the calendar multiple times.
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?
This is exactly what I want I think. Where is the code/script??? Can I just add 3 columns? Like Title column , Date column Time column, Then Description column.
Hi Michael, here is what you are missing: eventCal.createEvent(dataArray[i][0],dataArray[i][1],dataArray[i][2],{ description: dataArray[i][4] + ' ' + dataArray[i][5] + ' '}).setColor(CalendarApp.EventColor.YELLOW); The first value is the name, the second is start time, third is end time and THEN----> {description: "describe your event or use a variable to pull from the sheet"+ return + "this is the HTML link to the event"} make the calendar event yellow. - does make sense?
Hi there, thanks for this tuto, great one! So, I actually followed the step quite thoroughly but was not able to get to the end goal I had for my idea. Is there a way I could share you my spreadsheet so you can have a look and let me know what I missed/did incorrectly please? The doc is a retroplanning made through google sheet and I'd like to extract key events (all day events) to have them in my calendar. Thank you in advance for you support!!
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'. Thanks.
What about the opposite?... I have a Calendar which is filled by people on the field, and back at the office I need to IMPORT that data from each event to a spreadsheet ... Is there a way to do that?
Thank you for this! Thinking through our scenario - we have a few dozen people assigning themselves to shifts on the google sheet, who would really only be interested in adding their own shifts on their own google calendars. Any suggestions for this?
I actually have no idea where that signups matrix is being pulled from. I read the doc on loops and only got "our data is inside this matrix" ??? So everytime I'm running this code, its spitting out an error notification saying "signups is not defined"
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?
You can try messing with push notifications in the Calendar API. But you may have to go beyond using Apps Script to pull off a fully integrated two-way sync.
Yeah! This is definitely possible. It's a natural extension, but is a bit too in-depth for the pro-tip format above. It seems like there's some interest, so I'll play with getting a sample drafted this week, and comment here again when I do!
@@morriec9868 Clicking Show More dhows me a few links, including one to the blog post, but no code. Also, the blog post doesn't appear to have all the code in it - just a few scattered lines. Are you saying the code itself appears in Show More for you?
@@Rain_theGamer You're replying to the wrong person. I was just answering other persons in this sub thread requests for the code that was shown in the video, but not provided in c&p form. I don't disagree with their reason for asking it however (that you shouldn't expect everyone to learn the language just to do the task (or similar enough) to what is in the how to above). If people want to learn (as I would if I had the need to code in Gcode) there is documentation to do so.
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?
Did you ever figure out how to do this? I'm trying to do the same thing with my marketing calendar. I get duplicate events every time I run the script. Its driving me crazy!
So, the most important thing to update an existing event is to track the event ID. I recommend (1) adding a column to your spreadsheet for the ID. (You can "hide" it from the UI so others don't see it/delete it by accident.) Then, (2) when you call createEvent(), store the event that comes back. i.e.: var event = eventCal.createEvent(). And then (3) you can use event.getId() to find the id of the specific calendar event. (4) Write that ID to your spreadsheet. (5) Before creating a new event, you'll want to see if an event ID already exists in your spreadsheet. If not, create the new event and save the ID. If it does, retrieve the existing event, and update individual values as needed. developers.google.com/apps-script/reference/calendar/calendar-event#methods
@@JoannaGSmith Thanks so much for your advice Joanna. I took what you said and came up with this: function scheduleDeliveries() { /** Open the Delivery Calendar **/ var spreadsheet = SpreadsheetApp.getActiveSheet(); var calendarId = spreadsheet.getRange("B1").getValue(); var eventCal = CalendarApp.getCalendarById(calendarId); /** Pull in each delivery date/time and existing Event ID **/ var deliveries = spreadsheet.getRange("B3:D25").getValues(); var eventIds = spreadsheet.getRange("Q3:Q25").getValues(); /** Do the work **/ for (x = 0, y = 0, z = 3; x < deliveries.length; x++, y++, z++) { var delivery = deliveries[x]; var oldEvent = eventIds[y]; var eventId = 'Q' + z; var startTime = delivery[0]; var endTime = delivery[0]; var customer = delivery[1]; var location = delivery[2]; var existEvent = eventCal.getEventById(oldEvent) if ((!startTime) || (!endTime)) break; if (!existEvent) { // Creates new events from new entries and writes new event to sheet var event = eventCal.createEvent(customer, startTime, endTime,{location: location}).getId(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(eventId); cell.setValue(event); } else { // Updates existing events if changes are found var existEventStartTime = existEvent.getStartTime(); var existEventEndTime = existEvent.getEndTime(); var existEventTitle = existEvent.getTitle(); var existEventLocation = existEvent.getLocation(); if (existEventTitle != customer) { existEvent.setTitle(customer) } if (existEventLocation != location) { existEvent.setLocation(location) } if ((existEventStartTime != startTime) || (existEventEndTime != endTime)) { existEvent.setTime(startTime, endTime) } } } } This seems to work for me and events aren't getting duplicated anymore if there is already an eventId attached to it from the spreadsheet. If there is not one, it makes an event for it and populates the column. I also realized that I don't need a different startTime and endTime, so I modified the code to use the same value for both. It would have taken me a lot longer to figure out if I didn't have your hints. Thanks so much!
@@xkaliba Hi!, Thank you for posting your code! it inspired me how to modify mine. But did you solve the issue with new entries that are outside the range set to obtain values? (i.e: var deliveries = spreadsheet.getRange("B3:D25").getValues()). I've been having the issue that if i put data in B26, the code does not process correctly, but if I modify the range in the script, it works perfectly, but user should not have to modify the script in order to function.
@@batero06 I think I saw one solution to this - if (customer == ''){continue;} this should skip the entry if there is no value for 'customer' in this row
@@arkanthiel Thank you for your answer! while I did a test before, seems it didn't follow my calendar's timezone. Anyway, I specified the timezone inside the script finally.
Do you have multiple sheets in your Google Sheet? I had to specify which sheet first, then the cell. Ex: var calendarId = spreadsheet.getRange("'Database'!L1").getValue();
Hi Joanna, thank you so much for this video! I was playing around aiming to customize this, and I was wondering if there's any articles/additional informations regarding how to create multiple event types, or more specifically how to split the cell ranges up? For example, I was trying to customize this into a start and end dates instead of times, and instead I populated the event successfully, but they spanned from the start date all the way until the end date. I appreciate any guidance you can provide. Cheers!
I tried to reply to this earlier, but think it was deleted because of links. Going to try again (4th try...i think comments hates me :) ***** I was playing with customizing what I saw here as well and found what I think you are looking for. This page [Calendar Service | Apps Script | Google Developers] may have the info you need. I found 'createAllDayEvent' options especially helpful, thouugh you may be looking for one of the other listings If I misunderstood and you are saying you need to break the date and time into different fields then combine on processing, then you may also need this page [Working with Dates and Times | Google Ads Scripts]. It helped me combine date/times extracted from other info into a useable date for a secondary entry. I am a newbie too, but hope this helps.
Yes, I did skip over it. But I wrote a follow-up post specifically for this, and it's linked in the video description. Check it out: medium.com/@dontmesswithjo/coding-basics-for-loops-90dca2873c70
I've noticed, on my test calendar, that the items on the calendar duplicate every time the task is run. Is there a way to check to see if the calendar event exists and if so, skip it?
Hey Shane, she gave some tips under another comment about what to do so I came up with this: function scheduleDeliveries() { /** Open the Delivery Calendar **/ var spreadsheet = SpreadsheetApp.getActiveSheet(); var calendarId = spreadsheet.getRange("B1").getValue(); var eventCal = CalendarApp.getCalendarById(calendarId); /** Pull in each delivery date/time and existing Event ID **/ var deliveries = spreadsheet.getRange("B3:D25").getValues(); var eventIds = spreadsheet.getRange("Q3:Q25").getValues(); /** Do the work **/ for (x = 0, y = 0, z = 3; x < deliveries.length; x++, y++, z++) { var delivery = deliveries[x]; var oldEvent = eventIds[y]; var eventId = 'Q' + z; var startTime = delivery[0]; var endTime = delivery[0]; var customer = delivery[1]; var location = delivery[2]; var existEvent = eventCal.getEventById(oldEvent) if ((!startTime) || (!endTime)) break; if (!existEvent) { // Creates new events from new entries and writes new event to sheet var event = eventCal.createEvent(customer, startTime, endTime,{location: location}).getId(); var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheets()[0]; var cell = sheet.getRange(eventId); cell.setValue(event); } else { // Updates existing events if changes are found var existEventStartTime = existEvent.getStartTime(); var existEventEndTime = existEvent.getEndTime(); var existEventTitle = existEvent.getTitle(); var existEventLocation = existEvent.getLocation(); if (existEventTitle != customer) { existEvent.setTitle(customer) } if (existEventLocation != location) { existEvent.setLocation(location) } if ((existEventStartTime != startTime) || (existEventEndTime != endTime)) { existEvent.setTime(startTime, endTime) } } } } This works for me since it only makes a new event if it doesn't see an eventId for it on the sheet. If it sees an eventId on the sheet it checks for changes and then sets them. The startTime and endTime can be set to the same which is why I'm using delivery[0] for both values. I haven't added the ability to delete an event if it doesn't find the eventId in the sheet anymore so that's it's a full sync but this has been working for all my needs so far. Hope this helps!
I love the Google sheet and calendar integration. I want to create an add to calendar link to my spreadsheet. For example I have a list of live streams with date and times. I want to have in the last column a clickable link to add all the data to the right in the users calendar. Is this even possible? Do you have some script that would help with this?
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
You'll want to add "options" to your createEvent() call. See the documentation here: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options
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!
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.
I got this error message too. I worked around it by calling `Utilities.sleep(3000)` every 10 iterations of the loop. You can adjust as necessary-the point is you want to slow down the creation of new events.
Try Google Workspace For Free Now → goo.gle/3pONCUN
This is cool, but where do I get the skeleton code from? That seems like the missing bit.
Just loved the part where she made it easy for every one to share... And shared the spreadsheet and script with us
dude I have 8 miinutes please tell me if youre being serious and if so please tell me where i can find the spreadsheet
Why didn't you put the script and the google sheet in the description to download ?
Drive traffic to the website
They want you to work
It would be more simple if you guys have an add on to synch the forms with the calendar, instead of code for every event.
Hi Joanna!
This is AMAZING info, but I'm in way over my head. I have ZERO coding experience, so trying to make your shift calendar work for my needs is... well, I don't know what the hell I'm doing! Please help?!?
Goal:
I want to use a spreadsheet to set reminders in my calendar based off of the dates customers purchase specific products. I'd like to reminder to read "Customer, Item, Quantity, Expiration Date".
Any guidance?
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.
Amazing tutorial tyvm! :)
But i am wondering if i suddenly decide to delete the schedule or update it, will it also be updated in the calendar or will it just create another calendar?
Any Luck on this?
It duplicates the old event and create new event.
How do you manage this if you don't want each run to create a duplicate event? If I run this 2 times, it creates the same event twice. The video was excellent, thank you
I followed the video, triple checked functions & variables, and ran the script without the Script Editor returning any errors, but my Google Sheet and script seem fundamentally disconnected. The new menu option in the Google Sheet has not appeared and no events have been created in the relevant calendar. Is there an obvious basic step I'm missing outside the scope of this video?
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?
Hi, thanks for the video! I'd like to have the (sheets and g-calendar) automated so anytime something is updated on the sheets, it's also updated on the calendar and vice versa. I see it can be customized but is there a way to do it automatically? I'm also curious if I have the locations set with a specified time in different timezones, how can I make that show up on my sheets/caldendar?
I feel like im watching a tv show. I am a music teacher and I have a full schedule from 8-6pm most days so this really helped lessen the work
So this is great. Unfortunately, when I run the script, it doesn’t actually populate the calendar with events. I’m not sure what I’m missing.
I see a lot more code in the video than I do in the 'Step-by-step guide'. Where can I get a copy of the code to inspect and modify? Where is this 'skeleton code'?
I was just wondering. Would it be possible to actually create a calendar event by submitting a google form? Without the possible or showing conflicting events on the same calendar? Google form to calander event? For elderly, and children ,is it possible? Thanks and blessed 2024
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?
Thanks!
Hi Thanks for the Video. I wrote the code to pull data from the spreadsheet and push into my calendar (one 1 week) with google app script. Its work well. It has created Events. But Instead of Events I want to create Tasks. Is there any method availabe for this ?
Also, I have a Sheet one where I have a master schedule where I pull data. is there a way for me to NOT duplicate a calendar event?? I want to run the script but it duplicates as things get added from sheet one. Thank you
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?
Hello, do you remember how you made the trigger you set on "Form Submit" so the calendar updates every time someone completes a form?
HELP REQUEST: I used a google sheet to schedule my day, and I'd like this to be put in to my calendar. Now, if I use this script today, it will update my calendar today. But tomorrow when I use a different range within the sheet to update my plans for tomorrow, will today's stuff be deleted?
My google sheet has 5 days for scheduling tasks, but I schedule each day in the morning. The days don't have a date field. Just start time, finish time, task name.
At the end of the week, my google sheet is full. The week after that, I delete and start the next week.
The question is, will my historical tasks/events be deleted in google calendar?
Trying to have a budget/bills to pay sheet merged with a bill calendar? Do I need to do this or is there a built in merge within google workspace which would seem logical.
Hi Joanna & G-Suite Team,
Question about the shift events. Right now we can make the events last from a certain timeframe, such as 8am-1pm, how can I make them an 'all day' event instead? For instance, this person works all day from 11/10/22 through 11/30/22
And lastly, is there a way to have the google sheet completely sync with the Calendar? Meaning if I change the event in the calendar, can that update the google sheet & vice versa?
Is it possible to do the same process in reverse? I want to extract a calendar event/roster but don't want to export the entire calendar. Then use this data in sheets to import it into a doc or PDF
? Has this been deprecated ? Getting This: TypeError: Cannot read property 'createEvent' of null (line 16, file "Code") Can this how-to be removed if it no longer works. I'm not going to get that 2 hours back.
i received the same error. have you figured it out yet?
Thanks for putting this together. What if I wanted the process to flow in the opposite direction? For example, when an event is added to a GCalendar, details from that event are added to a new row in a GSheet?
That's exactly what I need to do. Did you manage to do it?
Hi! I am getting this error - TypeError: Cannot read property 'createEvent' of null
Would it be possible to run it the opposite way? Say I wanted to have events from an existing calendar show up on the spreadsheet?
Hi Joanna & G-Suite Team,
Need to schedule to run a macro at a particular time of the day?
Any help would be useful
it works fine but every time i use the script it puts a new entry in the calender it does not sync. Is there a code that see duplicate entrys ?
this is super relevant. If the code could either remove old ones or change them it would be much more useful
Can we add information to another person's calendar (to whom we have shared file)?
Mind-blown! Totally doing this for our nonprofit volunteers! Thanks so much!
Awesome! Is it also possible to delete calendar events via this script?
Great guide! I'm wondering how I can add reminders to my events from Google Sheets, please
It’s a bug that app script throws for dates
(String,string, string) don’t match the method signature for utilities date .. but the script works fine.
Can you plz check and rectify
Hi Joanna, thank you so much for this video! I was wondering if its possible to send event on google calender to each person that his name is on the cell of the even.For example, If for example on Monday Jason is working from 8-5 at a certain location , would it be possible that once I'm typing his name it will automatically send to his google calendar invitation to event ?. I appreciate any guidance you can provide. Thank you so much . Happy new year
Is there a way I can reverse this process, i.e. populate a sheet with particular kinds of events in calendar?
I second this
has anyone found the reverse....Calendar to sheets??
@@rezjiwa3702 Sure, check the CalendarApp documentation, you can get all event on a range of time and other stuff related developers.google.com/apps-script/reference/calendar/calendar.html
@@CamiloNovoaT is there a way to export gcal into gsheets as a log counted entry? e.g. if i visit the gym 3 times a week and want to track ?
@@CamiloNovoaT is there a way to export gcal into gsheets as a log counted entry? e.g. if i visit the gym 3 times a week and want to track
Is it possible to sync multiple calendars to one spread sheet? Looking to have certain event dates send to the individual's email who is working that event. Currently have all the events on one spread sheet.
Is the skeleton code shared anywhere? I can't find it on here or the blog post...
i got this error The parameters (String,String,(class)) don't match the method signature for CalendarApp.Calendar.createEvent. (line 13, file "code")
this is my code
function myFunction() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
var calendarId = spreadsheet.getRange("U1").getValue();
var eventCal = CalendarApp.getCalendarById(calendarId);
var signups = spreadsheet.getRange("A2:C500").getValues();
for (x=0; x
@DOR MOSHE - Hi , was wondering if you solved this issue? I am running into the same error message for the following line and wanted some insight on how to solve it.
eventCal.createEvent(name, startTime, endTime)
New to anything Google, besides web browsing. Can you "link" a record in google sheets to calendar, so if either calendar or the sheet is updated it will update the other automatically?
This is great! Now what if I have a description that I want to include (for example, if I have volunteers and they're all going to different locations or something)? How would I add that bit as well?
Hi Jessica, here is what you are missing:
eventCal.createEvent(dataArray[i][0],dataArray[i][1],dataArray[i][2],{
description: dataArray[i][4] + '
' + dataArray[i][5] + '
'}).setColor(CalendarApp.EventColor.YELLOW);
The first value is the name, the second is start time, third is end time and THEN----> {description: "describe your event or use a variable to pull from the sheet"+ return + "this is the HTML link to the event"} make the calendar event yellow. - does make sense?
Where can we find the complete script at?
Awesome stuff. I would like to export my google calendar into google sheets, as I have to time keep how many hours I've put in either per week/month/year. Could you provide?
GREAT Joanna, but i don't know why it doesent work, sintax error in the last point, cuold you help me?
I wonder if there is any (easy) way to generate an ICS file (for use with outlook calender) from a google docs spreadsheet ? (because I do not use google calender)
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?
1. This is manually triggered by clicking the custom menu. Setting up a trigger would be just as simple--for example, to run everytime the Sheet is edited. (developers.google.com/apps-script/guides/triggers/)
2. Nope, this script does not automatically update, but setting up an onEdit() trigger would do so! Note that you'd want to add support for tracking the event ID in order to make changes. Should I write up an example of this?
@@JoannaGSmith yes please
@@JoannaGSmith Yes please
@@JoannaGSmith Yes please
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. ???
how can i set the date as dd-mm-yyyy its not working when i change it ( can i have the time set to a default time"08:00" so i don't need to enter it ?- and what is the code to not duplicate events if all ready there ?
I have successfully repurposed your script to export our work schedule into a shared calendar. BIG THANKS! …but it times out when I have too many lines of script in the for loop. Is there a way to batch the eventCal.createEvent outside the for loop? I have an array containing just the staff members name, start date/tie and end date/time.
Thanks in advance.
Does this code keep importing new signups into the calendar, as and when they are entered in Google Sheets?
Can we pull a cell data as an email address and then add it to the invite block of an event?
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.
incredible. Any way to make sure that there are no double entries in the calendar e.g. if the script gets fired 2 times with the same dates & times
Hi Joanna
Can we include an incremented serial number in the Subject line of each incoming email. How can we achieve that please. ?
Thanks! This is great. I'm trying to add the sync to calendar and it shows up in UI but when I click it gives me a message Script function not found: scheduleShifts. Am I missing something?
I'm getting an error: "Exception: The parameters (String,number[],number[]) don't match the method signature for CalendarApp.Calendar.createEvent."
👉 I have changed the date information to "Date time"
👉 When I console.log the data I get an 'object'. This seems to be a data type error)
👉 The signature method a String, Date, Date which I have.
Here is my for loop:
for (x=0; x
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?
Namaskaram
How can I start with managing my calendar in GSuite..... what's the simple way... can you please guide me....
Also, can I send email directly from my excel sheet... say a welcome email or something
Hi Joanna!
Thank you so much for this. Could you please help me arranging the code to just make an all day event from a date in my google spread sheet?
@@jamielaing479 did you really just make an account to say this bad joke
Is there a way to only send newly added data to the calendar? Right now when I run the script to add new data - the entire thing is added again; putting items on the calendar multiple times.
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?
Oh, and I did try this with another calendar that isn't part of a business g suite, and the script ran but did not populate the calendar.
I see all these questions but none have been answered. =(
It's good to know that even Google drops the ball :)
Oops! I don't always remember to scroll through my UA-cam comments.
eventCal.createEvent(volunteer,startTime,endTime) I keep getting an error message, invalid argument: title. Any ideas why ??
This is exactly what I want I think. Where is the code/script??? Can I just add 3 columns? Like Title column , Date column Time column, Then Description column.
Hi Michael, here is what you are missing:
eventCal.createEvent(dataArray[i][0],dataArray[i][1],dataArray[i][2],{
description: dataArray[i][4] + '
' + dataArray[i][5] + '
'}).setColor(CalendarApp.EventColor.YELLOW);
The first value is the name, the second is start time, third is end time and THEN----> {description: "describe your event or use a variable to pull from the sheet"+ return + "this is the HTML link to the event"} make the calendar event yellow. - does make sense?
Hi there, thanks for this tuto, great one! So, I actually followed the step quite thoroughly but was not able to get to the end goal I had for my idea. Is there a way I could share you my spreadsheet so you can have a look and let me know what I missed/did incorrectly please? The doc is a retroplanning made through google sheet and I'd like to extract key events (all day events) to have them in my calendar. Thank you in advance for you support!!
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'.
Thanks.
Please is it possible to schedule tasks rather than events?
This format of video makes it so easy to understand. Thank you so much!
What about the opposite?... I have a Calendar which is filled by people on the field, and back at the office I need to IMPORT that data from each event to a spreadsheet ... Is there a way to do that?
Thank you for this! Thinking through our scenario - we have a few dozen people assigning themselves to shifts on the google sheet, who would really only be interested in adding their own shifts on their own google calendars. Any suggestions for this?
Can you do this in reverse? Can you import events data from a calendar into a google sheet automatically?
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?
I second this
Is there a way can this be done for google tasks for other users??
I actually have no idea where that signups matrix is being pulled from. I read the doc on loops and only got "our data is inside this matrix"
??? So everytime I'm running this code, its spitting out an error notification saying "signups is not defined"
is there a way to export gcal into gsheets as a log counted entry? e.g. if i visit the gym 3 times a week and want to track
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?
That's a great question. I'd love to hear if there's a way to do that
You can try messing with push notifications in the Calendar API. But you may have to go beyond using Apps Script to pull off a fully integrated two-way sync.
Yeah! This is definitely possible. It's a natural extension, but is a bit too in-depth for the pro-tip format above. It seems like there's some interest, so I'll play with getting a sample drafted this week, and comment here again when I do!
@@JoannaGSmith any luck?
@@marcuskruse1 apparently not..
I receive get the error: ReferenceError: volunteer is not defined (line 9, file "Code")
I have the same error. Did you fix it? Will appreciate your help.
In my code, there's an error that says "Can't find method createEvent(object, string, object)"
This probably means that your variables are in the wrong order. Make sure that it's createEvent( Name-of-event, Date-object, Date-object )
How about the "skeleton" code....that would be nice so we can just copy and paste.
Looks to be available (at the time of writing at least) when clicking on "Show more" in the blurb mentioning G Suite Pro above.
@@morriec9868 Clicking Show More dhows me a few links, including one to the blog post, but no code. Also, the blog post doesn't appear to have all the code in it - just a few scattered lines. Are you saying the code itself appears in Show More for you?
@@jimdoria3283 Sorry, no, just that code is in the blog post in the show more section, in parts, not all together.
@@morriec9868 that's because you are supported to *learn* the coding process
@@Rain_theGamer You're replying to the wrong person. I was just answering other persons in this sub thread requests for the code that was shown in the video, but not provided in c&p form. I don't disagree with their reason for asking it however (that you shouldn't expect everyone to learn the language just to do the task (or similar enough) to what is in the how to above). If people want to learn (as I would if I had the need to code in Gcode) there is documentation to do so.
OMG sweetest how-to tech video I have ever watched!
Doesn't pop up on my calendar but the UI Button does on my Sheet :/ any help?
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?
Did you ever figure out how to do this? I'm trying to do the same thing with my marketing calendar. I get duplicate events every time I run the script. Its driving me crazy!
So, the most important thing to update an existing event is to track the event ID. I recommend (1) adding a column to your spreadsheet for the ID. (You can "hide" it from the UI so others don't see it/delete it by accident.) Then, (2) when you call createEvent(), store the event that comes back. i.e.: var event = eventCal.createEvent(). And then (3) you can use event.getId() to find the id of the specific calendar event. (4) Write that ID to your spreadsheet. (5) Before creating a new event, you'll want to see if an event ID already exists in your spreadsheet. If not, create the new event and save the ID. If it does, retrieve the existing event, and update individual values as needed. developers.google.com/apps-script/reference/calendar/calendar-event#methods
@@JoannaGSmith Thanks so much for your advice Joanna. I took what you said and came up with this:
function scheduleDeliveries() {
/**
Open the Delivery Calendar
**/
var spreadsheet = SpreadsheetApp.getActiveSheet();
var calendarId = spreadsheet.getRange("B1").getValue();
var eventCal = CalendarApp.getCalendarById(calendarId);
/**
Pull in each delivery date/time and existing Event ID
**/
var deliveries = spreadsheet.getRange("B3:D25").getValues();
var eventIds = spreadsheet.getRange("Q3:Q25").getValues();
/**
Do the work
**/
for (x = 0, y = 0, z = 3; x < deliveries.length; x++, y++, z++) {
var delivery = deliveries[x];
var oldEvent = eventIds[y];
var eventId = 'Q' + z;
var startTime = delivery[0];
var endTime = delivery[0];
var customer = delivery[1];
var location = delivery[2];
var existEvent = eventCal.getEventById(oldEvent)
if ((!startTime) || (!endTime))
break;
if (!existEvent) { // Creates new events from new entries and writes new event to sheet
var event = eventCal.createEvent(customer, startTime, endTime,{location: location}).getId();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange(eventId);
cell.setValue(event);
}
else { // Updates existing events if changes are found
var existEventStartTime = existEvent.getStartTime();
var existEventEndTime = existEvent.getEndTime();
var existEventTitle = existEvent.getTitle();
var existEventLocation = existEvent.getLocation();
if (existEventTitle != customer) {
existEvent.setTitle(customer)
}
if (existEventLocation != location) {
existEvent.setLocation(location)
}
if ((existEventStartTime != startTime) || (existEventEndTime != endTime)) {
existEvent.setTime(startTime, endTime)
}
}
}
}
This seems to work for me and events aren't getting duplicated anymore if there is already an eventId attached to it from the spreadsheet. If there is not one, it makes an event for it and populates the column. I also realized that I don't need a different startTime and endTime, so I modified the code to use the same value for both. It would have taken me a lot longer to figure out if I didn't have your hints. Thanks so much!
@@xkaliba Hi!, Thank you for posting your code! it inspired me how to modify mine. But did you solve the issue with new entries that are outside the range set to obtain values? (i.e: var deliveries = spreadsheet.getRange("B3:D25").getValues()). I've been having the issue that if i put data in B26, the code does not process correctly, but if I modify the range in the script, it works perfectly, but user should not have to modify the script in order to function.
@@batero06 I think I saw one solution to this -
if (customer == ''){continue;}
this should skip the entry if there is no value for 'customer' in this row
Anyone know what's this error
Error
ReferenceError: calendarId is not defined
(anonymous) @ Code.gs:4
May I ask how this program can understand which timezone it is based on the "start time" "end time" I input inside the G doc? Tks a lot!
As per Google's documentation, if a timezone isn't specified on the entry it will defer to the Calendar's timezone.
@@arkanthiel Thank you for your answer! while I did a test before, seems it didn't follow my calendar's timezone. Anyway, I specified the timezone inside the script finally.
Is there any way to link Google Calendar with ms. Access?
same thing i am using but show Error "Cannot find method createEvent(string,string,string). (line 17, file "oo")" Please resolve it
Do you have multiple sheets in your Google Sheet? I had to specify which sheet first, then the cell. Ex: var calendarId = spreadsheet.getRange("'Database'!L1").getValue();
Hi Joanna, thank you so much for this video! I was playing around aiming to customize this, and I was wondering if there's any articles/additional informations regarding how to create multiple event types, or more specifically how to split the cell ranges up? For example, I was trying to customize this into a start and end dates instead of times, and instead I populated the event successfully, but they spanned from the start date all the way until the end date. I appreciate any guidance you can provide. Cheers!
I tried to reply to this earlier, but think it was deleted because of links. Going to try again
(4th try...i think comments hates me :)
*****
I was playing with customizing what I saw here as well and found what I think you are looking for. This page [Calendar Service | Apps Script | Google Developers] may have the info you need. I found 'createAllDayEvent' options especially helpful, thouugh you may be looking for one of the other listings
If I misunderstood and you are saying you need to break the date and time into different fields then combine on processing, then you may also need this page [Working with Dates and Times | Google Ads Scripts]. It helped me combine date/times extracted from other info into a useable date for a secondary entry.
I am a newbie too, but hope this helps.
Cannot read property 'createEvent ' of null. Help me please
Can you explain what is going on with the for loop here? I'm not sure how to apply it and you sort of skip over it in the video.
Yes, I did skip over it. But I wrote a follow-up post specifically for this, and it's linked in the video description. Check it out: medium.com/@dontmesswithjo/coding-basics-for-loops-90dca2873c70
Is there a way to overwrite the previously made event?
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
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?
I've noticed, on my test calendar, that the items on the calendar duplicate every time the task is run. Is there a way to check to see if the calendar event exists and if so, skip it?
Hopefully this gets answered, it's pretty important I feel.
i am also curious with this issue!
I answered at this question...please check my other comment. Hope it can help you somehow. Bye
Hey Shane, she gave some tips under another comment about what to do so I came up with this:
function scheduleDeliveries() {
/**
Open the Delivery Calendar
**/
var spreadsheet = SpreadsheetApp.getActiveSheet();
var calendarId = spreadsheet.getRange("B1").getValue();
var eventCal = CalendarApp.getCalendarById(calendarId);
/**
Pull in each delivery date/time and existing Event ID
**/
var deliveries = spreadsheet.getRange("B3:D25").getValues();
var eventIds = spreadsheet.getRange("Q3:Q25").getValues();
/**
Do the work
**/
for (x = 0, y = 0, z = 3; x < deliveries.length; x++, y++, z++) {
var delivery = deliveries[x];
var oldEvent = eventIds[y];
var eventId = 'Q' + z;
var startTime = delivery[0];
var endTime = delivery[0];
var customer = delivery[1];
var location = delivery[2];
var existEvent = eventCal.getEventById(oldEvent)
if ((!startTime) || (!endTime))
break;
if (!existEvent) { // Creates new events from new entries and writes new event to sheet
var event = eventCal.createEvent(customer, startTime, endTime,{location: location}).getId();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var cell = sheet.getRange(eventId);
cell.setValue(event);
}
else { // Updates existing events if changes are found
var existEventStartTime = existEvent.getStartTime();
var existEventEndTime = existEvent.getEndTime();
var existEventTitle = existEvent.getTitle();
var existEventLocation = existEvent.getLocation();
if (existEventTitle != customer) {
existEvent.setTitle(customer)
}
if (existEventLocation != location) {
existEvent.setLocation(location)
}
if ((existEventStartTime != startTime) || (existEventEndTime != endTime)) {
existEvent.setTime(startTime, endTime)
}
}
}
}
This works for me since it only makes a new event if it doesn't see an eventId for it on the sheet. If it sees an eventId on the sheet it checks for changes and then sets them. The startTime and endTime can be set to the same which is why I'm using delivery[0] for both values. I haven't added the ability to delete an event if it doesn't find the eventId in the sheet anymore so that's it's a full sync but this has been working for all my needs so far. Hope this helps!
I love the Google sheet and calendar integration. I want to create an add to calendar link to my spreadsheet. For example I have a list of live streams with date and times. I want to have in the last column a clickable link to add all the data to the right in the users calendar. Is this even possible? Do you have some script that would help with this?
Did you figure out how to do this? I'd like to be able to do this as well.
@@lisahubbard356 nope! I am so not a programmer but I think it would be an awesome thing
Is there a link to the google sheet that was created in this video?
Hello, where Can I download this scrip?
Thanks
Regards
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
You'll want to add "options" to your createEvent() call. See the documentation here: developers.google.com/apps-script/reference/calendar/calendar#createeventtitle,-starttime,-endtime,-options
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!
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.
I got this error message too. I worked around it by calling `Utilities.sleep(3000)` every 10 iterations of the loop. You can adjust as necessary-the point is you want to slow down the creation of new events.