07

Google Script – Plain Text

Google Sheets often thinks it’s smarter than you. That’s helpful at times and irritating at other times.1 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 F and G to […]

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). [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.

Embed Collector Tool

Some people don’t have websites handy or their blogs won’t allow iframe embeds. They should still be able to have their students do stuff and get it all in one place. This is the Internet! And this tool is meant to deal with that. You can see a working example with three submissions (via a Google Form) here. It’ll take any iframe or HTML as a submission. The search box will also filter based on all the additional material associated with the element in the form. For instance in our example you can type “Sarah” and you’ll end up with one item. So the way this should work is you click here and accept the copy. You now have the spreadsheet and form. The directions are on the sheet labeled directions. I have a dramatic green arrow pointing at it in the image below. You can edit the form associated with the sheet under Form>Edit form. The only thing you have to leave/have in the form is the one called “The embed code.” Everything else can be deleted and/or changed. Stuff I Learned This pretty much a mashup of two recent projects – the personalized fridge poetry and the Angular/Google Sheets JSON stuff. This one runs entirely in javascript so to use the $_GET option, which javascript doesn’t have, I […]

A google map showing the data being plotted

Google Forms Interacting with Google Maps

The challenge as initially laid out was to let students log the miles they walk and have those miles show their progress from Richmond VA to Key West FL on a map. I found quite a few ways not to do this- mostly because I was attacking the problem in the wrong way. I wanted to set a point of origin, a destination direction and then map a straight line for X amount of miles. It may be possible but it feels like you’re fighting the system the whole way. I took some time off and let it marinate in my head and a much easier solution appeared. Goal In the end I realized I wanted to get the path from Richmond to Key West and then be able to add a marker to that path at any mileage point along the way. The miles would be pulled from Google Form submissions. One of the key helpers was epoly.js . It adds a number of tricks which come in handy- including the option to GetPointAtDistance along a path. I used this example for find the midpoint of a route as the starting template. I figured I’d leave the midpoint as a goal and then all I needed to do was add another marker that was placed based on the sum […]