Fun with Coordinates: A story about using JavaScript to do things the hard way.

Went to a training class this morning on using Google's MyMaps.  Interesting enough, though I don't see myself ever really using it.  For the uninitiated, this application lets you upload a spreadsheet of locations with a bunch of metadata, and you can manipulate the visualization.  Well, the map pointers are placed based on either an address or a lat and long.  The presenter noted that she wasn't sure if you could use UTM or State Planar Coordinate System coordinates.  Because I had pretty much exhausted my interest in playing with the MyMaps interface in about 10 minutes, I took it as a challenge to obtain and test a UTM data set.  I did a search for an available dataset but didn't find anything useful, however I had at my disposal a Google Sheets spreadsheet that had the Wyoming zip codes with latitudes and longitudes (basically a truncated version of this file). "So I'll just convert the ones I have, easy..." BWAHAHAHA ok, we'll get to what that was about.



So I did another search, this time "convert lat long to utm".  I thought it would be a straight forward formula, but instead all I got was calculators.  So I settled on the one hosted by University of Wisconsin Green Bay: Convert Between Geographic and UTM Coordinates.  I was still thinking there was some magic formula that no one wanted to share, so I started poking around in the dev console looking for it:

Well there we go, a JavaScript function does the conversion. Lets take a look...

Yeah.... so I'm not even trying to reimplement that in a formula in Google Sheets. So my next thought is "well I'll just bring the data into the console and iterate it through the function..."  Simple enough right?  So I'm in the spreadsheet, come up with a simple way to build up an array declaration:

Works like a charm... except there is one little tiny problem.  The training lab has been recently refitted with Chromeboxes for all the workstations, and I'm logged in as Guest.  While I thought this was fantastic when I found out it means that every chrome instance is pretty much automatically incognito (so I don't have to worry about my log ins hanging around)... it also means I have no clipboard access in Google Sheets without installing the Drive app. And guess what? Installation is not enabled... fml...

Not to be deterred, I knew there had to be SOME way of getting the data out.  Turns out that I had copy paste access to pdf documents, so I threw the last cell into it's own sheet so I could make the one cell nice and huge, and I downloaded it as a pdf.

Not pretty but at least I could use the clipboard...

Drop it into the dev console, add the closing bracket for the array, clean out some rogue newlines, and voila, I have all my lat and long data in a nice neat array of JSON objects:

The next bit was pretty straight forward, programming wise.  The GeogToUtm() function was grabbing values out of the lat and long input fields, transforming them, and dropping the result in the UTM input fields.  So all I had to do was iterate over my points, and for each one drop the value into the lat/long inputs, run the transform, and pick the result out of the output. First go at it I just printed the results to the console, apparently having forgotten about the "no clipboard access" issue I was having with Sheets...

Success! Now I just need to copy and paste.. these.. into... well damn...

I had a laugh with my neighbor (he'd been watching this circus with great interest...), and almost threw in the towel.  But I figured I would see if there was some way of writing a text file to the local hard drive with JavaScript, so back to Google I went, and lo and behold, StackOverflow to the rescue (if I had a nickle for every time SO saved my bacon). Since I could still copy-paste out of the browser, I put the interwebs to work for me.  Instead of writing to the console, I dropped everything into an array, which I then ran through the SO code.  Took me a couple tries erroring out before I went back to the SO page and discovered a fix for the version I was using.  Here is the csv generation:

Running the second part got me the data downloaded.  On the Chromebox it seamlessly opened it up in Sheets, whereas on my regular workstation it downloaded like a normal file (after I told it the pop-up was ok, of course):

Once I had the converted data in Sheets it was trivial to try importing it into MyMaps and discovering that, no, you cannot use UTM coordinates (even tried adding a "zones" column, since a proper UTM coordinate requires one... and yeah, no dice there either).  Want to know the REALLY funny part? Had I logged in (instead of using the machine as a Guest), I'm pretty sure I either A) would have had the Drive app already installed or B) could have installed it myself... and thus could have finished much faster. Doh. It was a fun exercise anyway, though... yolo. =P