Это видео недоступно.
Сожалеем об этом.
Apps Script - All Day Calendar Events in Google Sheets
HTML-код
- Опубликовано: 7 авг 2024
- Create all day calendar events with apps script from a Google Sheet.
This is a walkthrough of several variations of the CalendarApp method createAllDayEvent() and createAllDayEventSeries().
⭐ FREE sheet here - www.gotsheet.xyz/c/apps-scrip...
(I added four buttons to the sheet itself since recording to run any of the four functions I go over in the video.)
CalendarApp Class Documentation: developers.google.com/apps-sc...
We receive commission on purchases made through some links on this page
_______________________________________________
🎓Software Recommendations:
► Starting a Newsletter? Check out Beehiiv with a free trial and 20% off your first 3 months: www.beehiiv.com/?via=eamonn
► ConvertKit - creator marketing & newsletter platform - partners.convertkit.com/358pj...
► HubSpot free CRM software for sales, marketing, customer service, operations teams. hubspot.sjv.io/c/4186908/1001...
► Notion - productivity & note-taking app - affiliate.notion.so/p7771a8zwlsu
► Carrd - one page landing sites - try.carrd.co/s4x1c97b
► Coefficient - import data and sync with your business systems in Google Sheets - coefficient.io/?via=984f9f
_______________________________________________
🎓Data Course Suggestions: (coursera)
► Advanced Data Analytics Certificate by Google: imp.i384100.net/21dWGa
► Microsoft Power BI Data Analyst Certificate - imp.i384100.net/4PgjK3
►Microsoft Business Analyst Certificat - imp.i384100.net/anamAq
► Cybersecurity by Google: imp.i384100.net/VmN6oM
► Google Cloud Data Analytics Certificate - imp.i384100.net/B0ZJb0
_______________________________________________
🎓AI Course Suggestions: (coursera)
► IBM AI Developer Certificate - imp.i384100.net/B0ZR9L
► Overview IBM AI Engineer Certificate - imp.i384100.net/KjnyA9
► Google AI Essentials - www.coursera.org/learn/google...
_______________________________________________
🖥️ Office items I can't live without:
amzn.to/3uqSO7Q (amazon)
📲 Find me
on LinkedIn: / eamonncottrell
on Twitter: / eamonncottrell
_______________________________________________
⌚Chapter Time Markers:
00:00:00 - Intro
00:00:18 - Calendar Setup
00:01:38 - With Title and One Date
00:05:38 - With Start and End Dates
00:06:33 - With Options
00:08:13 - Recurrences
00:09:42 - Tell me why this is glitchy
=============================
#googlesheets #spreadsheets #dataanalytics #excel #productivity #lifehacks #appsscript
⭐⭐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!
Outstanding! You’re welcome! Glad it was helpful.
Thank you so much! I really need to learn this.
Awesome! You’re welcome!
Hi Eamonn, Thank you for this video, it solved me a huge problem I was facing!
Glad to hear that!
This is really helpful. Thank you.
Do you know if you can update the information on an event if you change it in the sheet after it's already created?
It takes a good deal more coding that I haven't gone over yet, but yes it is possible. I may add this to a future video.
Valeu! Thanks
Happy to help! You’re welcome, and thanks! 😊
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?
The last line in each of the four scripts should handle this: sheet.getRange("J"+ newIndex).setValue(true)
Is it not behaving properly on your sheet?
Is there a way to add a column with the color we want it to be and for some items to sync as all day and some sync as time bound in one list?
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.
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.
Thanks! Good to clarify 👍👍
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)
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. 😅
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!
ruclips.net/user/shortsRy7upQAaB_Q?feature=share
It's not letting me view the video it says it's private? Thank you for your help!!!!
@@Blessed24724 woops! check it out now... 😉
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
@@t.z.sagiroglou5036Fantastic! Glad you have it working now!
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
@@EamonnCottrell Thank you Eamonn! You helped me a lot in resolving this specific and important issue 👍
@@DmytroMaiboroda Happy to help!
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?
@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)
@@EamonnCottrell i have send you an email with the link to my sheet and some additional information. many thanks in advance
@@EamonnCottrell I have send you an email with the link to the sheet.
@@pieterdecuyper5252 checking it out today...will email you the fixes hopefully
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
Just did
@@michaelsglas Just emailed you. Looks like you copied the public url instead of the calendar id
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.
@@EamonnCottrell Thanks a lot!!
Hi, Eamonn. Great video. I have a problem, why does only let me use the function until the 19th row, how can i expand it?
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!
@@EamonnCottrell I am having issues finding (B8:J19) in the script. Do you know what line that would be in? Thank you so much!
@@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
Could you show a sample of creating all day events and events with time within the same script pls?
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. ruclips.net/video/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)
hi @eamonnCottrell the script wont work from row 19,can u help me?
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"
@@user-zv5vv2mu4kHuh. 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.
@@user-zv5vv2mu4kHuh. 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.
please make a video using google task appscript i got an error adding time using app script