@@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.

Awesome, thank you!

Yay! You’re welcome

Awesome! Glad it helped

You’re welcome! Glad it was helpful!

Doing what I can, my friend 😁

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)

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!

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: kzfaq.info/get/bejne/sJeeq8py39WUY40.html

You’re welcome!

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.

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/

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

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.

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.

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/

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'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

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

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: kzfaq.info/get/bejne/Z5mEramovKu7qKs.html

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: kzfaq.info/get/bejne/j6qYn9Fk2tGsZ40.html

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: kzfaq.info/get/bejne/fKqbfqx33se9c2g.html The "with options" part starts at 6:33

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'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

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 👍

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: kzfaq.info/get/bejne/fKqbfqx33se9c2g.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.

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!

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

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 kzfaq.info/get/bejne/fKqbfqx33se9c2g.html

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.

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

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. 😀😀

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("")

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...

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.

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!

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

You’re welcome!

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.

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.

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!

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?

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

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.

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

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!

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

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!

All good. Just separate the email addresses with commas like I've done in the example sheet in G3: docs.google.com/spreadsheets/d/1o3qP4uWK8aZwFIhHW7aeCFu-tbOqOUkYsDqDBIiDdOk/edit?gid=0#gid=0

@@EamonnCottrell Thank you so much!

@@PhilipAceAm-is you're welcome!

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?

Just separate the email addresses with a comma (NOT a semi-colon).

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!

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. 👍👍

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

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.

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 👍👍

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...

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!

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!!

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!

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

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. 🤣

Did you set up the named range "events". I'm not for sure, but it sounds like nothing was added to those two variables (alreadyCreated and newEvents). I can take a look if you want to share your sheet with me (my full name at gmail)

@@EamonnCottrell I've shared it with you. I named the range (checked it again also) but still wasn't able to work it... somehow nothing appears even though it says "finished script"

@@DeborahCheah I think I've got it all working for you now. I'll email over the notes. Couple small things

@@EamonnCottrell Somehow I'm still getting an error message. I've emailed you on this...

@@DeborahCheah I think we're good now. Emailed you the solution. I don't have a good explanation but the date format being "mm/dd/yyyy" fixed it

It's just authorization required to run Apps Script the first time. Check this out: kzfaq.infokC53zCctVqw?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')

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.

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.

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.

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.

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

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.

Hmm. Did you edit the script? I can take a look at your file if you share with me (my full name at gmail)