Using Google Sheets to Update Calendars
HTML-код
- Опубликовано: 17 сен 2024
- 1/28/2022 - Seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google - cloud.google.c...
I haven't done this myself though so I won't be able to answer any questions about it.
I walk through how to set up the FormMule script and create your first event.
Here is the original FormMule Script document-
docs.google.co...
Here's a video on how to make changes to your entries after you've run the merge: • Revising FormMule Cale...
===========================
I’m Thom Gibson.
🍎 I teach middle school math, science, and robotics.
🎥Video & 💻Computer Equipment I use - www.amazon.com...
*contains affiliate links*
⏱ What my channel is about in 30 seconds - • A Teacher / Filmmaker'...
💰'How to Teach Kids About Money' online course for teachers - thomgibson.com/...
💻'Create Successful Videos for Distance Learning' 1-hour online course for teachers - thomgibson.com/...
🎤Contact me for speaking / PD training - thom@thomgibson.com
================
Find me on social media :)
Twitter - / gibsonedu
Instagram - / gibsoneducation
Website - thomgibson.com
It seems this script may not be working anymore.
I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google - cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Looks like there's an add-in called Sheets2GCal. It's free up to a certain limit, requires sharing access to your Google Account. Tutorial here: ruclips.net/video/UcRbJB9U-io/видео.html
Dude, if you're ever in New Jersey, beer is on me...I cannot thank you enough for this helpful tool....Bravo, Bravo, Bravo.
I'll let you know Ira! I'm sure the offer still stands 6 years later. Haha.
Great hack! This is better than any premium service promising to sync Calendar and Sheets
totally get it... Just thought you might have heard this one before.. I didn't add anything. but I'll keep playing and see if I can figure it out.. I use Sunshine for my calendar, so it does display the description where I want it. (i'm getting there!)... this is super helpful. thanks!
For up!
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Event-O-Matic recommended in the description works amazingly well, and they don't even charge for it! Thank you so much!!
Can you just tell me Event-o-matic is an add-on or any script can you guide me please?
@@AnkitKumar-pk8tk it's an add-on. There's a link to it in the description of the video.
@@wyattc9984 thank you sir
But it is not good as formule scripts every time I have to setup event o matic it didn't automatically update my calendar whenever I create a new row
Do you have any idea of other add-on or any other script which automatically update my calendar whenever I create a new row
Btw thanks for your reply
See the description for an update on this method
Instead of "make a copy" go to the document URL you link, find the part that starts "/edit...garbledeygook" and replace with "/copy" and that forces a copy. As you've helped me this way, I'll help you that way.
Thanks Joshua!
This is really helpful. Thank you. If you come to Cambodia in the next few years, drinks are on me.
Haha, fantastic!
Thank you for this video! This was very useful.
I had a question though. Would it duplicate entries every time you run the merge for a new event?
Hey there, thanks for watching! No, it won't duplicate. There is a column that fills in with the event ID once it's been created so it knows not to create it again. I have another video on how to make changes to previously posted entries :)
+GibsonEdu thank you! :)
Simply superb.. thanks for sharing..
👍🏼👍🏼👍🏼
Awesome information and great tutorial. Thanks for sharing this!!!
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Thank you. Very educational.
Can't seem to get the html tags to not show up on the calendar though.
Do you need to put tags in?
I was just trying to
+Jeff Layden
Hey Jeff. I think the html tags will show up if you're viewing through calendar.google.com or the Google Calendar or Apple Calendar apps but if you're looking through an embedded calendar, it won't. If you won't really be using embedded calendars, it may not be worth it.
Hey, Form mule stopped working for some reason :((( Says "UiApp has been deprecated. Please use HtmlService instead"
Hm...I don't really use formMule anymore so I'm not sure. Sorry :(
Hi Thom, I get the message at the very beginning that I need access permission to do step 1. can you help?
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Mr. Gibson,
I am trying to figure out a way to use this as an appointment scheduler. My Column A has the "date" that I prefilled. My Column B is "time block" for the appointment that is prefilled. My Column C is titled "Car ID." Is there a way to set it up that once someone inputs their information in Column C / Car ID that it creates an event based on the date in Column A and time in B?
I've tried a few times but the start time & date in the preview shows: "Sat Dec 30 1899 08:30:00 GMT-0500 (EST)"
The date issue may be fixable under file - spreadsheet settings and setting it to the right time zone.
Not sure if you can automatically populate the calendar whenever someone fills in the spreadsheet (instead of manually performing the merge)
When using a start time and end time, the time is assumed to be in GMT, and then it is converted into EDT when the manual merge takes place. Can this work for other time-zones?
The same is happening to me, its using Eastern time I need PST.
Hi Thom, I have same question. It will be of immense help if you can throw light on this issue. Thanks :)
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Thanks Thom, however Richard has just the date, if is like to add the DATE as well as the time do I need to put a semi colon or something between the codes? If I don't add the time it creates an all day event even if that option is unchecked!
+Thembi Mhlanga I understand. If you type the date and time into the same cell, it should work. For example, I wrote 1/8/2016 1:00pm and then it populated the cell with 01/08/2016 13:00:00 (you can see in this picture here - drive.google.com/file/d/0B3N0pN7tvWcBdDlUZlpCckxfWTA/view?usp=sharing)
Great info! Thanks so much. One question; Is there a way to auto generate the event tab color on the calendar? I would like to create one color for Due Date and another color for Appointment Date. (I apologize if you have already answered this question. I scrolled through the comments and didn't see it but may have overlooked it.) Thanks so much!!!
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Thanks for this video. I enter my wife's work schedule into her calendar for her and while I'm not sure if this will actually be any faster it does allow me to calculate things like daily and weekly hours which can then of course be added right into the description. Pretty neat. If I can just find a more efficient way of entering the date/time line... I did notice that a couple of things don't seem to work like they did when you recorded this. For example, I was unable to use the bold command in the description, it would literally enter those tags in the field as well as a tag for a line break. Also, I could not copy the file the way you did as it would only store it in the origin folders. I had to add the file to My Drive and then right click it to make a copy.
Hey Steven. Yeah the HTML tags only show up in an embedded calendar unfortunately. If I go to calendars.google.com and click on an event, I too will just see the line break and bold tags. As far as the time, you can make two columns. One start and one end, but they both have to have the date and time.
Start: 01/01/2017 08:00am
End: 01/01/2017 10:00am
Then uncheck 'all day' when setting it up. Hope that helps!
I had that but I was looking for an easier way to enter the date time. I was hoping I could make military-style date time group (yyyymmddhhmm) work but even setting the custom format and then trying to get another cell equal to that but in the format the calendar needs didn't work. I ended up just making a basic form for start and end time with date picker and time and then copying those entries to the correct cells. That works fine. Also, on our phones the description doesn't seem to be displayed until you tap on an item so I just moved the data I want shown to the location field which is a good work around in this case.
Very nicely done video - sorry that the script no longer works. I will try the other suggestion. Thank you!
Yeah it's a bummer. Have heard the new one that's linked in the description is good. Let me know if it works for you!
I make a copy of your file and generally it work well. However, I am
unable to cause the "Event Description" to show up on the calendar like
how you did it in the tutorial. Is there anyway that I can do? Thank
you. Please help on this
Hey there Rohit. Could you take a screenshot of your spreadsheet as well as the how you filled in the info on 'set up calendar merge'
Thanks for the video. Im not sure if you've already answered this question, but do I have to manually run the merge every time i add an entry
I believe so. Unless this spreadsheet is where the answers from a Google Form populate, then I think you can check the box 'trigger event creation on form submit' but I haven't tried that before.
First of all, EXCELLENT TUTORIAL. I have a couple of questions though if you could help WOULD BE AWESOME. I am using the time for the appointment and it seems the system automatically is using EASTERN TIME but I am in the Pacific, is there anyway to change that please? And second, can the system add the appointments automatically or I have to keep pressing the PREVIEW AND PERFORM MANUAL MERGE option? THANK YOU AGAIN.
Hey there Beisbol-Baseball. I've pasted a response that +andreas persson had about this as I haven't played around with it much:
"I noticed that in the sheet-settings the sheet GMT was -05:00
changed it to My GMT (+1) and it got better on sync :-) "
Not sure if you can set it up so that it automatically populates the calendar without you performing the merge. It may work if someone fills out a form that populates this spreadsheet, and then you have 'trigger event creation on form submission' checked on the calendar merge set up.
+Thom Gibson thank you very much, I found the answer yesterday online. still thank you very much for your responses to all of us and thank you for the terrific tutorial and spreadsheet.
When I run step 1 it says "You do not have permission to perform that action"
Why is that?
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Hi Thom,
Thank you so much for this video, it really helped me solve a problem. I was wondering if you know how to include in the excel document conditional formatting that will change color based on a few different categories that will export into the calendar in the same colors?
Hey Erin. I believe if you do the conditional formatting, it shouldn't effect anything regarding the export to the calendar. There's a video on conditional formatting for sheets here: ruclips.net/video/Wub2uL_z_Pg/видео.html
I have an error "UiApp has been deprecated. Please use HtmlService instead." any tips?
Did you find a fix for this? Just found this video and got the same error message
See the description for updates on this method.
Thom, very informative, thanks for posting this. I found it looking for something related and maybe you might further my journey. I have a few employees that work out in the field. I created a google sheets form for them to enter their work time, very basic, start, lunch start/lunch stop, end time and a few specifics like location and type of work from a checkbox. It works fine, they can enter from their phone, and I can manually get the totals to audit before adding to payroll input. I was wondering if I could have the entries populate their google calendar and they could their hours worked and maybe a week to date total. Could you visualize your tool helping accomplish this, or if not any suggestions?
Hey John. The only way I think it would work is if you created a separate calendar for each employee. Each spreadsheet can only populate one calendar, and unless you want them all to see each others work hours, they would all need their own calendar with it's own spreadsheet. May be pretty time consuming to set up depending on how many employees you have.
Another option is to have them fill out a Google Form, and then on the Google Sheets response page, install the FormMule add on (different than the FormMule script I'm using in the video above. Confusing, i know). The add-on can be found here:
chrome.google.com/webstore/detail/formmule-email-merge-util/kabhamaiihmaphgpijdolgcihofefajn?hl=en
RUclips how to set it up but basically you can have them get a weekly email that shows all the entries they put that week. There is probably a way to have it add up the hours the entered but I'm not sure. One of the fields on the Google Form would have to be the email that they want the information sent to. Take a look at it and let me know how it goes.
Hello, because you said u can only link 1event per sheet.. so I wonder can I link 1sheet for whole month event? For example, i want to link my sheet for a month and I want to link the event according to table I have set on sheets. Is it possible to do?
P.S. English is not my native tongue. Sorry if not understand my question
Hello fhy, I'm not sure if that's doable. I know many people have been having trouble with this script recently and I'm not sure if it will even really be effective soon. Try using this tool called Event-o-Matic that does the same thing: chrome.google.com/webstore/de...
I haven't used it myself though so won't be able to answer questions about it.
Thank you very much. It is really very helpfull.
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Hi Thom,
Thanks for the video. I'm having trouble to set the start and end time. It somehow automatically refer to December 30, 1899 instead of the day the calendar was made for.
Do you have any solution for that?
Hey Edward, I've pasted a response that +andreas persson had about this:
"I noticed that in the sheet-settings the sheet GMT was -05:00
changed it to My GMT (+1) and it got better on sync :-) "
Somewhere I found my questions answered was the Google Plus Community for FormMule. Check it out here and I bet someone there will be able to help you :) Sorry I couldn't be of more help!
plus.google.com/communities/104966983745456719075
To be clear I have start date and start times as I'm creating an executive calendar and the times of events are essential
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
I noticed in your video that your calendar displays the "Description" right from the firm moment you click on a calendar even. How did you get the daily event to display like that?
+John Cavernelis That will only work if you're looking at an embedded calendar. If you're looking at the calendar on calendar.google.com or through any calendar app, it won't pop up like that.
Thanks for this tutorial! Is it possible to amend the content in Google Sheet (Example: Begin 4.4 Reading change to Begin 10.10 Reading) and had it automatically update the changes in Google Calender? Thanks!
Hey Heawie, have you gotten a chance to get through the whole tutorial successfully? Most people have said this particular script doesn't work anymore but if you look in the description of the video, I have a link to another tool that can do something similar, but I haven't tried it myself yet.
Hi Thom, It will be of immense help if you can throw light on this issue. Thanks :)
When using a start time and end time, the time is assumed to be in GMT,
and then it is converted into EDT when the manual merge takes place. Can
this work for other time-zones?
I believe you change your time zone in File- Settings. That should fix the issue.
My description is showing up like this: CLIENT: PHOTOSHELTERSTRATEGISTCHRISTINA PAPALE... And doesn't display in the card as you showed. (Instead you have to click through the item and see the description - which reads like above. Thoughts on this one?
+Susan Solomon Hm, did you put that / in the bracket? Usually if you want it to go to the next line, it's just . But may still just show up when you look at the un-embedded calendar. Hard for me to tell without seeing what's there though.
Is it possible to have a column formatted in such a way that it can send an invite to somebody i already have linked to the calendar? currently it requests all i have input their email in the step 2b setup calendar merge - Guests section, but would be nice if only the name i input in a column got the invite.
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
This won't let me go past the app not being verified screen. It's asking me to fill out a form, but I don't believe the form applies to me. Can you help?
I also need an answer to this question; commenting to keep track
Actually I figured it out; you'll try to allow it, get the verified screen and click "Advanced" then go to the link it provides marked "(unsafe)" and click "allow"
Thanks for the tip. I’ve never gotten than message so this will help others.
Thom,
It keeps showing all of the HTML coding in the "description" area (e.g when I view it from calendar.google.com and my mobile app. Is there anyway to do it so that doesn't show up or does it only work on an embedded calendar?
+Tinashe Chaponda I haven't figured out a way unfortunately :/
Thank you Thom! I appreciaite you responding.
Very Useful, but have one question that why description on appearing on even in the month calendar view
Description only shows up in an embedded calendar unfortunately. You'll have to open the even up to see the description anywhere else.
Hey Thom, thanks for the video. I needed help with something which I guess is more complicated than this. I have a spreadsheet where I have rows of data with columns in the following format- "Orderno., Name, Address, Amount payable,...., Start date, next installment (month1), next installment (month2),.., installment(month9). Its basically to keep track of clients taking products on rent and I needed the merge of the spreadsheet to my google calendar such that I get a reminder of order number, name and amount to be collected on that particular date for every order. I tried but wasn't able to get anywhere. Is it possible?
If each installment is paid on the same date of the month, perhaps you could use the 'repeat for how many weeks' option and just put '9' in there and make sure to set the reminder type. Just make sure under the 'event description' you have the information you want (like on mine I put 'due', 'in class' and 'homework', you would put 'name' and 'amount to be collected'. Let me know if that works.
I can't run step 1 : define merge source setting.. "the UIApp has been deprecated" show in my computer.
why this happen mr Thom?
See my pinned comment on this video as well as the description.
Awesome!
Thanks for sharing!
Thanks Canaan!
Nice video Thomas, quick question, I see that the start dates and times have to be formatted as the DateTime format in the sheet, my problem is I am copying and pasting game schedules from the web to this spreadsheet and the dates and times are separate columns, do i have to manually change the start time of the game to include the date from the other column? is there a formula or a merge shortcut.... i have over 2000 games to schedule
Hey Mike. Hm, I don't know of any way to merge two cells and combine the date and time. In the 'start time' part of the setup, you can try to put the date column value followed by the time column value but I have a feeling it wont work as even in the instructions, it says 'must be a spreadsheet formatted date/time value.'
I did a quick Google search on combining the cells and found this: www.excelforum.com/excel-general/797328-combine-date-and-time-in-a-single-cell.html It's for Excel but may work in Sheets. Good luck!
ty for the quick response......so, i've found that if you just simply add the two columns together (Date + Start Time) it provides you the datetime formula result.. that's pretty easy...the problem now though, is that even if I type out the start time (or use the addition formula) as say 06/09/2017 19:15:00 the script still states that it is an invalid date and won't create the event in calendar.........
I created a whole new spreadsheet and this time deleted the comma column.... now everything works..... thanks so much for the help!!! this will be a HUGE help next hockey season
Excellent!
Hi Thom! Just wanted to know can I merge multiple sheets from the same book to the calendar? As of now, only data in the first sheet is merging. Thanks!
Hey Sanica, I don't think that's doable. This is a pretty old script and the developers aren't even working on it anymore so it likely won't come but I imagine it's just a matter of time before a more well developed add-on comes around that does this well.
Thom Gibson thanks for letting me know!
I'm having trouble. The merge does not work and under the Event Creation Status column it says "Create calendar event failed: Exception: Action not allowed" Any ideas?
+Robert Calahan Hey there Robert. There may be several factors. Could you take a screenshot of the setup page as well as your spreadsheet with the info filled out. If you put them in a Google Drive folder, share it, and post a link here I can take a look.
Hello +Thom Gibson. Thank you for a fantastic tool! I have two issues which I can't find a solution for. 1) The tool doesn't update cells when I make some changes to existing data, 2) It doesn't add a new entry, 3) It doesn't delete event when it's completed. Generally, I have to delete (each time I make a small change in data) columns: "Event Id", "Event Creation Status", "Event Update Status" (this one is always empty even if I make some changes in data), I have to "Set up Calendar Merge" and "Preview and perform manual merge". Is there a way to update everything automatically? Thanks!
The same questions, it's able to create new event but update & delete
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Thom, thank you for video, but I'm so confused about firs step: I'm doing cope of the document, but I do not have a "formMule" on my toolbar. What should I do to get this?
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Hi Thom, thank you for this video I have been trying to do this for weeks and I couldn't figure it out. I just had one question, when I go to insert a time it keeps coming up Sat December 1899 and then the time. How do I make it just say the time?
Joseph Eliah
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
I make a copy of your file and generally it work well. However, I am unable to cause the "Event Description" to show up on the calendar like how you did it in the tutorial. Is there anyway that I can do? Thank you
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
This is amazing, help me, so much. Thank you!! You made your event an ALL DAY event, please tell me how to put a start date and time in the calendar merge set up? I can only get the date?
+Thembi Mhlanga Hey Thembi. Make sure 'all day event' is not checked and then you can put the times in. You can see an example in this tutorial here- ruclips.net/video/GKOaWvgMGBE/видео.htmlm44s
I'm working at association that is acts as co-operation organization to the other local associations, my job is like an handyman, I'm also updating their website when needed. Now i need to make space booking calendar that people can use.
I've already made 2 calendars for the spaces, how can i add both calendars to a new calendar that updates when when either 2 are updates as thelocal associations reserve the spaces for their meetings, etc. etc...
What do i need to do ?? I'm pretty new as a Google Calendar user..
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Hey folks, here are a few of the FAQ regarding this:
Q- When using a start time and end time, the time is assumed to be in GMT,
and then it is converted into EDT when the manual merge takes place. Can
this work for other time-zones?
A- You change your time zone in File- Settings. That should fix the issue.
Q- I set up the calendar merge exactly like you, yet on the Event description in my agenda, the informations are shown between /> and 1981
Hi, there Thom. Thanks so much for this tutorial, really just amazing. I have this problem, if I'm seeing the calendar in calendars.google.com can I see the description? It's not showing in mine.
Hi Thom, I tried the method u said, but the date shown in the calendar was still different with the one I typed but remained the EDT time, may I know how to solve it?
UiApp has been deprecated. Please use HtmlService instead man PLS fix this!! i realy need this...
Hm not sure what that means :( Apologies.
@@gibsonedu All well the problem was solved by the re-creation of the document.Thanks again for your script, it is beautiful and helps me a lot.
Hi. Please Help-me Form mule stopped working for some reason Says "UiApp has been deprecated. Please use HtmlService instead"
See the description of the video for details on this.
I have a running script to add sheet info to calendar. However, I only want to add the newly inputted data, not the entire sheet. Any thoughts?
See the description for updates on this method.
No longer works any updates?
Not that I know of. Check the description of the video.
i am getting the following error TypeError: Cannot call method "getLast Row" of null. What does this mean and how do I fix it? the schedule was working fine then after adding more events and updating a few this message appeared and I'm unable to get my changes uploaded into google calendar.
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
This is great! Just one question... I am struggling to get the time slot to work! it seems to only use GMT-4 is there a way to use GMT0 London as this is what my sheet and calendar are based on. Look forward to your comments :-)
+Ernesto Sousa Hey there Ernesto. I've never worked with changing the time zone stuff but if you go to this Google+ community and ask your question, it'll very likely get answered (the creator of the script is on there frequently).
plus.google.com/u/2/communities/104966983745456719075
+Ernesto Sousa
I notised that in teh sheet-settings the sheet GMT was -05:00
changed it to My GMT (+1) and it got better on sync :-)
Hello! Could help me, everything should be written right in google sheet, but it shows all the dates one day earlier in calendar... what could I do to fix it?
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Thom, I am trying to add the link you gave to an already existing google workbook I have. Once i do that, i loose the FormMule toolbar. How do i resolve this? I tried adding FormMule as an add on, but cant figure out how to get to the calendar options. I just get the email form options. Thanks!
Hey Kyla. It's kind of confusing; the add-on called formmule is different than this formmule script. Use the link I have in the description and copy the data from your spreadsheet to this new spreadsheet that has the script in it.
I copied the data to your spreadsheet. Now i am trying to run the script so it will merge and i am getting this error : TypeError: Cannot call method "getLastRow" of null.
Also, when updating do we have to merge it for every line item or is there a way to merge them all at once. I managed to fix my error problem. But i am merging them one by one.
Sorry, full of questions. Is there a way to set up the reminders in a better manner? It only gave me the option for minutes, and i need weeks or days. I dont think the minutes will work when i just did the math to convert them to days and weeks. I know i can go into calendars and manually change them all. But, is there a way with the formmule set up?
Hey Thom,
Would you know a reason for why the calendar ID wouldn't work?
Thanks!
Hm, I'm not sure. I guess make sure you have writing privileges on that calendar and that you haven't included any spaces before or after the ID.
Thom, can you post a copy of the link for the formMule Scrip you spoke of in the beginning of the video. I only can access the email merge utility. Thanks, Sorry if previously posted..... I cannot find it.
Hey Lesa. I have a link in the description of the video :)
The video was great and easy to follow - even for a beginner like myself. However, is it possible to to the the opposite, so it takes data from Google Calendar and puts it in Google Sheets? I need to print out an agenda each week and make it as automatic as possible.
Unfortunately I don't know a way to make it work in the opposite direction.
Thanks for the speediness! I figured it out! Now trying to figure out why my calendar display isn't as pretty as yours...:) Doesn't show the details like yours does...
+Susan Solomon Yeah the bold text only shows up like that if it's an embedded calendar. Whenever I'm looking at my calendars at calendar.google.com or through any calendar app, I see all the HTML tags like TITLE instead of the word TITLE being in bold.
how can you update the google sheets calandar template to 2018? I only see the 2017 and I can't figure out how to update it for 2018
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
How do I get the script to not re-add events to the calendar that have already been added? I have row 2 with info that was added to my calendar. I added row 3 info for a second event and when I preview and perform merge for the row 3 info, it wants to create 2 events. One for the row 2 that has already been added and another event for the new info on row 3.
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Hi, i have one problem with the script, it could not create the event, on the Event Creation Status says "Create calendar event failed: Exception: Action not Allowed", I don't understand why, the calendar and the spreadsheets are on my same google account
+Daniel García Hey Daniel. Hm, not sure. Try posting your inquiry in this Google Plus community (I've had a lot of my questions answered there) - plus.google.com/communities/104966983745456719075
"UiApp has been deprecated. Please use HtmlService instead." How can I fix this ? Thanks
Hm, I don't really use formMule anymore so I'm not sure how to fix that. Apologies.
Hey José, did you find a fix for this? I have just found this video and got the same message myself
@@DavidWinterDrums ho god it was long time ago... i re do the entire code by myself and it is not that complicated
Hi there, just wondering if I can link two separate spreadsheets to the same calendar, and if so how? Thanks in advance!
Hm, it doesn't seem so. On Step 1- Define Merge Source Setting it asks which sheet you'd like to look at, but I dont' think you have have it pull from two at a time. Only other option is just to create a whole other spreadsheet and link that one to the same calendar as the first one you created.
Sir this event o matic is not good as your scripts can you update your script or recommend any other add-on which automatically create event in my calendar whenever I create a new row
Please I really need this
Hey Ankit. It’s not my script. Please see description for alternative tool.
How would I apply this functionality to an existing sheet? I can't recreate it since it is a shared sheet, but want to add the sheet calendar to a shared calendar
+Samantha I don't think you can apply it to an existing sheet as the script has to be built into it (which is that link in the description). Could you make a new one, copy the information from the other sheet, and share it with the same people?
+GibsonEdu I am not sure I can recreate, but will try. Thank you for the response.
Hi Thom I got the error: TypeError: Cannot call method "getLastColumn" of null. how can I solve it because it stays loading and doesn't merge.
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
I have installed FormMule and it is working, however despite my spreadsheet settings (UK) the date the manual merge is throwing up is 30th December 1899, whereas the date I am using is 27th January 2016. Have you any ideas why this is happening and how to fix it?
+Iwona Batterberry Hey there Iwona. I've pasted a response that +andreas persson had about this as I haven't played around with it much:
I noticed that in the sheet-settings the sheet GMT was -05:00
changed it to My GMT (+1) and it got better on sync :-)
But of course you said the spreadsheet settings are set for the UK so I'm not sure why it would do that.
Somewhere I found my questions answered was the Google Plus Community for FormMule. Check it out here and I bet someone there will be able to help you :) Sorry I couldn't be of more help!
plus.google.com/communities/104966983745456719075
Thanks, I will take a look at that. I really appreciate your response.
Hey, how can you make multiple events one with started and ended date and other with all day, is there a way to do it?
Hm, I don't think you can do that. You may just have to do 12am-11:59pm for all day events unfortunately.
Nice for a Sheets-to-Calendar set up, but what about something to work the other way round?
Unfortunately I don't know of any way to work in the opposite direction :/
Hi Thom,
Is there any way to change the time zone? I'm in CST timezone but every time I run a manual calendar merge the time shows up as EST. Where would I change that? Thanks in advance and great job on this, you saved me a lot of work!
Ha, I should have read your comment that you pinned! Got the issue fixed, thanks again!
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Great video! I tried to replicate this but this script is no longer supported. Do you have an alternative tool you use? You listed Event-o-Matic, have you had success with it?
Check the description of this video for an alternative 😃
Hey Thom, this is a great tool. Is there a max capacity for how many events you can create in one batch? I'm trying to use this to create a calendar with several hundred events and it only seems to be doing a hundred or so at a time.
And if there is a max capacity, does that same cap also apply when you update events?
@@N3xtP13as3 Hey Ryan, I'm not sure as I've never done more than 100. Sorry about that!
Where do you set the "End Time"? There is only a Start time in my formmule setup?
Uncheck 'all day event' in the setup page and it should pop up. When you write the start and end time, put the date and time for both. So if it's Mar 26, 2017 8am-10am, put 4/26/17 08:00 as start time and 4/26/17 10:00 as the end time.
Thom, I need to go back and make changes to the merge I did earlier today. How do I retrieve the file where i put all my data to do the update?
+Carletta Cunningham Hey Carletta. Check out this video I made on making changes. Let me know if it doesn't answer your question. ruclips.net/video/J6R69jZoU9g/видео.html
Great Video...One question, my sons teacher uses Google Sheets for her Assignments etc. I want to take what she has online and import it into my google calendar, Is that possible or do I have to get her to do this from her program?
If her sheet for the year is already complete for the year, you could copy it into a spreadsheet with this script and do it yourself. But that's probably unlikely as things are always changing in the classroom. She would have to do this to the sheet she is working on.
You could add an import script to your spreadsheet that has the FormMule script so her spreadsheet imports to your spreadsheet. You can then add a trigger to update this OnOpen.
This is awesome! Any idea how to set it up so that it takes the headers from row 11 instead of row 1?
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
hey,, why i get this line "You do not have permission to perform that action."
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Hey! Thank u very much for this video and sript! Almost everything work perfect except time at calendar. I'm from Russia. I made my events with time for start and time for end for each event.But it appear at calendar with in 8 hours later, then I tape at my sheet. F.ex. I tape 13:00 and event appear at 21:00. Could you please help me?
+dkubasova I haven't had to work with the time zone stuff but I was able to get a lot of my answers questions in this Google Plus community - plus.google.com/communities/104966983745456719075
The creator of FormMule is in there are lot, and I would also reach out to +Richard Anderson who made this video: ruclips.net/video/GKOaWvgMGBE/видео.html
Sorry I couldn't be of more help!
I keep getting this message every time i run the merge: (The set up looks fine.)
Event Id Event Creation Status
Create calendar event failed: Exception: Action not allowed
Any suggestions? What step did I miss?
+Susan Solomon Hm, hard to say. Try posting your inquiry in this Google Plus community (I've had a lot of my questions answered there) - plus.google.com/communities/104966983745456719075
The developers of the script post on there often and may have a bit more insight to what may be going wrong. Sorry couldn't be of more help :(
I am having issues with the formMule link. It is saying that I only have viewing permission and must get owner permission to access it. I am therefore unable to download the link.
Hm, I have it set to public. You wont' have permission to edit it but if you click on 'File' 'Make A Copy' you can have your own copy to edit.
Have tried it on a fake account and have done 'Step 1: Define merge source settings' The "loading" donkey has been on the screen for about 3 min now...not sure what to do. Please help?
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Whenever I add text to the description portion the format stays in HTML. How do I change this?
Unfortunately, the html tags will only show up if you're looking at it in an embedded calendar (like on a website). Otherwise, you will just see the html written out.
This is awesome. I'm wondering if I could adapt it into a vacation request calendar for my 40+ employees, so I can share with them to create transparency?
Hm, I'm not sure. Haven't used this script in a few years.
we do vacation rental management. would this be a good application for this?
Yeah, perhaps have a Google Form and then apply the results to a spreadsheet with the FormMule script (found in the description). It wouldn't automatically put the events on the calendar, you'd still have to run the merge.
That is almost excatly what i am doing with it and it works really great.
Hi, I have an issue with inputting in a time to the west coast. Everything works but the time I enter in shows up as if it is Eastern time on my calendar even though I am on the west coast. Is there a way to fix this?
+waterangel1287 Hey there +waterangel1287. I've pasted a response that +andreas persson had about this as I haven't played around with it much:
I noticed that in the sheet-settings the sheet GMT was -05:00
changed it to My GMT (+1) and it got better on sync :-)
+Thom Gibson thank you! I checked the sheet settings and it was set not set to PST. Thanks for the help!
+waterangel1287 : I'm on the west coast too, did you make the change in the script editor or elsewhere?
It's under File --> Spreadsheet Settings.
Related: After changing the spreadsheet settings, the event is created at the correct time, but my email merge still lists it as EDT; know of any way to fix that as well? Thanks!
Awesome video!
How often does the script update the events to calendar? I waited for 20 mins but nothing happened. any ideas?
Hm, should populate after about 5 seconds or so.
Hey Thom,
I have been working with the sheet but still no update.
Would i need to make a change with the triggers? Should I make a new copy of the sheet?
Any Ideas?
that's far too complicated. there's also a new version of google sheets/diary. would you set my sheets to create diary events for me? :)
This video is very old and it was the only way to do it at the time. Good luck with your sheets diary though!
Hi Thom Gibson, when i update the google sheets contents then it makes a duplicate in the calendar, is there a way to prevent duplicates when changing the google sheet. Thank you for this amazing video, its helping me alot, i look forward to your responce
Hey Nate, check the second video (linked in the description) on how to update the sheets and that should fix your duplication problem.
@@gibsonedu Thanks alot Thom, this really helps alot, ill check out your other vids and subscribe
@@gibsonedu Thanks alot Thom, this really helps alot, ill check out your other vids and subscribe
What about recurring events? Could we have the script create recurring events in Calendar?
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Hola Thom buen dia, todavia esta funcionando tu link, por que me pide mandar permiso para usar la hoja y despues no se como añadirlo como complemento
Buenas dias Pedro. Clic en el link y clic FILE y CREATE A COPY para crear una copia que puedes editarlo (lo siento por mi espanol!)
Hi! First of all thank you! Is very helpfull. Just one question, everything works fine but it looks like every time 'im adding a column then I'm trying to setup calendar (on an already working calendar with my events) it says "TypeError:Cannot call method"getLastColumn" Of null" ...
Any idea ?
Many thanks
Also how you do to have this kind of visual for your event ? I'M kind of stock with the default one I guess..
Hm, perhaps you could send me some screenshots of how you have 'set up calendar merge' settings.
I figured out .. But I still don't have the visual you have with the description ... In fact I can't see the description unless I open the event on my calendar. Do you have an email to send you a screenshot ?
Yeah, that description stuff only shows up in an embedded calendar. If you're viewing it from calendars.google.com or any calendar app, you'll have to click on the description to see what's in there. Kind of dumb. Only reason I do it though is because I embed the calendars on my class site.
what did you do for this...? I have the same issue
I keeP getting after every one of my column values in the description box. What am I doing wrong?
+Evergreen Tractor Hm, are you just pushing enter in the cell that is defining what will go in the description box? Also, where are you viewing your calendar (app, calendar.google.com, embedded calendar?)
Hi Thom, thanks so much for this going to make my life much easier. However you only show how to create an all day event. please could you tell me how to enter the an event at certain times. Every time I try to enter a date and or a time this I have been told its an invalid date. Desperately looking for some help.Thankyou
Uncheck all day and make a column for start time and a column for end time. Include the date and time in them. Ex: start - Feb 05 2016, 08:00am end - Fev 05 2016, 10:00am
Thom Gibson Amazing thankyou so much it's working! But.... my calender is set up for uk timings and all my events are in UK timings however, the event I just made has come through 5 hours late. 10.00 in the spread sheet became 15.00 on the calender. How can I fix this please? Really appreciate all your help.
Rachel Moore No worries, had a play and found the spread sheet settings under file. I've change my time to London. Cheers
Hello the system seems to use Eastern time only I am in the pacific, how can I change that please.
I can't get the specific times to work. Can you tell me how to add the time slot in there????
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
I seem unable to get the formmule app to link with my spreadsheet. Any idea how i can do this?
This reply is insanely late but it seems this script may not be working anymore. I did find this free solution that does involve a little bit more coding but it's a method directly from the folks at Google -
cloud.google.com/blog/products/g-suite/g-suite-pro-tip-how-to-automatically-add-a-schedule-from-google-sheets-into-calendar
I haven't done this myself though so I won't be able to answer any questions about it.
Even though i have the date format as 07/05/2016 and put in the right fields in the calendar merge settings for some reason it generates for the date Sat Dec 30 1899... where do i change the date settings?? Through the excel spreadsheet or the google calendar? Any one else had this issue?
+Sachi Jayasinghe Hey there Sachi. I've pasted a response that +andreas persson had about this as I haven't played around with it much:
"I noticed that in the sheet-settings the sheet GMT was -05:00
changed it to My GMT (+1) and it got better on sync :-) "
Somewhere I found my questions answered was the Google Plus Community for FormMule. Check it out here and I bet someone there will be able to help you :) Sorry I couldn't be of more help!
plus.google.com/communities/104966983745456719075