Template: Use Google Calendar to help you manage your events

[rissamari]

I'm having trouble limiting the number of participants for an event. I've tested it out and the event currently allows more than my 'max participants' amount of people to accept the calendar invite. Am I doing something wrong, or misunderstanding the feature of stipulating the max participants in the spreadsheet? As in, is that column just informational rather than functional?

[romainvialard]

@rissamari

No, it is functional (or mean to be). Did you put the trigger to send email confirmations ?

[rissamari]

@romainvialard I did, but found that more than the max # of people could both register for an event using the form and also say that they were attending via Google Calendar. I might be a bit confused because there are two methods of RSVP'ing?

[romainvialard]

@rissamari

No, they can only register via the form. But then the script mark them as attending in Google Calendar.

I checked and the max participants feature works well for me, nothing seems to be broken.

[pattyrucks]

@rissamari @romainvialard

I am having the same problem. It doesn't seem to be restricting the number of people that can sign up. Are there any hints about how to make this work?

[ajames571]

HI

Thank you very much for the effort and work; it is very impressive. I have managed to create my own copy, create a new calendar, make the modifications in the templates sheet and create and import some events. I have also modified the form as I am in a gmail domain so the form will collect the username. I have deleted the two questions and just left the prefilled box on the form with submit.

The problem i have is the form will not prepopulate. Any advice? I can send to you if helpful?

[zpga1]

why no sound?

[cadpoobah]

Awesome stuff! Is there a way to "modify" an event and have it updated on the calendar? If I change an event in the spreadsheet, it just adds a new event, leaving the original one. Thanks!

[romainvialard]

@cadpoobah

I'll try to add that soon :)

[mxminime]

How i can modify the template if I need to add the information to the (Venue) Place, I hope you can help me.

[romainvialard]

@mxminime

You can already add this info (it's not shown in the video but you will easily find it in the template)

[BoDzor]

Haha thanks, and sorry for yet another question

I have set up 2 events (still on my site which i linked before).

The first event has a limit of 2 people

The second event has a limit of 10 people

The first and 2nd have gotten 2 people, and now they both display "Registration closed for this event"

If you like i can private message you the link to the google docs if that helps.

Thanks

[BoDzor]

@BoDzor

Sorry for yet another post.

What ive found.

When i send confirmation emails it then closes all events regardless of how many free spaces are in there.

Is this on purpose or is it a bug.

Regards

Richard

[romainvialard]

@BoDzor

It is not on purpose. But I can't replicate this issue :(

Can you share your spreadsheet with me ?

[BoDzor]

Hey

Im having issues as the form is not pre-populated with the event title

Seeing as this video is very out dated, i believe i have set it up properly.

Things import to calender but when i click on booking, the form doesn't have event on it.

thanks

[BoDzor]

@BoDzor auzsurf.com/richard/

link to the test

[romainvialard]

@BoDzor

I'll try to make a new video next week

In the new template, you don't have to copy/past the form key / url in the spreadsheet.

If you've done it manually, check if the url ends with #gid=13

(In that case, here is your issue, you should remove the end of the url and import your events again)

[BoDzor]

@romainvialard

AMAZING!

thankyou.

Also, what will happen when in an event if there are only 2 people allowed

and a third tries to join, will it remove the link from the calander, or will it display a message saying too many people

how does that work?

Thanks!

[romainvialard]

@BoDzor

It will replace the link inside the event description by "Registration closed for this event"

[cat6898]

How do you limit the number who can sign up for an event? Did I miss it?

[romainvialard]

@cat6898

This is an old video, the template has been updated.

In the sheet "Put your events here", you can now see a column named "Max participants" :)

[xOnic]

@romainvialard I am guessing the template has been updated again because there is no longer a column for max participants. Any idea how I can edit a specific event let's call it "Research Appointment" for a max participant of 1?

[romainvialard]

@xOnic

There is a column for max participants.

But the template is now called "Event management: booking / Registration"

Did you get that version ?

[xOnic]

@romainvialard Hi Romain, this was exactly what I was looking for. It seems the new template name was never mention in the video and thus the confusion. Just a little comment since I notice that the date and time are integrated to one column. Is there a time-picker or does this have to always be enter manually. If the latter, it would be more convenient to have the start/end time on a separate column so it would be easier to copy and paste the times.

[xOnic]

@xOnic Another way to put this is how would I automate repeating events that occur at the same times, but with different dates without having to manually changing the date.

[messierjeanfrancois]

Merci !!!  Je découvre avec ce vidéo la puissance de GOOGLE APPS SCRIPT

WOW !!!

[clayharris78]

Romain, any ideas on whether or not HTML will be allowed in calendar entries again? Any other workarounds that you can think of?

[romainvialard]

@clayharris78

Hi, sorry for the delay.

Google finally posted an official answer on Google Help. HTML links won't be allowed in event's title but some HTML tags should be available again in the future (like background color). I don't know when...

No workarounds :(

[paulb92100]

since 2 days, the layout in the embed calendar is not fine. The html cells are not for. the event's title / description. The title (description) is displayed with all the html string instead of to use the html format to change the layout... :-(

[romainvialard]

@paulb92100

Yes, I hope Google will fix it as soon as possible.

Still no news from them on Google Help...

[freddespont]

@romainvialard Is there any post from you or anyone else in the google forums?

[romainvialard]

@freddespont

Yes.

Search for "HTML code is appearing in Google Calendar Event Description."

But no news from Google on those forums. So I'm now trying to contact the Calendar team directly.

[afq5n]

I have added a question on the form to ask whether the participant would like to be registered for the whole series of events (e.g. subject a lesson a, subject a lesson b, ....etc), is there a way to automate this type of registration so the report and summary would reflect this whilst keeping the possibility of registering for individual class components? I had a look at the script section but can't figure it out. I am not familiar with google api but have a fair knowledge of js. Thanks again,

[romainvialard]

@afq5n

Recurring events are not yet available

But you can write a function to find related events in the sheet 'Put your events here' and invite the participant to all of them in Calendar, or just increment numbers in the report.

There's a user guide if you've time to give it a try:

search for: google apps script guide

[afq5n]

Thanks for the good work.

[afq5n]

I would also like to know whether it is possible to populate the no. of registered participants of each event back to the calendar?

[romainvialard]

@afq5n

Registered participants are now shown in Calendar :)

[afq5n]

@romainvialard

Hi Romain,

Thank you for your prompt response, you are marvelous.

Where in the calendar can I find details of the registered participants for each event? I know such details are available in the report section of the spreadsheet but I actually need make this information (and just the no. of registered participants public).

[afq5n]

@romainvialard

Thank you for your prompt reply, Romain. You are marvelous. The e-mail confirmation script works okay now, thanks a lot.

Where in the calendar can I find the no. of registered participants for each event? I know such information is available in the spreadsheet report but I am trying to make this information (just the no. of registered users for each event but not personal details of individual participants) public and ideally on the calendar itself.

[romainvialard]

@afq5n

By default, the no. of participants isn't shown in Calendar. If you click on each event, (and if you have the right permissions) you can see all participants through 'more details', but that's all.

So you have 2 options:

- share the sheet 'report' (you can embed this sheet only in your webpage) (easy)

- update the event description after each submission (to add the number of participants in the description)

[afq5n]

Hi Romain,

I'm very grateful for what you have done here, thank you for creating such a useful template.

I am experiencing a problem with the template though, most of my events are "all-day-event" when I run the script to send e-mail notification, I get the follow error message "Event start time must be before event end time."

E-mail notification nevertheless got sent out, but the "email sent" column is not updated, so every time I run the script, new notification is sent to all participants.

[romainvialard]

@afq5n

Wow, I'm very sorry for this bug.

It's fixed, please make a copy of the template to get the updated version.

[trafficnl]

VERY  USE FULL THX .......

[GR8K9Sgreatcanines]

Great template. Is there a way to limit the number of registrants per event?

[romainvialard]

@GR8K9Sgreatcanines

In the template, you can see a sheet named 'Reports'- it indicates the number of participants per event.

With a little formula in column C (eg: if number in column B > 15, write FULL), and a Notification Rule (see spreadsheet menu (Tools)) - you can be alerted when the limit is reach for an event.

Then you can go to Calendar and change the event description: delete the link to the registration form and indicate there is no vacancies.

[positivedogtrainers]

thanks for responding. I have it now. I just changed the back ground color

[positivedogtrainers]

Thanks for the help. I will adjust the time. I'm getting use to the date and time format, now that I know what to do with the time, I should be fine. One more question if you do not mind. I want to change the colors, how would I do that?

thanks again

[romainvialard]

@positivedogtrainers

easy! In column 'Event Color', replace the text (eg: #8c6d8c) by another html color.

(search for 'html color code' on google)

[positivedogtrainers]

Great spreadsheet. Could you tell me how to change date/time format to am and pm instead of 24 hour clock? Also, two problems. When I post to 7pm, it shows up on my calendar as 6:59pm. Then when I have some of the dates and times filled in, I am told I need a start time, but it is there. Thanks

[romainvialard]

@positivedogtrainers

Sadly, this date & time format is the only one available in Google Spreadsheet (other available formats are only for date OR time - not both in the same cell).

Concerning the lack of precision (7 => 6:59), this is due to the conversion between the spreadsheet and the script - maybe Google Employees will find a way to make it more accurate in the future - in the meantime, as a workaround, you can add some time to your event: 7:00:01 to show 7pm in Calendar)

[romainvialard]

@positivedogtrainers

About your last issue, I'll need more info (the best would be if you can share your spreadsheet)

You can check for each row: if you've entered an event title (in a new row) but not a start time, there will be a problem.

[Bearskopff]

I'm eager to give this a try as it looks ruddy excellent. I'm having a little bit of trouble though. It seems the okay button refuses to work when I wish to "Make A Copy". Am I doing something ridiculously idiotic or is it something else entirely? Sorry to be a bother. :)

[romainvialard]

@Bearskopff

Hi,

Indeed "make a copy" don't work right now on Firefox.

It's a google issue, and it should be fixed soon.

It's working on Chrome.

But please wait a little. I'm trying to add new functions in the template and it may not work correctly right now.

[romainvialard]

@Bearskopff

Sorry, my bad, not working either in Chrome.

"There is a bug in Google Spreadsheets right now where making a copy of a sheet that contains a drawing is not possible. It will be resolved shortly,"

The "add to guest list function" is working!

[romainvialard]

Update:

- TimeZone in the template is set according to the TimeZone of your Calendar.

- Event reminder (email reminder - 15min)

- Participants are added to the guest list in Calendar - so they can easily unsubscribe.

[paulsjames1024]

Hey love the calendar importer. The only issue I am having that I can't solve is getting the registration form to add the prefilled field.

[romainvialard]

@paulsjames1024

Hi Paul,

Does the Form URL contain #gid=XX ?

(in cell B5 of sheet 'Templates')

You need to delete it.

[paulsjames1024]

I am not able to get the form to fill the prefilled section when they click on register.

[romainvialard]

"Is it possible to do confirmation email sending -process to happen automatically, or do I have to do it manually for each participant?"

It is now possible!

- In the spreadsheet menu, click on Tools > Scripts > Script Editor

- Then, in the Script Editor menu, click on Triggers > Current Script's Triggers...

- Click on "No triggers set up. Click here to add one now."

- Set: run "sendEmails" every 5 minutes

And done!

=)

[romainvialard]

Template updated:

- event import in Calendar

- email sender

=> Thanks to Google Apps Script Tutorial: Simple Mail Merge

(Reading Spreadsheet Data using JavaScript Objects)

It's now easier to modify both email and event templates (in sheet Templates)

[romainvialard]

If you want the possibility to easily manage events with limited number of participants

Or easily manage created events in general

Please star issue 39 on google-apps-script-issues

(I'll need this to improve the template)

[Tubbers888]

Nevermind, I found the Time Format setting, it is set correctly. Still have the same problem.

[romainvialard]

@Tubbers888

Could you share your spreadsheet & calendar ?

[Tubbers888]

Having trouble getting it to display the event times correctly. Just like idrhythym, all of my events start at 12am, no matter what time I actually set it to. Google Docs, Google Spreadsheet, and Google Calendar are all set to use the same timezone.

I saw your response to idrhythym, but how do you change time format from military? The DateTime strings in 'Calendar to import' show up as "6/20/2010 6:50:00". The day and relative times are correct in the calendar, but it always starts at 12am.

[idrhythm]

You're correct! It worked. I'm having one more issue I thought maybe you could help with. I'm entering in the time for my events from 8:00am-12:00pm MDT. I've changed the settings on the calendar, and the spreadsheet settings (and google doc settings) to match, but when I import to the calendar, the times are 8 hours behind. (showing 12am-4am.) Please advise!

[romainvialard]

@idrhythm

Have you changed all settings in calendar ?

Time Format 13:00 => 1:00pm ?

[idrhythm]

Hey, thanks so much for this. I tested this last night and it worked several times. Then, without warning I keep getting this error:

"Service invoked too many times: calendar (# 28)"

I have erased my calendar and set it up again. I have also started over on the spreadsheet portion. Any thoughts? Thanks!

[romainvialard]

@idrhythm

I suppose you reached a quota limit set in Google Calendar (to prevent abuse)

Just wait 24hrs and it should work again.

[romainvialard]

New feature added: Stats sheet

- number of registration for each event

and a lot more to come!

(contact me if you think of useful stats to add)

[paraboldk]

Brilliat demo and work! Just rember you need to keep the date format to us both in spreadsheet and calendar otherwise all your events will end up on january 1. 1970

[kekepengpeng]

Excellent! I have been seeking for an answer and finally someone is smart enough to make a video for all.

Thanks for sharing. It's awesome.

[romainvialard]

Sorry Kekepengpeng, I've erased your other comment when wanting to reply.

Can you post it again ?