Google Calendar Events via Google Form
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).
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.
The goal of this project was to have students logging their practice on translation and a separate non-translation activity.Jogging, juggling, some other j word . . . It’s a neat idea and I proposed doing it via Google Forms/sheets because it was a really short timeline. Here’s a walkthrough of how it ended up with version two. It’s likely we’ll have a more packaged third version that will integrate some more elements. There’s not too much unique going on in the script but here are a few of the hassles and how I dealt with them. Connect Weekly Data for Stacked Bar Chart I needed a way to group the data logged per week. ISOWEEKNUM gives you the week number that a particular date occurs in. So this little bit sets the last row of column N to be the ISOWEEKNUM function and referneces the date the form was submitted. This is handy and I’d never run across it before. Keep it Sorted I was trying to figure out how to do the sorting on the QUERY but this seemed easier. This function just keeps the entire sheet ordered by the week number. That way if people enter data later, it doesn’t mess up the graph. Authorizing IMPORTRANGE via Script From the beautiful people at Stackoverflow comes this gem. Previously, […]
Google Sheets often thinks it’s smarter than you. That’s helpful at times and irritating at other times.Mostly, I find it irritating. The Google Form to Sheets path is one place where that can come up repeatedly and in ways that are hard to see. For instance, if you use the Time entry on forms, Google Sheets tries to coerce it into a date structure while leaving you seeing just the time in the sheets view. That leads to weird and unpredictable stuff if you’re trying to use Google Scripts to interact with that cell data based on what you see. For instance, this is data from the time entry field as seen on the sheet side. Note the two places you might expect to see truth. Now if we get that same data via Google Script . . . Cell data that you’d expect to just be that time element returns as Sat Dec 30 1899. The time is right though. If you manually change the format of that column to plain text things work . . . but it gets re-set on all subsequent submissions. My solution so far has been the following Google Script set to fire on every new form submission. In my case, I’m changing the format of the latest entry in the spreadsheet in columns […]