(100% FREE) - Auto (Bulk) Update Google Calendar from Google Sheet
HTML-код
- Опубликовано: 26 июн 2024
- As sync large number of events to Google Calendar could be painful especially your events might updated from many collaborators at the same time.
In this video, I will show you using Google Sheet allow you to let collaborators to insert event by themselves, and using addons "Sheets2GCal" will auto update to the specific calendar.
👋 Resources called out in this video:
1) Google Calendar - ss88.my/g-calendar
2) Google Add-ons "Sheets2GCal" - ss88.my/gsheet-to-calendar
3) Google Sheet
⌚ Timestamps:
0:00 Introduction
1:30 Prepare event list at Excel
2:00 Create new calendar (optional)
2:55 Sign Up for "Sheets2GCal"
4:20 Create a blank spreadsheet
5:00 Get add-ons "Sheets2GCal"
6:18 Link sheet (to specific Google Calendar)
7:45 Copy data from Excel to Google Sheet
9:10 Update sheet data to Google Calendar
9:55 Done, check result
10:35 Wrap Up
🚩 Connect with me on social:
- ss88 (Social Bio Link) : ss88.my/u/technologysimplicity
- Facebook: / technologysimplicity
- Instagram: / technologysimplicity
- LinkedIN: / technology-simplicity
🔔 Subscribe to my RUclips channel
ss88.my/BVCg
🎬 Want to watch again? Navigate back to my RUclips channel quickly
ss88.my/ts-youtube
💝 Support me with your Google Play Book purchase ss88.my/pshX. (Purchasing through this link gives me a small commission to support videos on this channel -- the price to you is the same)
🤝Special thanks to the people who assisted me along the journey:
1) Transition Animation Powered by KenFX Studio 
Хобби
You're a blessing. Thank you!
Thank you so much for your compliment! 🤗
Thank u so much for the tutorial Jay. 👍🏻
You're most welcome Kanal 🤗
Presentation very good! If you're someone who likes using Google's products, then this is a simple but effective way of managing your appointments / tasks / work schedules etc, etc. for those who have to plan the year ahead.
Thanks Pete for your kind compliment :)
Hello Jay,
Thank you for the awesome tutorial. Keep up the good work
Hi Kisal!
Thank you so much for your kind words! :)
man thanks so much this is a life saver
Hi Lorena! Thank you for your kind words and I'm glad it helps :)
Thank you for this clear and helpfull tutorial!!!
Thank you so much for your kind words and compliment!
I'm really glad that you enjoyed the tutorial :)
Very Helpful, Thank You
Glad it was helpful, and thank you for the compliment! :)
Fantastic....👍👍 Very helpful & useful in many scheduling arrangements.
So nice of you with the compliments and it's my great pleasure to help!! :)
Thank you for this tutorial. :)
Glad it was helpful! And you're most welcome!!
Thank you very much!
You're most welcome and I'm glad you liked it!
Thank you for your support and stay subscribed to my channel :)
Thank you sir
Thanks alot ❤️ kudos to you. You helped me in such simplest way.
You're most welcome! 🤗 I'm glad that it's helpful.
I completed 15$ order with the exact help of this video.
thank you so much !!!
You're most welcome and I'm glad it help!
Your efforts helped me a lot. Thanks a ton ❤❤❤
Glad to hear that and happy to help!! Your compliment made my days!! :)
@@TechnologySimplicity You are most welcome.
very easy to understand
Glad to hear that!!
great
Hello Jay !
Thanks for this very helpful tutorial !
Just a question : do you know how i could put a function like "=if(C2="Oliver";"09:30";"")" to automatically set the start time to 9:30 (and end time at 19:00 with the same kind of function) when the subject is Oliver ? I tried it but the event results in a whole day event 🤔
Thanks for your help if you know 😊
Hi Olivier!
Very good question!!
Your command is correct and the issue occur because the sheet should be in "text" rather than "command" as when the script is running, programmatically it will detect in "text" which the cell's value, hence cannot detect the "Start Time" and "End Time".
My suggestion - duplicate the sheet somewhere, where you can run the command and fill up everything by itself first. Then copy all (from A2 to the end of your data) and paste as "Values ONLY" (or Ctrl + Shift + V).
Hope it will works for you 🤗
thank you for the tutorial. have you found the way to create "limited" recurring events ? i.e. teaching schedule for 10 times
Hi Indra, thank you for your kind words.
Unfortunately, it's the current limitation for the script/add-on now.
To have limited-times recurring events as per you mentioned, best is created the events on the sheets semi-manually (where you key in 1~3 times of the event and drag-down to auto calculate).
Hope my little explanation is helpful.
thank you it is really helpful, but i hope there is a tutorial for Microsoft Excel also
You're most welcome and let me know what you want to learn for Microsoft Excel and I shall try my best to produce the tutorial ya :)
Thank you for the tutorial!
is there any possibilities to set the guest email from the spreadsheet to gcal?
Thank you for the compliment :)
For the template, it's not possible to include guest's email. Most probably the creator want to avoid abusive of email spamming, perhaps?
Hello very good video. Is it possible to do it the other way around. So if i add an event in my calender it will automaticly go in sheets and if not do you know another way where that will be possible?
Hi Isac, thank you for the kind words. May I know what's your use case (example) to have "live sync" from Google Calendar to Google Sheet?
There are quite a number of solutions but depends on your requirement. Hope to hear from you so that I can share in more specific and near to your expectation.
How can I mark events as repeating in the g sheet that links to the calendar? I have events recurring at custom intervals - - specific business days of each month. I have this view built but need google calendar to recognize the events as grouped/repeating so I can update one event in google calendar and have it update the repeating events.
Hi there! Very good question! I found possible solution and I'm doing some testing as per the complication. IF you have some technical background, you can try it yourself too (link below will leads to the highlight section):
ss88.my/sheets2googlecalendar-repeat-column
Which then will actually leads you here (this might be very technical for others):
icalendar.org/iCalendar-RFC-5545/3-8-5-3-recurrence-rule.html
Let me do more testing before come back to you again!
Thanks for the tutorial, Jay. I have a question. Is it possible to set up notification reminder automatically when export the data to calendar?
Thanks for the compliment!
I get your point, you want to import data to calendar and have each event preset notification reminder. May I know are you looking at same reminder or different?
Can we have info on how to do both the same reminder and different?
Thanks a lot, is there a live update feature?
You're most welcome.
I presume "live update" means whenever there's new row of data at Google Sheet, it will auto push to Google Calendar? For the extension/script I sharing at the video, it doesn't work that way.
Let me know your scenario and perhaps I can suggest other workaround.
@@TechnologySimplicity I'm trying to set up appointments to load directly from sheets to calendar.
Very informative! But I need to keep the data on the sheet I'm linking. I entered the Google Calendar ID and clicked Link Sheet...what are the next steps?
Hi there! Thanks for the compliment!
You need to choose "Update calendar" after you clicked "Link Sheet". The way you can keep the data on the sheet.
IMPORTANT:
1. Anyway, to be SAFE, always try the step above (that will impact whole calendar and all data) at TEMPORARY-NEW calendar & sheet. Test it out before you publish/update to real calendar.
2. Always make copy of your sheet AND calendar before you make major changes is essential, just in case any disaster happen :)
Hope simple tips like this helps!
Is there any way to add event calender automatically if there any new input from google form response (Google sheet) ?
Hi there! Great question and indeed there are many ways, but not that straight forward as one of the consideration you need to pay attention to is what-if your Google form get spam or your team / clients submit wrongly, etc? Hence not straightforward ya. If you wish to learn, let me know again.
Really useful and interesting video. Does it also work in reverse? Let me explain: if I add a new appointment on Google calendar, will I also find it in Google sheets?
Jay?
Hi @valerio! Thank you for your kind words.
For now, it's one way where you push events from G.Sheet to Google Calendar.
May I know example scenario on why you needed the new calendar entry at Google Calendar to be appear at Google Sheet? Maybe I can suggest other workaround?
@@TechnologySimplicity Hello I have the same question. I want google sheets to add a new row and update everytime someone adds a new event to my google calendar. I am using the google sheet as a data base for my app so i essentially want to run the script so the app shows an updated list of current appointments for the user that has the app. I find several videos that explain the script from sheets to google cal , but I need the opposite, google cal events to google sheets . Theres an app scipt i have however I am not sure how to add a loop to tell it to update everytime a new event gets added to calendar. please help!!!
@@islaxlasislas Hi there! It's a great question and like the opposite scenario there! Please share with me your script and let me know what error/problem you faced so I can assist further! You may PM me via my page's IG. 😊
Hello Jay, Very good video....I have made a google form for leave application for my team. The data comes on to a google sheet. I have done everything you have shown and it works perfectly manualy. What I want is whenever there is an new form entry onto the sheet, it should get updated automatically to the calender. Can you help please? Thanks
Thanks for the kind words.
Sounds like a great integration you had there!!
Allow me to share my concern regarding your request. Don't you worry that there's not-to-approve application that automatically update to calendar? Or perhaps what's the main purpose you want it to be appear at calendar automatically?
Currently it's quite tricky to setup auto-trigger at Google Sheet (add-ons). I don't have solution for now but I can try to figure it out (no promise ya) :)
Let me know more so that I can assist further too!
Hello Jay, thanks for your quick reply.
I get a notification when the leave form is filled. What I want is,
to get notified about upcoming leaves of my staff before X days, which I can set in the calender. This way, I can plan work with least disruption during their absence. Thanks again
@@jayeshshah3381 Very good nice workflow you have there.
Anyway, for the current script, it doesn't support notification per event (yet).
However, the best workaround you can have is you can have "Daily Agenda" as a summary to reach your inbox for now.
Check this out:
support.google.com/a/users/answer/9308957?hl=en
Hope the little trick up there can serve your purpose (a bit?) :)
Is there a method to UPDATE (or change) the Location of the event(s) from a gSheet WITHOUT changing the other fields.
Hi there! Good question and with the current script limitation, you can't.
Btw, you can achieve it by exporting events from calendar, import to Google sheet, then modify the locations you need and resync. Hope it helps. Please try it in small scale before you did it for whole calendar, just in case attempt outcome doesn't fit your need.
Terima kasih
Sama sama and let me know here if you need further help :)
In my computer i cant do it!:
ScriptError: Its Necessary Autorization to do this operation. How can i autorize this?
Make sure your Google calendar and Google sheet that you're using is sign in using same Google account
Is it possible to run this from mobile?
Hi! Good question! Unfortunately there's no easy/direct way to run from mobile for now.
If necessary, you can trigger auto-run (like every 24 hours once) to run from Google Sheet. Hope this helps :)
@@TechnologySimplicity Hi ! could you show us how to realize it ?
Hi Olivier!
Thank you for the question and allow me to sorted out my time and tutorial video production materials. Shall produce another short video for this :)
Please stay tuned.
GOT PROBLEM
google sheet not allowed date after 2022
how to solve? TQia
ScriptError: Exception: The parameters (String,String,String) don't match the method signature for Utilities.formatDate.
Hi there!
Most probably your data format (date) is different with the required one.
If you're pasting from someone else like Excel, make sure you paste as text only so that the data will follow the format as per the template.
Let me know if this method not working or you need further assistance.
hi i cant see link options in google sheet
Hi there! Do you mind to share more about what is the "link options" you are looking at?
Saludos, me dio un spritError Exception ranger A10997- H10997 excedes grid límites máx row 10996
Hi Aaron, sorry I can't understand your question. Do you mind to share again in English? :)