Google Calendar Events via Google Form
- Author: Tom Woodward
- Category: Google
- Tags: calendar, google form, google script
This script allows you to setup a Google Form that adds events to a calendar. It’s useful.
You’d make your form first and calendar. Then you’d adjust it to reflect your particular column order and calendar ID. Finally, you’d add the script to your Google sheet (where the form submissions end up).
[Edit]
You’ll also want to set your script trigger to run on the submission of the form.
While in script editor, you’ll see a little clock icon. Click it and add the trigger so that the function runs on form submission.
That’s about it.
Related posts
Google Script to Copy Row Above to Blank Row Below
I had a spreadsheet that entered blank cells when there was more than one admin for a WordPress site. So if Site_1 had two admins, I’d get two rows of data. The first row for the site would have- siteURL | siteTitle | siteAdmin but the second row for that site would have something like- < blank > | < blank > | siteAdmin I started to just drag down and fill but there was lots of data and it just felt like something for the machine to do. The following Google Script did it for me in a few seconds despite a couple thousand rows of data. Not rocket science but it might be handy for someone and it was a really convenient example of variables and loops when I had a conversation with my son last night.
- Author: Tom Woodward
- Category: Data, Google
- Tags: google script, google sheets, javascript, js, Sheets
Google Script, Twitter API & Google Maps
Image taken from page 12 of ‘Guide to Cambridge: the town, university and colleges … To which are added, notes upon the villages within ten miles, a map, etc’ flickr photo by The British Library shared with no copyright restriction (Flickr Commons) This is more playing with Martin‘s TAGs in Google Script and then moving it into Google Maps for visuals . . . the TAGS related functions will only work in that context but the purse Google stuff should work in any spreadsheet. Get Twitter Location by Account This function will get the user’s location as defined by their profile. So =getLoc(“twoodwar”) would return Richmond VA. This function will get the lat/long via Google Script. So =getLl(“Richmond, VA”) would return 37.5407246, -77.4360481. Keep in mind if it’s trying to get the lat/lng for someone who put “the Interwebz” as their location like OnlineCrsLady then your lat/lng may not work out that well. So that’ll let us take a chunk of TAGs data.Martin Hawksey forever. and do something like this with Google Maps. I’m not going to break this chunk down quite yet. I did it at very odd hours and it works but it’s not very clean and I know there are ways to do it better.There is no shame in my game but there is some bashfulness. Pretend […]
- Author: Tom Woodward
- Category: Google
- Tags: Google Maps, google script, google sheets, latitude, longitude, TAGs
Interactive Google Sheets Dashboards
It’s pretty easy to put a bunch of data and charts in a spreadsheet and call it a dashboard. It became a more interesting challenge to make those charts change to reflect variables chosen via dropdown cell menus. The key it turns out is using =query. I can do some really powerful things with query and as long at the data bounds (columns/rows) are the same, I can change the content and it’ll replicate in the chart assigned to those columns/rows. For instance, I can have raw data on a sheet called data. I can use query on that data on another sheet with something like =query(data!A1:N,”select B,C,D,E,F,G,H,I,J,K,L,M,N where A=”&”‘”&A2&”‘”) That formula is going to the sheet named data and querying cells A1 through N(whatever the last row is) and selecting columns B through N where the content of cell A matches the text of cell A2 on the local page. As a result we can manipulate the contents of cell A2 and change the data being returned. In this case I did a little data validation drop down to restrict it to the three items for which we have data. Do take note that for numbers I could have just appended the cell reference (&A2 in this case) but because it was a text match I had to staple […]
- Author: Tom Woodward
- Category: Data, Google
- Tags: charts, google sheets, graphs, query
Comments on this post
Any reason you don’t just tap into the form submit event array? https://developers.google.com/apps-script/guides/triggers/events
It could pretty much entirely cut out the getLatest parts of that function. Since the form submit event returns an array, zero indexing would apply. Here is an ex from a post I’ve been working on. Looking to make a tutorial vid in the next few days:
http://www.jeffreyeverhart.com/2015/11/03/how-to-create-a-google-calendar-event-with-google-forms/
You’re doing some cool stuff with Google apps 🙂 I’ll be back! Cheers!
I blame it on ignorance. 🙂
Thanks for the tip and so very happy to see the post.
Hi
I want to create a form that will update x number of calendars depending on the x selected when the form is submitted.
I can use the old FormMule script but not sure if it can send events to different calendars.
Johan – That should be possible. I think just push the various calendar IDs into an array (based on the form choices) and loop the event creation piece for each calendar ID.
I’m at a conference at the moment but I’ll make a demo in the next week or so.
Thanks for commenting Tom. Appreciated. I am looking forward to the demo if you have the time. BTW: I think I can do the first bit but not sure about the last “looping the event” 🙂
Here’s the loop for adding to multiple calendars. You’d just work it in with the create event portion and map fromSS to the form entry that allows users to pick the calendars. I’ll probably rewrite an example that takes advantage of the simpler version Jeff created and adding this element. That’ll probably happen this weekend at the latest.
Hey Tom,
Thanks for posting this. I have a question: What adjustments would I have to make if I wanted to schedule the event for a specific time instead of for specific dates? Example: Monday, July 25th from 1 pm to 4 pm.
Would appreciate your help on this.
Best,
Sebastian
Pretty sure this one will do that. You just have to activate the time option in the form date field. If it doesn’t (and maybe you should anyway) take a look at the script Jeff references above.
Thanks for the great information! Not sure if my previous comment posted so I’ll try again.
Hopefully you can help me with a couple of questions. I almost have what I need but I can’t seem to figure out two details: (1) I would like to make it so that there is a default end date/time (always two hours later than the start date/time) built into the script and therefore NOT entered manually at all because (2) I would also like to add recurring events with two specific options–a one-time event and another option that allows me to repeat the event 12 times. Does that make sense?
Is this something you would be able to provide some suggestions with? I haven’t had any luck in my efforts up to now and with only limited programming experience.
Thanks,
Eric
Sorry Eric. Your comment got caught in the spam folder . . . which I need to check more regularly.
As penance, I’ll try to write a decent post about this in the near future. In the meantime . . .
1. You could create an auto-end time in the script itself by doing something like (pseudo code- obviously)
var endTime = startTime+2;
2. You could make the initial question something like ‘is this a one time or repeat?’ If they choose repeat, you’d set the variable in the script to 12
if (column A = 'repeat') { var repeat = 12} else {var repeat = 1}
Then you’d loop through the event creation 12 times incrementing the date variable in whatever way you needed.
Tom
I am completely new at this,
I am experiencing an issue wherein when I submit the form, the information is recorded in my google worksheet,
however.. unless I open the script editor and run the script the script doesnt function..
I am sure I am missing something? just not sure what.
Yep – you want to set the script trigger to fire on form submission. I did not have that in the directions. I’ve amended them.
Just wanted to say thank you for the code and the clear explanation. Automated vehicle submission thanks to you at my non-profit. Appreciate it.
Awesome!