⭐⭐Grab the FREE demo sheet here: www.gotsheet.xyz/c/apps-script-all-day-calendar-event ‼UPDATE: I've added buttons in the Google Sheet since recording this so that you can run any of the four functions that I made directly from the sheet by clicking the button. There's one each for oneDay(), twoDays(), withOptions() and recurrence() depending on which one you need for your use case.
Hi Eamonn, Hope you are well. I have few questions to help my scenario 1) for each Entry I want to create Events 5 Months Prior , 3 Months Prior, On the Date and 3 Months After the date. Is that possible if yes how is that possible 2) Whenever I run the script I donot want the system to create duplicate events 3) Lastly. When I change the date in the Google sheet. I would like it to change the Event dates also automatically in my calendar. Your Help will be greatly appreciated. I have tried to find systems that can help however I could not find any solution
@@toprealtorgarrythind6316 A few thoughts: 1) To do this in Google Sheets, you'll need a separate row for each of those events. 5 months prior row, on the date, and 3 months after. This gives you the flexibility to either copy the rows and change only the dates, or to also have separate descriptions, details, etc. 2) I handle duplicates with the checkbox in the demo sheet. If a row is checked, it won't add that event again. 3) This is a little tricker and outside the scope of what I've put together. You'd need additional code to get the calendar event ID's and then update their info by running a separate function if you change something, Feel free to email me if you need me to point you further in the right direction or clarify (my full name at gmail)
Hi Eamonn, just wanted to say thankyou so much for this tutorial! I've been able to set this up for our business for scheduling purposes with minimal experience with Google sheets/excel. Cheers!
Hi Eamonn, thanks for this tutorial! Is it possible to use the described method to create events that reoccur on specific dates and times? In example I have "Event A" and in the same row I list 4 different dates and 4 different times when it is going to happen. I'd like script to create 4 different events based on that.
You could, you'd just need to handle each of those cells with the different dates and times accordingly in the Apps Script. They can be referenced in the same format as I reference the single dates i.e. e[0], e[1], e[2], etc
Hi Eamonn! I wanted to ask if there is any way that we could use more than one calendar at a time? Thank you in advance and also thank you for your content very huge help to scale my business! Have a great day!
Hi Eamonn, Thanx for again a very cool feature video. Can i add some script or function to the sheet, so that the Row (J) existing checkbox is automatically to come "checked" when script has been run and the meeting is added to my calendar?
I would love to be able to add an attachment (to a different google sheet) to the event. I'm assuming I would just put a URL in an options field, but I've tried 'attachment' and 'addAttachment' with no success. I'm working around this by putting the URL in the description but an actual attachment would be cleaner. Do you know the syntax for making that work? And thanks for these videos. Saving me a TON of time and effort.
Great question, and one I haven't looked into fully yet. So, adding the url of course works like you figured out, but I found this article from a Google Sheets legend with a script for actually adding the attachment from your google drive like you want. The structure of how he builds this out is different from how I did my video, so I want to check it out more thoroughly myself, but here's the link if you want to do some trial and error in the meantime: www.labnol.org/code/20157-create-google-calendar-event-attachment
Eamonn, Really professional teaching skills, I thank you for sharing your knowledge. I have a question... What I need to do in the script in order to insert a hyperlink instead on sending email invites? Also in the location I'm using a smart chip but it does not recognizes it, Any advice on how to accomplish that?
Hey there, what are you needing to do with the hyperlink? If it’s going into the description of the event, it’ll need to be set as an advanced option like with the email addresses. Smart chips require different syntax to interact with. If you reference the cell and then use dot notation you can extract data from the chip. My friend Ben has a good article detailing this: www.benlcollins.com/spreadsheets/smart-chips-in-google-sheets/
Dear Eamonn, Hope you can help me out. I keep getting "Exception: The parameters (String,String) don't match the method signature for CalendarApp.Calendar.createAllDayEvent." when running just the OneDay part (haven't gotten further yet). And I can't figure out why it keeps saying it? Is it in the code or in the values/input? I've copied the code and just changed the numbers between the brackets to match my sheet.
Hey there! So the issue is in the spreadsheet values. The method we are using needs a string for the title and a date for the date. Try formatting the column in your sheet where the dates are. Right now, you’re giving it a string value instead of a date value. The issue could also be that you’re just referencing the wrong column in the bracket notation. Remember that it will start with zero. I’ve made that mistake a ton of times. 😅
Thank you for the video! What I need to do is the exact opposite. When a Google Calendar event is made, I wanted the details to be shown in my Google Sheets. Could you tell me if that's even remotely possible?
Oooo, that's a good bit harder. Pretty sure it's possible but I haven't done this. I would probably suggest looking at a Zapier integration because I know you can set one up for that pretty easily.
Help! I have been watching multiple videos like this but I still can't get it to work with our google sheet. I've applied different code.gs from app script to the google sheet however i feel like i'm missing something all the time. Do i need buttons on google sheet
Yes you can set the color using the setColor event on the event itself. Here’s the docs on that: developers.google.com/apps-script/reference/calendar/calendar-app#setColor(String) And yes, you could differentiate between all day and time bound events in the spreadsheet so that you could add an additional condition in the apps script. This is on my list of items for the next video in this series I just have not built the necessary code yet.
I'm getting a strange error telling me that the script cannot access the calendar data. What can I do if I want to keep the calendar private? The error: Exception: The script does not have permission to perform that action. Required permissions: (.../auth/calendar || .../auth/calendar.readonly || .../calendar/feeds)
Interesting. I'm not sure why you'd be getting this if you went through the steps here. You can put the calendarID string straight into the apps script code like this if you don't want it displayed in the sheet itself: CalendarApp.getCalendarById("paste your full calendarID here and make sure it's enclosed in quotes") You have to enclose it in quotes, though, maybe that's the issue?
Hey Eamon , Im trying to add rows to the table , but it keep only adding event for the first 6 events that exist in your Demo , I tried to add to the data vlidation range up to row 45 but I still dont get it updated in the calander and it runs only on the first six rows can you advice ?
You've got to change the range of the named range "EventList" so it goes down to however many rows you've got. If you still have trouble, share your sheet with me and I'll take a look (my full name at gmail)
Hey there! It's because I'm using the named range, EventList (B8:J19) in the apps script. You can expand the range by going to Data - Named Ranges and adjusting as you need. Thanks!
@@treyhughes3869It's the named range, "EventList". In the spreadsheet itself, you'll modify that named range. Go to Data - Named Ranges from the menu in the spreadsheet to see it displayed. You can modify that range so it fits your data. Then in the apps script, it's being used on lines 8, 28, 50 and 76 in the getRangeByName() function. I find it easier to setup named ranges in the spreadsheet so that we can use those more readable names in the apps script.
@EamonnCottrell thank you so much! I spent hours a year ago trying to create a script for my Endurance Group calendar (for races and group events) and finally gave up. I would end up manually making over a hundred individual events. Last night, after 30 minutes of watching your video and using the shared copy, I had something I could use for years... just with a little bit of copy pasting, but with your response, I am so hopeful. Thanks again!
@@treyhughes3869Fantastic! I hope it works just like you need it too. Feel free to ping me if you get in the weeds and need another set of eyes on anything. My full name at gmail. Happy to hear it's for endurance races too 😀 - I've got a 100K all day tomorrow
Hey Eamonn, I’ve tried to used the DEMO sheet. I’ve replaced Calendar ID with mines and I’ve done the Give Access part. The thing is when I try running the code I get the error “TypeError: Cannot read the properties of null(reading ‘createAllDayEvent’) Any ideas or solutions to solve this problem? Look forward to your feedback
This usually has to do with named ranges being either unassigned or pointing to the wrong place. Feel free to share with my full name at gmail and I can take a look if you’re still having problems
Hi Eamonn, First of all, thank you very much for your training videos - they are very helpful. I used your script (withOptions) and slightly adapted it for my needs. Everything works perfect, but I encountered a small problem. It is very important for me that the script is executed by clicking a button, located on another sheet, but in the same spreadsheet. When I click the button to execute the script, the event is added to the calendar, but the checkbox remains unchecked (false). If the status of the checkbox does not change to "true", then each time I click on this button, duplicates of previous events will be created in the calendar. Summarizing, if I run the script from a sheet with the data used to create the event, the checkbox changes its status to “true”. I suspect there must be a simple solution here, but I'm not familiar enough with Apps Script to figure it out on my own. It is possible that the reason is that the script references ActiveSheet. In my case, I probably need to specify the sheet name, but this requires some changes to the code and this is where I lack knowledge. I would be very grateful for any advice and ideas. Thank you in advance!
Sorry for the delayed response! Yes, I expect that there is only a small change necessary. Are you able to share a copy of the sheet you’re using for me to look at and recommend changes to the code? My full name at gmail
@eamonnCotrell, Hi first of, amazing video it's been very helpful. I do have a small problem. When i use the script and press execute it does everything it is supposed to but it gives me an error, " Exception: The parameters (String,String,(class)) don't match the method signature for CalendarApp.Calendar.createAllDayEvent. " i have checked the sheet and the value within the cell is a valid date (not a string). any ideas?
which of the four scripts are you using? This error usually happens when something like two dates is used but only one date is present because of the way I coded each example. does that make sense? ...I can take a look if you want to share your file with me (my full name at gmail)
Dear Eamonn, thank you for the demo sheet. I do not know anything about coding so this is very usefull to me. Weird though, I just copied the demo sheet and tried to run it for sample three but it only create the all day event with no description or guests. I didn't change anything. I only unchecked sample three from check boxes and of course pasted my calendar ID before that. Any suggestions? Greetings from Greece!
Hey there! Thanks for reaching out and that was my bad - it's probably defaulting to the first function instead of the one with options. I've added buttons in the sheet itself now that will trigger the needed function (oneDate, twoDates, withOptions, or recurrence). Check out the original sheet now and make a new copy to see what I mean. I should have added that to begin with! Have a great one!
Thanks. You 're great! Yes, I've noticed I was running wrong function. Anyway I adapted the sheet to my needs and it seems that works perfectly. Since I know very little about coding I was trying to write some with the help of OpenAI with no luck till I saw your videos, You reeeeealy doing great job. Thanks again!@@EamonnCottrell
If you are editing this code to work in a sheet you may already have note that to mark event to true use column plus whatever row number it should start on.
Hello, first of all thanks for the video this is exactly what I am looking for to get my life a bit more organized. However I am having some trouble. i get the events to show up in the calendar, but I also get an event for every row in the sheet. I have it set up so the dates are already shown in the sheet so I realized that this might be confusing the script as it would belive I want an event for this. A "no title" event. So i thought if I edit ypur filter to a -2 rather than -1 that would work, but it didn't so I am stuck. I appreciate any help I can get, if I find a solution I'll be sure to reply to myself, so if I haven't done that I am still looking for help :)
@@EamonnCottrell However I am now stuck at a new problem, not all days are filled with activities. What this means is that if for example all days are empty but the last row the script will se one row with data and check the first row of the eventlist, which is empty. It will proced with checking the checkbox and not creating an event (because the row is empty) This can be done again and the next row will be checked etc until it gets to a row with actual data. So far I haven't managed to get ChatGPT to help me :(
@@EamonnCottrell Soo my first comment keeps dissapering I don't know why, but I did solve this with some help from chatGPT I won't post the link to chatGPTs explanaition becuase maybe that is what is getting the comment removed but basically I had to change the filtering as I already had dates filled in for all days for the rest of the year. This I filtered out by changing 0,-1 to 1,-1 wich would filter out the first and last column aka the one with the dates and the one with the checkbox (in my particular setup)
@@lightningtech5706 I just emailed you back. The filtering got a little wonky on your example when I was testing stuff. I simplified and (I think) fixed the issues that I saw at least.
This is creating the events as per normal in calendars except for the all day events are not marked as whole day instead they are labelled 00:00 to 00:00 and the event name
This walkthrough may work better for you. I haven't combined the two yet, but I did do this one about setting specific times. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FxxPq2wXcK4.html
Hi Eamonn, I am running into the inability to run the script for a row in which has one date specified to denote an "all day event" and while allowing me to invite a guest. Is there a wraparound to this? When I try to run it as with options, I get the message, "Exception: The parameters (String,(class),String,(class)) don't match the method signature for CalendarApp.Calendar.createAllDayEvent."
Hey sorry about that! Yes, it's a minor edit on the function. You can remove the things in withOptions that you don't need. To have it just use a title, start date and to invite a guest, you would delete line 59 and then delete description: e[3], location:e[4], inside those curly braces. That way it will only try and use the guests and the sendInvites values. If you get lost trying to do that, feel free to email me (my full name at gmail)
Hey there, you just need to change the named range EventList so that it goes down to whatever row you need it too. I use that named range in the Apps Script to keep it simple, but I also only defined it through row 19 for the same reason. Go to data - named ranges and you can edit that range accordingly.
seems I'm getting errors when trying to run the scripts "TypeError: Cannot read properties of null (reading 'createAllDayEvent')" It's pointing to these two things (anonymous) @ Code.gs:14 oneDate @ Code.gs:11 when I try running the One Date option.
Hmm. So, I think it has to do with the named ranges. Did you copy the sheet with all the named ranges? And are they still pointing to where you have your event data? The "calendarID" range is what apps script is currently set up to read for the calendar id from B5 and the "EventList" named range is where it's currently set to pull all the events from B8:J19
@@EamonnCottrell Thanks for getting back to me so quickly! yes, everything is a one to one copy. All I've done is change the calendarID in cell B5. From what I can tell, every script runs fine except for "With Options"
@@BradKrivelow-g3cHuh. So it's still working on my end. If you want I can take a look at your file and see if I can figure out what's going on? You can share with my full name at gmail if you want.
@@BradKrivelow-g3cHuh. So it's still working on my end. If you want I can take a look at your file and see if I can figure out what's going on? You can share with my full name at gmail if you want.
