Google Sheets - Use Apps Script to Create Google Calendar Events Automatically
HTML-код
- Опубликовано: 18 июн 2024
- 🚀Join my free tech newsletter: got-sheet.beehiiv.com/
UPDATE: I made another video showing how to create All Day Events and also how to add some more options like descriptions to the invites. Check that out here after you watch this one: • Google Sheets - Use Ap...
How to add events to a Google Calendar and email guests all from a Google Sheet using Apps Script and a button.
🎈Demo Sheet (make copy by selecting File - make a copy): docs.google.com/spreadsheets/...
CalendarApp Class: developers.google.com/apps-sc...
=============================
🙏Want to say thanks? Subscribe to the channel; I love making these videos:
www.youtube.com/@eamonncottre...
►You can find more of my work including articles and podcasts on my website at www.eamonncottrell.com/
=============================
⌚Chapter Time Markers:
00:00:00 - Intro
00:00:42 - I'm Eamonn. You're awesome
00:00:54 - Time in Google Sheets
00:01:07 - Dropdown Times
00:03:08 - Combine Date and Time
00:04:17 - Create a Calendar
00:04:44 - Apps Script
00:06:19 - For Each Loop
00:08:30 - Run Function with Button
00:09:44 - Optimize Columns
=============================
#googlesheets #spreadsheets #dataanalytics #googlecalendar #appsscript 
Наука
⭐⭐⭐I've added an IF statement since recording the video that allows you to check the box in column H if an event is already created in your calendar so that running the script multiple times doesn't create duplicate events. Also, here is a link to the sample sheet to view the apps script and make a copy: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit?usp=sharing
@@user-hw7hd9zl1xSure thing! Let me know if you have any follow-up questions 👍👍
Where is the IF statement? Script isnt working for me...@@EamonnCottrell
@@user-qh1gk7vy4q I think you've got it working already, but the IF statement starts on line 15 of the Apps Script: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit#gid=349714920
Thanks for the vid and for adding the "duplicate event" script! Very helpful to sync up my gardening activity log to Calendar.
Is there a way to script to have it "fully sync" to the calendar to include updated event details, event deletions and event additions?
@@SteadyGrowing626Glad it's been helpful! Yes, it is possible to add that functionality. I currently have not made a fuller version, but if I do I will be sure to pin it here. I know I've seen some other folks who have done similar things though.
Great tutorial! F finally I have a real solid explanation under it time format! Thanks I have subscribed.
Awesome, thank you!
this is what i am looking for. thank you very much!
Yay! You’re welcome
Great tutorial! Just what I needed! Thanks a lot !!
Awesome! Glad it helped
Thanks a lot for the script. It is a really helpful tutorial!!
You’re welcome! Glad it was helpful!
thank you very much for sharing
You’re welcome!
Thanks for the video! Can you please make one on adding descriptions please!
I'm so overdue to update the video and add descriptions and locations. Coming soon, I promise! They are added in the same way as the guest email invites, though. Inside those curly braces. Thanks for watching!
Looks interesting, albeit a bit complicated for what I was looking for, which is just a way of reminding a group of people about specials days we have during the year (about six per month), so it does not require start and end times. Plus, I'm not sure if I understood correctly, but there seems to be a list of people who would receive a notification about the events, right?
It might be overkill for what you’re describing. This has an option to shoot an email invite to the event if you include a guest email. I’ve got another video bookmarked to make where it’s simply sending emails from sheets. That may be of more interest to your use case.
Hi Eamonn, I love this tutorial. I wonder how I might add in a description. Ive tried to find it on the developer link you shared but not sure how to input script to pull in description etc.
additionally, if I can ask, do you know if its possible to do the following two things:
Search a calendar to see if the rows have already been created in the calendar and then it activates the checkbox or something similar
Code to search the rows and delete the events e.g.. if you sent out multiple rows and then noticed you didnt need some of them so rather than manually delete you can essentially ctrl z the action. (not very technical terms there so hopefully it makes sense).
Any help would be super.
Hey John, there's probably another video in the mix for some of the advanced options, but yes, you can add a description for the event. It'd need to be in a new column and referenced in the same way we referenced the other items in our forEach loop. It is included by adding it in the object (the place in the curly braces where we added the guests and sendInvites). Let's say, the description is in e[7]...that object would then read {guests: e[6], sendInvites: true, description: e[7]}.
Yes, that does make sense on the other questions about removing events. I definitely want to dig into this more, but in the interest of your time, check out this video from the Practical Sheets channel. He's done a series of event generating videos that I just came across looking for the answer to your question: ruclips.net/video/z1nxgAzo03Y/видео.html
Thank you for this one truly helpful. Btw, is there any update regarding how to add description? Looking forward to hear from you! Cheers! 🍻
Hey there! Yes, you can add a description in the same way as the optional guests and send invites parameters. I have made a more recent video on creating all day events where I walk through adding all these optional parameters. You can check it out here: ruclips.net/video/FDkKIFyaYC4/видео.html
The "with options" part starts at 6:33
Hi Eamonn! Thanks for this, it's exactly what I'm looking for. Quick question, I had it working perfectly with the IF statement preventing duplicate events being created.... but now that is not working and events are being duplicated even if the box is checked. Can you help me out?
Hey! Glad it’s what you were looking for. I’m happy to take a look if you want to share a copy of the sheet you’re using with me. (My full name at gmail)
Excellent video. Very clear instructions. Thank you!
Nonetheless, I feel that you are missing something: shouldn't you be setting e[7] = true for each new event added?
Also, since used the script to add quite a few events for an entire week, Google Calendar was timing out, so I added a 3 seconds delay at the end of the loop, as as to not overwhelm the API.
Hey thanks! Great additions; you're right - it would be a good touch to go ahead and set e[7] to true for us in the script rather than manually checking. Same on the delay - good move!
Hi Eamonn, I'm changing the time locale settings in sheet settings to my timezone but it always seems to default to GMT +5? Am I doing something wrong? For example 0900 entry is creating an event at 0400? Also is there a way to make the Google Meet link which is shared in the event the same every time by default? Great stuff bte - will really help!
Hey there! I dove into the weeds recently with time zones and found a very helpful article linked below. You can change the time zone in Apps Script by going to Project Settings, checking the box to Show "appsscript.json" and then editing that json file in the code editor. Check out the article below, and email me (my full name at gmail) if you need some more detailed walkthrough...I think that will address the Google Meet link as well... yagisanatode.com/help-my-times-triggers-are-not-in-sync-how-to-update-your-google-apps-script-project-time-zone/
Hi Eamonn, Thanks for a cool video. I wonder how i can have these events in my calendar so that they don´t block the time that they are in. I just want to have them as a remainder in my calender.
I'm not sure exactly sure about this one, unfortunately. There is information here about the Google Tasks service if you were trying to create a task rather than a calendar event: developers.google.com/apps-script/advanced/tasks
Okay, I got it working. Do you think its possible to add more info to the newly created events...for example adding an address in the Google Sheet, and the location is in the event on the Calendar, via Google Maps?
You can add description and location as advanced parameters in the same way we did the guests and sendInvites in the options object. Check out this part in the docs: developers.google.com/apps-script/reference/calendar/calendar-app#createeventtitle,-starttime,-endtime,-options
Great video! Is there a way to input code to not send a notification to the invitees like the option we are given when creating an invite manually?
Definitely! In the script, simply delete the "sendInvites: true" part of the last line of the forEach loop. When adding guests using this createEvent method, it defaults to not sending the email unless you specify to do so with that bit that I wrote in the original script.
I’ve created an app for scheduling but don’t know how to connect with google calendar successfully. Any advice or videos on this? Is also pay to have it done for me.
Hey there, are you looking to integrate it into a web app? I know that Google calendar does have an API for developers, but it's outside the scope of what I've done. Start here; I also found some videos when i did a YT search: developers.google.com/calendar/api/guides/overview
Lemme know more details if you're looking to stay in sheets and use apps script - I may be able to help further
So grateful, thank you!
You’re welcome!
THANK YOU!!!! I only have one question: I have added the advanced parameter description, but ideally I would need to populate the calendar field with the content of more that one cell per row, do you think it's possible? Anyways thanks, this is an awesome tutorial!!
You're welcome! And yes, if I'm thinking about what you're doing correctly, you can use as many cells as you need to "build" the description. The easiest thing is to refer to one cell like we did in the video to pull in the description data. But if you have it in say 3 cells, you could combine them together in a fourth cell. For example, if the description pieces are in A1, B1 and C1, you could write this in D1 to combine everything, and then use A4 in the apps script like we did in the video: =A1&" "&B1&" "&C1.
The quotes with a space in them just creates a space in between the data in each cell.
If you wanted newlines for each cell's info, you could write it like this: =A1&CHAR(10)&B1&CHAR(10)&C1
Let me know if I've missed the mark...
@@EamonnCottrell I extended the range of the active spreadsheet to include the new column and it worked perfectly! Thank you!
@@torinodiscovery5959yay!
Hi! do you have a way were I can add another column for Calendar Description and what code to use for that? Thank you so much!
Yes, it’s just another option like the guests and send invites. I did that in an updated video I made about all day events. Check it out for reference: ruclips.net/video/FDkKIFyaYC4/видео.htmlsi=Ga_06QPVjRbBOqjx
@@EamonnCottrell Hi! Thank you so much! It works really well, I learned a lot from you thank you!!! However I came into another hardship with the calendar ID. I used my calendar, it works well. However when I used a different one, its a company account so their calendar id starts with "c_" then it no longer works, how can fix this?
Good catch! You need editing permission for the calendar in question.
Hopefully one last question! I changed the named range for the event and it created the events on the calendar but it didn't send any of the invites past the initial range any idea why that would be?
Hmmm. Not as sure on that one because the invite column should be included for the entirety of that "events" named range so it shouldn't have any issue. I will say that when I've used the same email address to test those invites as I'm using to log into Google Sheets and run the script, it has not emailed me. So, try testing with a completely different email if you're not already and let me know.
I've created the google sheet and script from scratch following your guide. I am in the UK so tiem and date format are set for me. Every time I run the script it inputs the the date as though it is American instead of UK format. If I have the time set to 15:00 Pm it also puts the time in around 10/11am :S Any ideas how I can fix this? Cheers.
You can change the time zone default of the Script or of the Calendar in question. I dove into the weeds recently with time zones and found a very helpful article linked below. You can change the time zone in Apps Script by going to Project Settings, checking the box to Show "appsscript.json" and then editing that json file in the code editor. Check out the article below, and email me (my full name at gmail) if you need some more detailed walkthrough...I think that will address the Google Meet link as well... yagisanatode.com/help-my-times-triggers-are-not-in-sync-how-to-update-your-google-apps-script-project-time-zone/
Great! Is there a way to get a users promising to sync with one is there existing calendars?
in order for the script to run, that account has to have full permissions on the calendar that it's adding to. To do this, go to the calendar settings, and share with the user. Just like sharing a Google Sheet, you need to select the option "Make changes and manage sharing".
After doing this, I was able to run the script from my other account like normal 👍
Amazing!
Is there a way to prevent duplicates of events already added to your calendar?
Yes! I added it after it was requested. Here's the link to the sheet you can copy. It's got checkboxes if the event is already in the calendar: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit#gid=0
Amazing Thank You!!@@EamonnCottrell
Hello, I followed your tutorial and the script works perfect! I learned something new to day, thanks for that :) I have just one question. Is it possible to retrieve the calender ID from a cell? How do I write that action in the script?
Great question, and yes. You can get the value from a cell by doing something like this:
let calendarIDFromCell = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("id").getValue();
This will get the value from a cell that I've given the named range of "id".
Then you just plug in calendarIDFromCell in the Apps Script instead of pasting the calendar ID into the Apps Script itself.
You can see a sample of this that I just made in the Apps Script for the demo sheet here: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit#gid=0
@@EamonnCottrell Thank you for your fast response. I`m struggling a bit to get this to work. Followed your steps, added the function and created a cell for de the calender ID but it didn't work immediately. This is new to me and this is the first script I wrote so I think I have missed a step somewhere. I will try again, thanks for your help :)
@@desireeroubos471Sure thing. It's easy to make a small mistake with this stuff. Happy to take a look for you if you want to share the sheet with me (my full name at gmail)
@@EamonnCottrell I have shared my sheet with you, thanks for your help
@@desireeroubos471You bet. I got it fixed and working and just emailed you the details. Have a great one!
Hi Eamonn, thank you for your script. Unfortunately, having trouble adding more events than the six you provided. Would you possibly know why this is? Thank you
Hey there, it's probably the named range "events". In the example sheet, the named range is just those rows A3:H8. Everything is looping through just that range in the script. Try changing the named range to include all your rows by selecting Data - Named ranges and then modifying the "events" named range. Let me know if it works and I can check out your sheet if you still hit a snag. Thanks!
@@EamonnCottrell where do I need to go to change the named range?
@@user-ko7dp9nh5g Go to Data - Named Ranges from the menu at the top. This will open a sidebar where you can edit and/or add named ranges.
Great information! However, I’m trying to set up the time in your sheets to include thirty minutes increments. Example, if an event starts at 4:30 opposed to 4pm. How can I change this to have it work? Thanks
I've got the times selected from the "hours" named range which is in column I (click the arrows in between columns H and L if column I is hidden). You can add whatever times you want in this list. Just make sure if you extend the range past I26 where the named range is currently set, that you open the Data - Named ranges menu and adjust it there as well.
Hello! Thanks so much for making this script. Having a related issue to this. I have set the time increment to every 10 minutes and adjusted the data validation range accordingly for the start and end times. As soon as I select a time that is in the PM, I get a red mark in the corner of the cell that says "Input must fall within specified range." Any idea as to what I'm doing incorrectly? Thank you!
Also if there is a way to change the color of different events that would be cool too :)
@@EamonnCottrell
I have got it to work, sort of, it is just what I am looking for.
Why sort of? I think I have a problem with timezones, I am in the UK and when I add your data to my calendar it adds it as my timezone - default so a start time of 12am inserts an event for the previous day at 5pm: and one for 2am is created for 7pm the previous day. So I think it is taking off 7 hours. How do I force the CreateEvent to use my timezone?
Yeah, there are actually two places to change time zones: one for the Google Sheet under File - Settings. And one in Apps Script under Project - Settings. I did a short video on it here: ruclips.net/video/13TzFwWEWxw/видео.html
Good day ! I am trying to do the opposite of what this is doing. I am looking for a way to have events that are already in google calendar feed into a good sheets. I am hoping to then use the information on the sheet to plan/budget with. Is there a way to reverse what you are doing ? Also, in trying to replicate what you are showing here, under the "event start" i get 5/22/2023 12:0 P5 " or "A5" instead of AM or PM. What am i doing wrong ?
Thanks in advance !
Marc
Hey there, not sure about the typo errors. The formula should take care of formatting in column B:
=TEXT($D3,"mm/dd/yyyy"&" "&text(E3,"hh:mm"))
Yes, you can pull info the other way (from a calendar to a sheet). I may try doing a video on this sometime, but here is one I found that does a good walkthrough of it: ruclips.net/video/YDhln3ukH7Y/видео.html
Would you happen to know how to update calendar events with new invitees with a form trigger? I have the event ID, but I am not sure how to make your function work for what I want to do.
This may be need a longer answer - you're welcome to email me if this doesn't answer your question (my full name at gmail). You can grab an event and add a guest to it using this built in method: event.addGuest(attendeeEmail). to get the event, you can use the getEventById(iCalId) method and throw your calendarID in there. If you're wanting to hook up a Google Form to the workflow, I'd need to dig some more; I've not set this up before but it sounds like a wonderful idea! 😀😀
I get an exception that says "Exception: Invalid argument: Invite" when I try to run the script. Any ideas here? I have row 2's headers set up the same: Event, Event Start, Event End, Date, Start, End, Invite, with the same corresponding info below each header. When I remove the Invite heading, the error disappears and the script works, but I would really like to be able to invite coworkers.
I bet that you've got that header row included in the named range you're looping through. Check that out. In my example, I'm starting that named range for the events data in B3 where the first event is. I think yours is probably trying to read the header line and then not seeing a valid email address where you've got "invite" written.
Is there a way to use the Contact Groups? so I can just add the Label/Group Name instead of adding all Emails for the guests?
it says in AppsScript, getContactGroup(name) = function is deprecated and should not be used in new scripts.
Any suggestions to do this easily and quickly?
thanks in advance
Interesting...looks like you've got to use the People API for this now. Which entails turning it on in Apps Script by adding it as a Service. Here's an article on some common functions, the first of which in the example is about getting the contact group by name: developers.google.com/apps-script/migration/contacts-people
Is it possible to repeat appointments based on the day they are on? Eg. every Monday from 8 am to 10 pm.
Hey there. Yes, you can do this, it just uses a different method called createEventSeries() which you can read about here: developers.google.com/apps-script/reference/calendar/calendar-app#createeventseriestitle,-starttime,-endtime,-recurrence
hello! from 5:03 in the video, for some reason the variable "events" keeps coming up as "undefined" when i've tried to do the exact same thing with code. it also says that line 2 "is not a function".
is there a way that this could be fixed? i'm new to coding so i'm not really sure what i'm doing hahah.
aside from this, this is a very helpful video; thank you!
I bet it's something small like a typo in the code. Care to share your sheet with me and I can take a look? my full name at gmail
@@EamonnCottrell will do - thank you for the speedy reply!!
@@OffScore.Warwick Sure thing; I think I've fixed it in your sheet...just emailed you back. 😀😀
How to also add description of the event with event name with the help of the same script?
Please Help!
You can add this in the same way as the guest emails and invites. The description is another optional parameter. Check out these video I did where I go through adding all the available options as part of the example. And let me know if you need any other help: Google Sheets - Use Apps Script to Create All Day Calendar Events
ruclips.net/video/FDkKIFyaYC4/видео.html
Hi there! Thank you for the information! I got to the very last step and I am getting this as an error... any ideas? Appreciate it. I'm NOT a coder, so help is appreciated! TypeError: Cannot read properties of null (reading 'createEvent')
That has typically happened when i've had an issue with my calendar ID. Are you copying and pasting it from your google calendar straight into line 16? Make sure to leave the double quotes and remove the angle brackets: getCalendarById("")
Thanks! How do I fix the timezone? I've already updated the GSheet timezone to Australia/Melbourne AEST.
In GSheet event date to create is 2/24/2024 2:00 PM - 3:00 PM
BUT it creates in calander on 2/25/2024 6AM - 7AM
There are time zone settings in the apps script menu as well. See if those are set correctly. I don’t know why they have them in both places.
@@EamonnCottrell Thanks so much, changing time in app script settings worked!
@@auraoflight4429fantastic! Glad that worked for you!
Is there a way to script that if an event time is say 7 AM- 5PM to have Google Calendar automatically check mark the "ALL Day" indicator in google calendar?
Secondly is there a way to create and "edit" button so that you can edit the Column details in your spreadsheet and then run a button to just edit the details within the "event" on the google Calendar?
I have these same questions! And another one: if you have multiple calendars, can you select which calendar to add the event to?
Hey sorry for the late reply! Yes, to both questions. You can have a script run in the same way for an all day event if criteria are met. You’d use this method instead for that: developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-date
The edit functionality is a bit more complicated but I have seen it done. I have plans to do another more in depth video on all this
Yes! A good way for this would be to have the calendar addresses in different cells in a sheet. Then use named ranges for each of them and reference the named range in a conditional statement in the apps script. You can have them selectable from a drop-down list in the main sheet where your putting event info
This is almost exactly what I'm looking for. I say almost because of a couple of questions. First, how many events can you create? Is there a monthly limit? Where can I eat create as many events as there are rows on the spreadsheet?
Second question, what is the trigger? I'm assuming I don't have to go into apps script and manually run it every time I want to add new events. You said you added a checkbox that will prevent the event from going to the calendar. Will the events just automatically appear on the calendar by the virtue of being populated in the row?
Last question. If I wanted to make this for a client's and I don't want them going into the script to add their calendar ID, what would I need to add to the script so we can look for a particular cell on the sheet where the client can paste their calendar ID?
Thank you for your help!
Limits: here's a list of quotas based on what tier of Google Workspace you're on. tldr; 5,000/day for calendar events created: developers.google.com/apps-script/guides/services/quotas
Trigger: I've got the button in the sheet set up as the trigger to run the script so I don't have run it manually. Nothing will appear on the calendar until that button's clicked, and it'll pass over the rows with the checked button. Here's the demo spreadsheet you can check out and make a copy of: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit#gid=349714920
Calendar ID: Yeah, I should have done this for the video. Have a cell in the sheet somewhere for the calendar ID. Say it's in E1. Then, I'd name that using a named range, say, "calendar". Then you can create and use a variable in the Apps Script by using var calendar_id = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("calendar").getValue()
@EamonnCottrell dude! Thank you so much for getting back to me. I researched how to do the calendar ID part. Simple enough. I like your button trigger. I'll probably use that code for something else. I want this to send to the calendar automatically. I also love your checkboxes to remove events if necessary. I'll be working on some formulas later put all this together. Ideally, when I hook a new event I wanted to go on the list and go on the calendar immediately. So I suppose that would be an onEdit trigger. For my events I have status updates. If I update one of them to cancel, I'd like it to automatically be removed from the calendar. I can probably just replace your check boxes the status updates. Because I am new to this I only know complicated workarounds. What I'm thinking is I would create a she just to gather all the information, sort of, and organize it. Create an array on that sheet to replace the check boxes so the cell will simply read true if the event status is updated cancelled. If the event isn't cancelled the range will read false. And then query all of that on to another sheet organizing the same format you have it. And set the query to only add events if the status update is false. If you can think of an easier way of doing that, I'm all ears. I'm sure I'll stumble upon something simpler as I continue to educate myself. But for now, thank you for letting me share my ideas and thank you so much for getting a detailed reply to my question.
@@EamonnCottrellI suppose I'll also have to figure out how to add a field for location and details. But this gives me a start.
@@richardthemagician8991 Check out the docs here for how to put location and details in as advanced options. You do it within the curly braces in the same way that I added the sendInvites option in the video. You'd just add columns for those and reference them using dot notation...and yes, I realize that a follow-up video going through a lot more details/options needs to happen 🤣🤣
@@richardthemagician8991You're on the right track with this, from what I can tell. I'm the same way - complicated solutions come first and pave the way to whittle it down to something more concise. Keep digging and I know you'll find additional resources that go into more depth especially with the updating/deleting functionality. I would like to put out a more in depth video that builds on what I started here...
Amazing!! Just wondering how to add the guests and only send the invitation to the guest.
I may do a separate video on this setup. So you just want the email sent but not event added?
thanks for your reply, can we do both, addGuest() and send out the email invitation at the same time to the added guest@@EamonnCottrell
Is there a way to get the script to run automatically when new data is added to the sheet? I don’t want to press the button i would like it to automatically add data in calendar without interaction only when new data is added to the sheet
You can make onEdit triggers, but it would try to run every time you edited anything. You would need the trigger to be tied to something like a checkbox column so that once that checkbox is checked, the script would run. You wouldn't save much, though, since it would still require something particular to be clicked, in which case clicking a button accomplishes the same thing. Let me know if you find a more concise automation like what you're looking for.
I'm hitting a major problem when it comes to the script acknowledging that my 'event start' and 'event end' should be in CST...not EST.
Any recs?
Hey there, Open apps script and on the left side, select Project Settings. In there you can change the time zone. It probably copied EST from my original sheet.
You can also change the time zone of the sheet itself by selecting file - settings.
I'd be safe and just put both of those as CST.
tbh I don't know why there are two places for time zones, but there are...
Hi, got a question. Let's say after I've done all these and it sync to google calendar, I want to add 1 more event of the google sheet, will it automatically sync to google calendar or I have to click that 'Add to Calendar' button and then it will start to reflect at google calendar?
You’ll need to press the button again. That is what actually triggers anything to get out into the calendar in this setup. Since recording, I added checkboxes in an example sheet so you can check off events already sent to the calendar too. This way it won’t create a duplicate event when you push the button again: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit
@@EamonnCottrell alright sure, appreciate your response. Thank you so much!
@@EamonnCottrell Apologies, if I have many sheets, how do I change the script to make it active on that one particular sheet?
@@kasmadi4979 I like using named ranges for the range where the events are. That way you can just use that named range in the Apps Script to get the events like I've got in the example. You can also use getActiveSheet().getRange(), but I don't think it's as clean that way.
I don't have an ID calendar. I mean in Calendar ID what I can see is just my email address. Any chance to figure out how to proceed?
Hey good question and I actually didn't know the answer till just now either. In this case, your email address is the ID. Put it in full in the quotes instead of the long ID thing.
Hello, what an excellent video!
Please help !!!!!!
Can we send anonymous invitations? For example, I put two email addresses in the invite column, and the invitation is sent without the invitees being visible to each other.
Oh, good idea! Yes! At the end of the createEvent part, you can simply add this: .setGuestsCanSeeGuests(false) I've added it (and commented it out) on line 24 of the Apps Script of the demo sheet for you to see.
i love this, it works great, i am trying to add a color though and am pulling my hair out. i have added a description column, in I and a color column in J. The description works fine, the color refuses to work with an input of red, or a hex code. i have adjusted the named range to include j, and the code i am using is {guests: e[6],sendInvites: true, description: e[8], color: e[9]} and ideas? this would really help manage a large shared calendar.
Oooo this was a good one - I hadn't looked into this before. Ok, so the .setColor() method wants a number from 1-11. It's just using the 11 colors found in the article I'll link below. If you have one of those numbers in your e[9] column, it will work fine. But I don't think hex codes will work with the way Google Calendar has their colors setup. Here's that link for you to see which color is which: developers.google.com/apps-script/reference/calendar/event-color
@@EamonnCottrell thanks for getting back to me, ive tried what you suggested above but cant make it work. i have tried all sorts, but even when i adjust the script to include the color, it just keeps defaulting to blue. i think maybe this is a loosing battle and that this part just cant be automated!
@@user-jc8gz5iy2wCheck out this sheet where I've got it working by just adding setColor(e[8]) to line 22 in the code - docs.google.com/spreadsheets/d/1s5QtOF1t2pgqLPA6VC32m_XUAbjw_Tpol5Nrm418QWs/edit?usp=sharing
@@EamonnCottrell legend! humans 10,000,000 GPT 0 thank you so much, this is awesome! Keep up the great work!
@@user-jc8gz5iy2whahaha! you bet! Have a great one 👍👍
hello, how can we make whole day events? or events that will take days to complete? :)
You can have a script run in the same way for an all day event if criteria are met. You’d use this method instead for that: developers.google.com/apps-script/reference/calendar/calendar#createalldayeventtitle,-date
The edit functionality is a bit more complicated but I have seen it done. I have plans to do another more in depth video on all this
Just tried to use this and when I create more rows of events they don't seem to be sent via the calendar invite. Any idea why that would be?
Hey there, yes, it's probably because the script as it's written references the "events" named range which is only A3:H8. If you edit the range to include all the rows you've added, you should be good to go 👍
Perfect that worked! I'll be sure to start using this at work!@@EamonnCottrell
Hi! thank you for this video!
The event is duplicated when I run it more than twice.
Is there a way to avoid duplication of the event?
Great question; I should have added this to the original apps script. I would suggest adding another column of checkboxes. We can check it if it's already entered in the calendar and then create an IF() loop in our apps script testing for whether the checkbox is checked or not. If it is, we don't do anything, if it isn't, then it's a new event and we create it like normal.
@@EamonnCottrell Are you able to share the script of the If formula and where to input it? This helped a lot! Thank you.
Hi! Were you able to insert the if ( ) for the checkbox? If so, are you able to share that info? Thank you!
@@achow21Yes, I just added that to a sample sheet, and here is the link to view: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit?usp=sharing
@@EamonnCottrell Appreciate it!
HI, ho can i make it not make a new event everytime I updated the sheets??? thanks
Great question; I should have added this to the original apps script. I would suggest adding another column of checkboxes. We can check it if it's already entered in the calendar and then create an IF() loop in our apps script testing for whether the checkbox is checked or not. If it is, we don't do anything, if it isn't, then it's a new event and we create it like normal.
Hi! I am getting an error when I try to combine the event start/end (column B). I've typed =TEXT($D2, "mm/dd/yyyy" "&text(E2,"hh:mm"))"), but it shows as an error when I hit enter. Any ideas?
You've got a couple small typos. And you might be referencing the wrong row. In my demo sheet, the data starts on row 3...try =TEXT($D3,"mm/dd/yyyy"&" "&text(F3,"hh:mm"))
You can copy and paste this to see the quotation mark, ampersand and parentheses differences.
Hope that helps!
@@EamonnCottrell Thank you for your quick reply! That worked. Two more questions: 1. Is there something I need to do to make the send invites code work? I didn't touch it from what you have listed in the code. 2. How do add a description and location? I'm assuming add the columns to the spreadsheet, but how do I add the loop to do that? Or, if you have a video that shows how to do those, that would be super helpful! Thanks again!
@@user-zt6eu2fp6w So for the invites, I have noticed that if I invite myself, it doesn't send an invite (perhaps because it's with the same account I'm logged in to the calendar with). It does add to the calendar, though. I've got to enter a different email for it to send the invite to that email.
For the description and location...I reeallly need to make another updated video because I left off these important pieces. They go in the curly braces advanced options just like the guest invites, though. So, yes, they'd go in another column and then you'd reference them like this: {guests: e[6],sendInvites: true, location: e[##], description: e[##]} where those e[##] parts are for the column numbers where they go..
I will make another video though! I'm overdue to add these as you're not the only one who's asked about them. Thanks!
Stupid question but What if I want to send 1 invite it to multiple invitees
Just separate the email addresses with a comma (NOT a semi-colon).
Hi, it's me again. My sheet currently runs smoothly, able to display to calendar after pushing the 'add to calendar' button. I shared the spreadsheet to my colleague who is able to access and edit. But after clicking 'add to calendar', it prompts an error "Exception: Action not allowed". Why is this so? Is there a way to make other users able to click the button successfully?
Hey there, I did some digging and ran into the same issue when I shared the sheet with another of my accounts. It's because in order for the script to run, that account has to have full permissions on the calendar that it's adding to. To do this, go to the calendar settings, and share with your colleague. Just like sharing a Google Sheet, you need to select the option "Make changes and manage sharing".
After doing this, I was able to run the script from my other account like normal 👍
@@EamonnCottrell okay sure will try to make those changes. Thanks so much!
Hi Sir, what to do if the values in my cell composed of formulas?
Should be able to do this. I have formulas in columns B and C in the video to get the date into a useable format. Can you share an example where you're having troubles?
Is there a way to send sms invite as well?
You can't do this natively, but there are some plugins around that allow for it. I've looked into it in the past but not pursued. Twilio and Sheet SMS are two that I have seen. 👍👍
Hey Eamonn! Do you got a minute to spare and perhaps can help me? I've been sitting for hours trying to get it right and also trying different codes. Everything in AppScript is running smoothly, however NOTHING is showing on my calendar... Do you maybe got and idea of whats up?
Sounds like the permissions for running the script haven't been accepted. Did you see a pop up when you tried to run it that said stuff like "this could be dangerous are you sure you want to authorize the code to run?"
Sometimes if you're signed into Chrome using a different profile than the profile you're using in Sheets, it'll cause issues too.
Feel free to share your sheet with me if you want me to take a look too. (my full name at gmail)
@@EamonnCottrell Hey Eamoonn! Earlier this morning I managed to figure out what was up. Such a simple thing. When I write dates, in Sweden we write dates like "dd/mm/yyyy" which is what I did in my sheet. Google probably can't read that. When I changed it to "mm/dd/yyyy" everything worked just fine and my whole office heard me scream "YEESSSS!!!"!
Ahhh, excellent! I didn’t even think about it being a regional thing. I have run into that before. Glad you figured it out!!
Hello, the code says it's successful but the events don't appear in the calendar. Any idea of what is going on?
Huh. The only time this happened to me while I was figuring out how to make this work was when I had the wrong calendar pulled up or the wrong date plugged in (both of which I did several times in troubleshooting 🤣🤣🤣). Did you change anything else in the script?
Oh man, watching this Ive realised I know literally nothing hahaha. Me: thinks a perpetual calendar in sheets is a good idea I can just import annually, Reality: WAY harder to execute than I thought. PLLLLEASE tell me you have a video on this hahaha
Haha! Tell me more, though. What do you mean by a perpetual calendar? A ton of rows in a sheet that are all imported to the calendar kind of like in this video?
Sorry for the delayed reply @@EamonnCottrell I had this bright idea that it would be easy to use google sheets to set up a meal planning calendar, hubby has a hard time knowing when and what to cook and I hate constantly telling him so I wanted to make something I could load into google calendar annually that would say hey your cooking and you need these ingredients. I got very stuck at the part of exporting 😬 and I have a new found belief you are truly a wizard with a spread sheet. My plan was a column for every day, and a row for dinner, ingredients, a link to the recipe and an email, so on the days he is to cook it would prompt him to buy the stuff on his way home and cook :)
@@eMavern_Emporium I love this idea. (so does my wife 😀). Yes, we can get this to work for sure - do you have a rough draft sheet yet that you could share with me? (My full name at gmail). No worries if not, I will fiddle with this either way this week.
I made my duration every 15 minutes, however, I'm getting an "The data you entered in cell E2 violates the data validation rules set on this cell." error. But only with certain time values! I cannot figure out why! For instance, 10:00 AM might work fine, but 10:15 AM throws an error! Everything I've googled says to format the times as plain text. But if I do that, it reverts the times back to the decimal values! Any ideas?
Ugh..that's frustrating, I know. I suspect it's a very nit-picky formatting issue. For instance, I just tried out changing a couple of my times to 15 min increments and it worked. I manually typed in 11:15:00 PM and 11:30:00 PM exactly like that and included the seconds place and the PM part. And it did add to my calendar. You can also find the decimal value for 15 minutes (0.01041666667) and add it to each of your correctly formatted times. Check out my sheet on columns I, J, and K where I've edited it a bit for your use case. Hope that fixes it; let me know if not!
@@EamonnCottrell I'll play around with it a little more. I tried for probably an hour or more on Friday to figure it out! I have the value for the duration, and the time increments based on the value itself (not the cell with the duration), if that matters. Once I had all the times I needed, I named the range "Hours", and created a data validation based on "Hours". Then, on my sheet, I created enough cells with that data validation to list every time *in* the data validation, so I could see where all the errors were. The first error started at 9:30, second was 10:15, so on and so forth. By the end of the list, the last 15 or so all had errors.
I changed the duration to 00:30:00 to just to see where the errors are, and they're completely different cells, and different numbers! It's so strange! I even converted the times to text, just to see, and that didn't work either! I'm not a "professional" spreadsheet maker, haha, but I am a front-end developer, so it bums me out when I can't figure these things out! haha!
Also, I'm not able to hit the "Plus" sign on your example sheet. Thanks for your responses!
@@EamonnCottrell I created a dummy sheet to further display the issue:
docs.google.com/spreadsheets/d/1DDwfb4RdZ_3XmBaJvEe84aKDWEn_w-TGGzn8EeRyIt4/edit?usp=sharing
@@meganorsi8591 Huh...are you able to share the sheet with me? Maybe it's something I can spot with fresh eyes. My full name at gmail
@@EamonnCottrell just added access!
I cannot get the script to run it keeps telling me that events, alreadycreated, and newEvents is not defined. What do I do to fix this ?
There's something typed wrong probably--I run into those errors all the time...I've got the full code linked in the Apps Script here if you want to copy and paste and adjust for your needs: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit#gid=0
Happy to take a look at yours too if you want to share with me (my full name at gmail)
@@EamonnCottrell Thank you so much! I actually looked at it again and checked some stuff and it ended up working. I am just curious why did you use "let" statements instead of "var", sorry if this is kind of basic but I am very new to this
@@wildabeast16Awesome! I'm glad you've got it working. var and let are interchangeable in this scenario. Their availability differs and in some cases one is better than the other. "let" variables are available only in the block they are declared while "var" variables are available throughout the function. No reason why I used it over the other--probably was just trying to be different when I coded it. 🤣
For some reason it doesn't work for me. I constantly get the error indicating that the date is an invalida argument :/
Hmm. Check both your spreadsheet settings and the apps script settings. You may have a time zone setting mismatched. I also have Enable Chrome V8 runtime checked in my apps script settings.
(I know nothing and am literally step-by-step copy-pasting)
In sheets, I get "TypeError: Cannot read properties of null (reading 'createEvent')"
In the debugger, it gives these two errors:
@ Event 7 and highlights line 7 of the code
createCalenderevet @ event 6 and highlights line 6 of the code
In the cloud logs
TypeError: Cannot read properties of null (reading 'createEvent')
at [unknown function](events:7:127)
at createCalendarEvent(events:6:9)
Thanks for reaching out! I'm not 100% sure without looking at your code, but did you make named range and name it "events" for the range where all your events are? In my sheet it starts with the first event at A3:G8.
The Apps Script uses that named range to get all the data and store them in the events variable.
You're welcome to share your sheet with me and I can take a look too if you're still scratching your head. My full name at gmail. Let me know if you figure it out in the meantime!
@@EamonnCottrell I shared the book with you I really appreciate any advice.
@@michaelluce4524 Cool...emailing you now. Your script works for me so I think it may be a Google profile issue. Details coming via email
I’m having the same error message
TypeError: cannot read properties of null (reading ‘createEvent’) as well. Any advice would help.
me too I am having the same error as @@willhennegan7986 Any help? @eamonncottrell
HELP!
Why does whenever I click the button to run the script, it's giving me this error: "Authorization required
A script attached to this document needs your permission to run." I'm sorry if I can't troubleshoot this on my own. This is the first time I've ever done this and I even had to search how to create a button. But now I'm stuck. Can somebody please help me?
It's just authorization required to run Apps Script the first time. Check this out: ruclips.net/user/shortskC53zCctVqw?feature=share
@@EamonnCottrell Wow, thank you for taking the time to create shorts to explain this! However, I can't find the questions that would allow me to grant permission. Instead, it says, "Google hasn’t verified this app. The app is requesting access to sensitive information in your Google Account. Until the developer (my email address) verifies this app with Google, you shouldn't use it." The next option is to click a blue button that says 'Back to safety,' which prompts me to the Google Help Page titled 'Share some access to your Google Account with third-party apps'.
@@mujerindependiente8324 Over to the left of that back to safety blue button, there should be the word, Advanced, that you can click. Then it'll show some more text at the bottom with a clickable, "Go to setEvent (unsafe)" line of text. Click that. Then it will show you the permissions it needs to access to allow the scrip to run. At the bottom click, Allow. They make it very unintuitive in case someone shares malicious code with someone else.
@@EamonnCottrell OMG, it worked!!!
@EamonnCottrell I'm so sorry I have another question. After I allowed permission, I clicked the button and now I have this on Google Sheet: TypeError: Cannot read properties of null (reading 'getValues')
i've used your template, but it only creates 7 events - no more
Hey there! To make more, you’ll just need to change the size of the named range “events”. The script is pulling everything from that named range but it’s only set for those seven rows in the demo. If you go to data validation-named ranges you can edit it.
Why is the demo sheet just advertising for this video
There's another tab at the bottom for the spreadsheet as seen in the video. And you can open apps script to check out the script from the Extensions menu.
Hi Eamonn. Got this error. How to fix this?
5:30:15 PM Error
TypeError: Cannot read properties of null (reading 'createEvent')
(anonymous) @ Code.gs:11
createCalendarEvent @ Code.gs:9
I’ve run into that a lot and it’s usually because you’ve got something in the wrong cell. So the script is referencing the one right next to it. If you can’t figure out, share a copy with me and I can take a look. My full name at gmail
Great tutorial and glad I found it!
I recreated your script exactly as shared in the video but I am unable to run the script and keep getting this error:
Exception: Invalid argument: invite
(anonymous) @ Code.gs:7
CreateCalendarEvent @Code.gs:9
Could you help me please?
Did you use the same named ranges, particularly the "events" and "id" named ranges. Those are used in the apps script and will need to be modified for your sheet if you're using differently named or sized ranges. Feel free to share your sheet with me if this doesn't help and i can take a look. my full name at gmail.